下面是从SQL里一个表里取出来的一组数据
D201 20071025004 0001 5102 20071009021
D201 20071025004 0002 5101 20070925064
D201 20071025004 0003 5102 20071009021
D201 20071115039 0001 5102 20071107016
D201 20071115039 0002 5102 20071108013
D201 20071115039 0003 5102 20071109013
D201 20071115039 0004 5102 20071109013
D201 20071115039 0005 5102 20071111007
D201 20071115039 0006 5102 20071115002
需要查询到第1列+第2列相同的情况,如果第4列+第5列也相同时显示出来(如下结果),否则不显示
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013
D201 20071025004 0001 5102 20071009021
D201 20071025004 0002 5101 20070925064
D201 20071025004 0003 5102 20071009021
D201 20071115039 0001 5102 20071107016
D201 20071115039 0002 5102 20071108013
D201 20071115039 0003 5102 20071109013
D201 20071115039 0004 5102 20071109013
D201 20071115039 0005 5102 20071111007
D201 20071115039 0006 5102 20071115002
需要查询到第1列+第2列相同的情况,如果第4列+第5列也相同时显示出来(如下结果),否则不显示
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013
下面是从SQL里一个表里取出来的一组数据
D201 20071025004 0001 5102 20071009021
D201 20071025004 0002 5101 20070925064
D201 20071025004 0003 5102 20071009021
D201 20071115039 0001 5102 20071107016
D201 20071115039 0002 5102 20071108013
D201 20071115039 0003 5102 20071109013
D201 20071115039 0004 5102 20071109013
D201 20071115039 0005 5102 20071111007
D201 20071115039 0006 5102 20071115002
需要查询到第1列+第2列相同的情况,如果第4列+第5列也相同时显示出来(如下结果),否则不显示
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013
select col1,col2,col4,col5 from tb group by col1,col2,col4,col5 having count(1)>1
SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE T.COL1=COL1 AND T.COL2=COL2 AND T.COL4=COL4 AND T.COL5=COL5)
select col1,col2,col4,col5 from tb group by col1,col2,col4,col5 having count(1)>0
a b c d e
D201 20071025004 0001 5102 20071009021
D201 20071025004 0002 5101 20070925064
D201 20071025004 0003 5102 20071009021
D201 20071115039 0001 5102 20071107016
D201 20071115039 0002 5102 20071108013
D201 20071115039 0003 5102 20071109013
D201 20071115039 0004 5102 20071109013
D201 20071115039 0005 5102 20071111007
D201 20071115039 0006 5102 20071115002
*/需要查询到第1列+第2列相同的情况,如果第4列+第5列也相同时显示出来(如下结果),否则不显示
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013
select a,b,d,e from (
select 'D201' a,'20071025004' b,'0001' c,'5102' d,'20071009021' e union all
select 'D201','20071025004','0002','5101','20070925064' union all
select 'D201','20071025004','0003','5102','20071009021' union all
select 'D201','20071115039','0001','5102','20071107016' union all
select 'D201','20071115039','0002','5102','20071108013' union all
select 'D201','20071115039','0003','5102','20071109013' union all
select 'D201','20071115039','0004','5102','20071109013' union all
select 'D201','20071115039','0005','5102','20071111007' union all
select 'D201','20071115039','0006','5102','20071115002') aa
group by a,b,d,e
having count(a+b+d+e) > 1/*
a b d e
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013
*/
insert @t values('D201','20071025004','0001','5102','20071009021')
insert @t values('D201','20071025004','0002','5101','20070925064')
insert @t values('D201','20071025004','0003','5102','20071009021')
insert @t values('D201','20071115039','0001','5102','20071107016')
insert @t values('D201','20071115039','0002','5102','20071108013')
insert @t values('D201','20071115039','0003','5102','20071109013')
insert @t values('D201','20071115039','0004','5102','20071109013')
insert @t values('D201','20071115039','0005','5102','20071111007')
insert @t values('D201','20071115039','0006','5102','20071115002')select col1,col2,col4,col5 from @t group by col1,col2,col4,col5 having count(1)>1
/*
col1 col2 col4 col5
----- --------------- ----- ---------------
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013(2 行受影响)
*/
select col1,col2,col4,col5
from (SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE T.COL1=COL1 AND T.COL2=COL2 AND T.COL4=COL4 AND T.COL5=COL5)
) as sql
group by col1,col2,col4,col5
having count(1)>1
CREATE TABLE TBTEST(COL1 VARCHAR(10),COL2 BIGINT,COL3 BIGINT,COL4 BIGINT,COL5 BIGINT)
INSERT TBTEST
SELECT 'D201', 20071025004 ,0001, 5102, 20071009021 UNION ALL
SELECT 'D201', 20071025004 ,0002, 5101, 20070925064 UNION ALL
SELECT 'D201', 20071025004 ,0003, 5102, 20071009021 UNION ALL
SELECT 'D201', 20071115039 ,0001, 5102, 20071107016 UNION ALL
SELECT 'D201', 20071115039 ,0002, 5102, 20071108013 UNION ALL
SELECT 'D201', 20071115039 ,0003, 5102, 20071109013 UNION ALL
SELECT 'D201', 20071115039 ,0004, 5102, 20071109013 UNION ALL
SELECT 'D201', 20071115039 ,0005, 5102, 20071111007 UNION ALL
SELECT 'D201', 20071115039 ,0006, 5102, 20071115002 --DROP TABLE TBTEST
SELECT * FROM TBTESTSELECT DISTINCT COL1,COL2,COL4,COL5 FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.COL1=COL1 AND T.COL2=COL2 AND T.COL4=COL4 AND T.COL5=COL5 AND T.COL3<>COL3)
COL1 COL2 COL4 COL5
---------- -------------------- -------------------- --------------------
D201 20071025004 5102 20071009021
D201 20071115039 5102 20071109013(所影响的行数为 2 行)