如何获取重复值唯一ID [英] How to get duplicate values unique ID

问题描述

示例表值：

Example Table values:

ID	AcountNo	Createddate
1	11111		2008-12-23 00:00:00.000 
2	11111		2008-12-23 00:00:00.000 
3	11111		2008-12-24 00:00:00.000 
4	22222		2008-12-23 00:00:00.000 
5	22222		2008-12-24 00:00:00.000 
6	22222		2008-12-23 00:00:00.000 
7	33333		2008-12-23 00:00:00.000 
8	33333		2008-12-23 00:00:00.000 
9	44444		2008-12-23 00:00:00.000 
10	55555		2008-12-23 00:00:00.000

我只想重复值Id不喜欢

I want only duplicate Values Id No like

ID
1
2
4
6
7
8

我需要sql查询1缺少数据。

I need sql query for 1 lacks data.

推荐答案

试试这个：

Try this:

SELECT
   ID
FROM
   YourTable As T1
WHERE
   Exists
   (
      SELECT 1
      FROM YourTable As T2
      WHERE T2.ID != T1.ID
      And T2.AccountNo = T1.AccountNo
      And T2.CreatedDate = T1.CreatedDate
   )
;

另一种解决方案是：

An alternative solution is:

SELECT ID, AcountNo, Createddate
FROM (
    SELECT ID, AcountNo, Createddate, ROW_NUMBER() OVER(PARTITION BY Createddate ORDER BY ID) AS RowNo
    FROM TableName
) AS T 
WHERE RowNo=1

有关详细信息，请参阅排名函数 [ ^ ]。

这篇关于如何获取重复值唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！