select a.* from YTK_YXBH a, (select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b where a.jh = b.jh and a.yxbh = b.yxbh
DECLARE @tb TABLE([JH] varchar(10), [YXBH] varchar(10), [ZD1] int, [ZD2] int, [ZD3] int) INSERT INTO @tb SELECT 'A', 'B', 1, 2, 3 UNION ALL SELECT 'A', 'C', 4, 5, 6 UNION ALL SELECT 'A', 'B', 7, 8, 9 UNION ALL SELECT 'E', 'B', 10, 0, 5 UNION ALL SELECT 'E', 'E', 3, 2, 1SELECT * FROM @tb A WHERE (SELECT COUNT(1) FROM @tb WHERE JH = A.JH AND YXBH = A.YXBH) > 1
select a.* from YTK_YXBH a join (select JH, YXBH from YTK_YXBH group by JH, YXBH having count(1) > 1) b on a.JH = b.JH and a.YXBH = b.YXBH
--测试表@ta declare @ta table(JH varchar(5), YXBH varchar(5), ZD1 int, ZD2 int, ZD3 int) insert @ta select 'A' , 'B' , 1 , 2 , 3 union all select 'A' , 'C' , 4 , 5 , 6 union all select 'A' , 'B' , 7 , 8 , 9 union all select 'E' , 'B' , 10 , 0 , 5 union all select 'E' , 'E' , 3 , 2 , 1select * from @ta a where exists (select 1 from @ta where JH=a.JH and YXBH=a.YXBH group by JH,YXBH having count(*)>1 ) (所影响的行数为 5 行)JH YXBH ZD1 ZD2 ZD3 ----- ----- ----------- ----------- ----------- A B 1 2 3 A B 7 8 9(所影响的行数为 2 行)
if object_id('pubs..YTK_YXBH') is not null drop table YTK_YXBH gocreate table YTK_YXBH ( JH char(1), YXBH char(1), ZD1 int, ZD2 int, ZD3 int ) insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 1, 2, 3) insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','C', 4, 5, 6) insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 7, 8, 9) insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','B', 10, 0, 5) insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','E', 3 , 2, 1) goselect a.* from YTK_YXBH a, (select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b where a.jh = b.jh and a.yxbh = b.yxbhdrop table YTK_YXBH JH YXBH ZD1 ZD2 ZD3 ---- ---- ----------- ----------- ----------- A B 1 2 3 A B 7 8 9(所影响的行数为 2 行)
select a.JH, a.YXBH , a.ZD1 , a.ZD2 , a.ZD3 from YTK_YXBH a,YTK_YXBH b where a.JH= b.JH and a.YXBH = b.a.YXBH 其他重复条件-----
roy_88(论坛新星_燃烧你的激情!!) 的不对,并不符合要求.
declare @tt table (JH varchar(10),YXBH varchar(10),ZD1 int ,ZD2 int,ZD3 int) insert into @tt select 'A','B',1,2,3 insert into @tt select 'A','C',4,5,6 insert into @tt select 'A','B',7,8,9 insert into @tt select 'E','B',10,0,5 insert into @tt select 'E','E',3,2,1 select * from @tt where JH+YXBH in(select JH+YXBH from (select count(JH+YXBH) as num,JH,YXBH from @tt group by JH,YXBH) as tt2 where tt2.num >1) 查询结果A B 1 2 3 A B 7 8 9
CodeSaint(下划线) ( ) 信誉:100 Blog roy_88(论坛新星_燃烧你的激情!!) 的不对,并不符合要求.楼主是指第一个语句是查出来记录JH,YXBH重复大于1的JH,YXBH。 下面测试的为查出重复记录
declare @tt table (JH varchar(10),YXBH varchar(10),ZD1 int ,ZD2 int,ZD3 int) insert into @tt select 'A','B',1,2,3 insert into @tt select 'A','C',4,5,6 insert into @tt select 'A','B',7,8,9 insert into @tt select 'E','B',10,0,5 insert into @tt select 'E','E',3,2,1select a.* from @tt a where exists (select 1 from @tt b where a.jh=b.jh and a.yxbh=b.yxbh group by b.jh,b.yxbh having count(*) >1)查询结果A B 1 2 3 A B 7 8 9
(select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b
where a.jh = b.jh and a.yxbh = b.yxbh
INSERT INTO @tb
SELECT 'A', 'B', 1, 2, 3
UNION ALL SELECT 'A', 'C', 4, 5, 6
UNION ALL SELECT 'A', 'B', 7, 8, 9
UNION ALL SELECT 'E', 'B', 10, 0, 5
UNION ALL SELECT 'E', 'E', 3, 2, 1SELECT * FROM @tb A
WHERE (SELECT COUNT(1) FROM @tb WHERE JH = A.JH AND YXBH = A.YXBH) > 1
from YTK_YXBH a
join (select JH, YXBH
from YTK_YXBH
group by JH, YXBH
having count(1) > 1) b on a.JH = b.JH and a.YXBH = b.YXBH
declare @ta table(JH varchar(5), YXBH varchar(5), ZD1 int, ZD2 int, ZD3 int)
insert @ta
select 'A' , 'B' , 1 , 2 , 3 union all
select 'A' , 'C' , 4 , 5 , 6 union all
select 'A' , 'B' , 7 , 8 , 9 union all
select 'E' , 'B' , 10 , 0 , 5 union all
select 'E' , 'E' , 3 , 2 , 1select * from @ta a where
exists (select 1 from @ta where JH=a.JH and YXBH=a.YXBH group by JH,YXBH having count(*)>1 )
(所影响的行数为 5 行)JH YXBH ZD1 ZD2 ZD3
----- ----- ----------- ----------- -----------
A B 1 2 3
A B 7 8 9(所影响的行数为 2 行)
drop table YTK_YXBH
gocreate table YTK_YXBH
(
JH char(1),
YXBH char(1),
ZD1 int,
ZD2 int,
ZD3 int
)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 1, 2, 3)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','C', 4, 5, 6)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('A','B', 7, 8, 9)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','B', 10, 0, 5)
insert into YTK_YXBH(JH,YXBH,ZD1,ZD2,ZD3) values('E','E', 3 , 2, 1)
goselect a.* from YTK_YXBH a,
(select jh,yxbh from YTK_YXBH group by jh,yxbh having count(*) >= 2) b
where a.jh = b.jh and a.yxbh = b.yxbhdrop table YTK_YXBH
JH YXBH ZD1 ZD2 ZD3
---- ---- ----------- ----------- -----------
A B 1 2 3
A B 7 8 9(所影响的行数为 2 行)
from YTK_YXBH a,YTK_YXBH b
where a.JH= b.JH and a.YXBH = b.a.YXBH
其他重复条件-----
insert into @tt select 'A','B',1,2,3
insert into @tt select 'A','C',4,5,6
insert into @tt select 'A','B',7,8,9
insert into @tt select 'E','B',10,0,5
insert into @tt select 'E','E',3,2,1
select * from @tt where JH+YXBH in(select JH+YXBH from (select count(JH+YXBH) as num,JH,YXBH from @tt
group by JH,YXBH) as tt2 where tt2.num >1)
查询结果A B 1 2 3
A B 7 8 9
roy_88(论坛新星_燃烧你的激情!!) 的不对,并不符合要求.楼主是指第一个语句是查出来记录JH,YXBH重复大于1的JH,YXBH。
下面测试的为查出重复记录
insert into @tt select 'A','B',1,2,3
insert into @tt select 'A','C',4,5,6
insert into @tt select 'A','B',7,8,9
insert into @tt select 'E','B',10,0,5
insert into @tt select 'E','E',3,2,1select a.* from @tt a where exists (select 1 from @tt b where a.jh=b.jh and a.yxbh=b.yxbh group by b.jh,b.yxbh having count(*) >1)查询结果A B 1 2 3
A B 7 8 9