在SQL Server中从“重复记录+非重复记录中仅选择1条记录” [英] Select Only 1 Record From Duplicate Records + Non-Duplicate Records In SQL Server

问题描述

大家好，



我在SQL中有一个表，我想要检索里面的所有记录。表格中有许多列，如NAME，IDENTIFIER，TOTAL。假设这个表有6条记录，如果IDENTIFIER列下有重复值，我想检索那些重复记录中的前1个和其他非重复记录。



例如

**目前SQL中的表格：**



----------------- -----------------------------------------------



NAME |标识符|总计



ab | 110011 | 10



cd | 110011 | 20



ef | 110012 | 30



gh | 110013 | 40



ij | 110014 | 50



kl | 110015 | 60



--------------------------------- -------------------------------

110011的IDENTIFIER重复，但我想找回一个那些记录。



**我想达到的输出：**



------- -------------------------------------------------- -------



NAME |标识符|总计



ab | 110011 | 10



ef | 110012 | 30



gh | 110013 | 40



ij | 110014 | 50



kl | 110015 | 60



--------------------------------- -------------------------------



请帮帮我在此，谢谢！

hi all,

I have a table in SQL where I want to retrieve all records inside. There are many columns inside the table like NAME, IDENTIFIER, TOTAL. Let's say this table has 6 records, if there is duplicate value under IDENTIFIER column, I want to retrieve the top 1 of those duplicate records + the other non-duplicate records.

For e.g.
**Table in SQL currently:**

----------------------------------------------------------------

NAME | IDENTIFIER | TOTAL

ab | 110011 | 10

cd | 110011 | 20

ef | 110012 | 30

gh | 110013 | 40

ij | 110014 | 50

kl | 110015 | 60

----------------------------------------------------------------
IDENTIFIER of 110011 is repeated but I want to retrieve one of those record.

**The output I want to achieve:**

----------------------------------------------------------------

NAME | IDENTIFIER | TOTAL

ab | 110011 | 10

ef | 110012 | 30

gh | 110013 | 40

ij | 110014 | 50

kl | 110015 | 60

----------------------------------------------------------------

Please help me on this, thanks!

推荐答案

如果是SQL Server，您可以使用 RANK（） 来完成你的工作。

尝试类似的事情 -

If it is SQL Server, you can use RANK() to do your job.
Try something like-

SELECT [NAME],IDENTIFIER,TOTAL
FROM
(
	SELECT [NAME],IDENTIFIER,TOTAL,RANK() OVER (PARTITION BY IDENTIFIER ORDER BY TOTAL) AS SlNo
	FROM YourTable
) AS T
WHERE SlNo=1

示例：

EXAMPLE:

SELECT [NAME],IDENTIFIER,TOTAL
FROM
(
	SELECT [NAME],IDENTIFIER,TOTAL,RANK() OVER (PARTITION BY IDENTIFIER ORDER BY TOTAL) AS SlNo
	FROM
	(
		SELECT 'ab' [NAME], 110011 IDENTIFIER, 10 TOTAL
		UNION ALL
		SELECT 'cd', 110011, 20
		UNION ALL
		SELECT 'ef', 110012, 30
		UNION ALL
		SELECT 'gh', 110013, 40
		UNION ALL
		SELECT 'ij', 110014, 50
		UNION ALL
		SELECT 'kl', 110015, 60
	) AS T1
) AS T2
WHERE SlNo=1

希望，它有帮助：）

Hope, it helps :)

你可以检查DISTINCT关键字。

You can check DISTINCT keyword.

这篇关于在SQL Server中从“重复记录+非重复记录中仅选择1条记录”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！