重复筛选

[赖哥]

我想筛选出每一单，有ID重复的单号。请问怎么筛选

--例如：

单号 ID
6921109062192 H123456
6921109062192 H123456
6921109062321 FW082679020506
6921109062321 FW072490010006
6921109063045 989419310173021242
6921109063045 PZ800946035000
DX123456 88997766
DX123456 88997766

[开启时代]

select distinct 单号 from TB where ID in (select ID from TB group by ID having COUNT(1)>1)

[赖哥]

按照那个，还是有ID没有重复的数据出来了

[發糞塗牆]

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY 单号 ) id ,
                    *
          FROM      ( SELECT DISTINCT
                                单号 ,
                                id
                      FROM      TB
                    ) b
        ) c
WHERE   id > 1

[习惯性蹭分]

select 单号 from tb group by 单号
having count(id)>1

[习惯性蹭分]

select 单号 from tb group by 单号,id having count(id)>1

[xiaoqi7456]

select * from tb where id=(select id from tb group by id having count(1)>1)

[赖哥]

3楼的兄弟，你的也不行

[發糞塗牆]

你的表只有两列？

[赖哥]

是啊，我的表只有2列，就是明细表 单号必然重复。就是要查询这个单号里面，货号是不是有重复的

[發糞塗牆]

我拿了两个数据来测了一下，应该没问题了

WITH    huang ( 单号, ID )
          AS ( SELECT   '6921109062192' ,
                        'H123456'
               UNION ALL
               SELECT   '6921109062192' ,
                        'H123456'
               UNION ALL
               SELECT   '6921109062321' ,
                        'FW082679020506'
               UNION ALL
               SELECT   '6921109062321' ,
                        'FW072490010006'
             ),
        huang2
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY 单号, ID ORDER BY 单号 ) rid ,
                        *
               FROM     huang
             )
    SELECT  *
    FROM    huang2
    WHERE   rid > 1

[赖哥]

10楼这个可以用！谢谢