假设存在一张表t1,有字段N,B,C,D,记录如下:N B C D
1 n1 c1 1.1
2 n1 c1 1.1
3 n1 c1 1.3
4 n1 c2 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5
11 n2 c4 1.6
12 n2 c5 1.6
请问要想得到以下内容,sql语句应该怎么写?
N B C D
1 n1 c1 1.1
2 n1 c1 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5
1 n1 c1 1.1
2 n1 c1 1.1
3 n1 c1 1.3
4 n1 c2 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5
11 n2 c4 1.6
12 n2 c5 1.6
请问要想得到以下内容,sql语句应该怎么写?
N B C D
1 n1 c1 1.1
2 n1 c1 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5
select max(call_id) call_id1
from t_call group by call_userid,call_repadetaid,call_stat having(count(call_userid)>1 and count(call_repadetaid)>1 and count(call_stat)>1)
union select min(call_id) call_id1
from t_call group by call_userid,call_repadetaid,call_stat having(count(call_userid)>1 and count(call_repadetaid)>1 and count(call_stat)>1)
)
把call_id 换成N,call_userid换成B,call_repadetaid换成C,call_stat 换成D,我在自己本地机器上试验成功了
create table T(N int, B varchar(10), C varchar(10), D decimal(10,1))
insert T select 1, 'n1', 'c1', 1.1
union all select 2, 'n1', 'c1', 1.1
union all select 3, 'n1', 'c1', 1.3
union all select 4, 'n1', 'c2', 1.1
union all select 5, 'n1', 'c2', 1.2
union all select 6, 'n1', 'c2', 1.2
union all select 7, 'n2', 'c3', 1.5
union all select 8, 'n2', 'c3', 1.5
union all select 9, 'n2', 'c4', 1.5
union all select 10, 'n2', 'c4', 1.5
union all select 11, 'n2', 'c4', 1.6
union all select 12, 'n2', 'c5', 1.6
select * from T as tmp
where (select count(*) from T where B=tmp.B and C=tmp.C and D=tmp.D)>1--result
N B C D
----------- ---------- ---------- ------------
1 n1 c1 1.1
2 n1 c1 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5(8 row(s) affected)
insert T select 1, 'n1', 'c1', 1.1
union all select 2, 'n1', 'c1', 1.1
union all select 3, 'n1', 'c1', 1.3
union all select 4, 'n1', 'c2', 1.1
union all select 5, 'n1', 'c2', 1.2
union all select 6, 'n1', 'c2', 1.2
union all select 7, 'n2', 'c3', 1.5
union all select 8, 'n2', 'c3', 1.5
union all select 9, 'n2', 'c4', 1.5
union all select 10, 'n2', 'c4', 1.5
union all select 11, 'n2', 'c4', 1.6
union all select 12, 'n2', 'c5', 1.6select * from T
inner join
(
select B, C, D
from T
group by B,C,D
having count(*)>1
)tmp on T.B=tmp.B and T.C=tmp.C and T.D=tmp.D--result
N B C D B C D
----------- ---------- ---------- ------------ ---------- ---------- ------------
1 n1 c1 1.1 n1 c1 1.1
2 n1 c1 1.1 n1 c1 1.1
5 n1 c2 1.2 n1 c2 1.2
6 n1 c2 1.2 n1 c2 1.2
7 n2 c3 1.5 n2 c3 1.5
8 n2 c3 1.5 n2 c3 1.5
9 n2 c4 1.5 n2 c4 1.5
10 n2 c4 1.5 n2 c4 1.5(8 row(s) affected)
在下想要的就是在这张表中查询出B,C,D三个字段的属性完全相同的记录,并且都能显示出来!
楼主,marco08是正解,我的办法是只显示两条,我还以为你只要显示两条,
学艺不精,献丑了
create table #test(N int,B varchar(20),C varchar(20),D decimal(18,3))
insert into #test
select 1,'n1','c1',1.1
union all select 2,'n1','c1',1.1
union all select 3,'n1','c1',1.3
union all select 4,'n1','c2',1.1
union all select 5,'n1','c2',1.2
union all select 6,'n1','c2',1.2
union all select 7,'n2','c3',1.5
union all select 8,'n2','c3',1.5
union all select 9,'n2','c4',1.5
union all select 10,'n2','c4',1.5
union all select 11,'n2','c4',1.6
union all select 12,'n2','c5',1.6--测试
--1,用binary_checksum函数进行处理,此函数不能处理image类似的字段类型值
select * from #test
where binary_checksum(B,C,D) in
(
select binary_checksum(B,C,D) BID from #test
group by binary_checksum(B,C,D)
having count(1)>1
)
--2.用关联语句
select A.* from #test A
inner join #test B
on A.B=B.B and A.C=B.C and A.D=B.D --这里比较麻烦,多少重复的要多少关联,还要有一个主键
and A.N<>B.N
--SQL 2005
with TIDCTE (TID)
as
(
select * from
(
select RANK() over(order by B,C,D) TID from #test
) A
group by TID
having count(1)>1
)
select B.N,B,C,D from TIDCTE A
inner join (select *,RANK() over(order by B,C,D) TID from #test) B
on A.TID=B.TID
/*显示结果
N B C D
1 n1 c1 1.100
2 n1 c1 1.100
5 n1 c2 1.200
6 n1 c2 1.200
7 n2 c3 1.500
8 n2 c3 1.500
9 n2 c4 1.500
10 n2 c4 1.500
*/
--删除测试环境
drop table #test
insert @T select 1,'n1','c1',1.1
union all select 2,'n1','c1',1.1
union all select 3,'n1','c1',1.3
union all select 4,'n1','c2',1.1
union all select 5,'n1','c2',1.2
union all select 6,'n1','c2',1.2
union all select 7,'n2','c3',1.5
union all select 8,'n2','c3',1.5
union all select 9,'n2','c4',1.5
union all select 10,'n2','c4',1.5
union all select 11,'n2','c4',1.6
union all select 12,'n2','c5',1.6select * from @T a
where exists (
select 1 from @t
where b=a.b
and c=a.c
and d=a.d
and n<>a.n
)N B C D
----------- ---------- ---------- ------------
1 n1 c1 1.1
2 n1 c1 1.1
5 n1 c2 1.2
6 n1 c2 1.2
7 n2 c3 1.5
8 n2 c3 1.5
9 n2 c4 1.5
10 n2 c4 1.5(所影响的行数为 8 行)
insert T select 1,'n1','c1',1.1
union all select 2,'n1','c1',1.1
union all select 3,'n1','c1',1.3
union all select 4,'n1','c2',1.1
union all select 5,'n1','c2',1.2
union all select 6,'n1','c2',1.2
union all select 7,'n2','c3',1.5
union all select 8,'n2','c3',1.5
union all select 9,'n2','c4',1.5
union all select 10,'n2','c4',1.5
union all select 11,'n2','c4',1.6
union all select 12,'n2','c5',1.6select * from (select b,c,d from t group by b,c,d having count(1)>1) aa
join t bb on aa.b=bb.b and aa.c=bb.c and aa.d = bb.d
create table T(N int, B varchar(10), C varchar(10), D decimal(10,1))
insert T select 1,'n1','c1',1.1
union all select 2,'n1','c1',1.1
union all select 3,'n1','c1',1.3
union all select 4,'n1','c2',1.1
union all select 5,'n1','c2',1.2
union all select 6,'n1','c2',1.2
union all select 7,'n2','c3',1.5
union all select 8,'n2','c3',1.5
union all select 9,'n2','c4',1.5
union all select 10,'n2','c4',1.5
union all select 11,'n2','c4',1.6
union all select 12,'n2','c5',1.6select * from t aa
where exists(select * from(select b,c,d from t group by b,c,d having count(1)>1) bb
where aa.b=bb.b and aa.c=bb.c and aa.d=bb.d)
insert T select 1,'n1','c1',1.1
union all select 2,'n1','c1',1.1
union all select 3,'n1','c1',1.3
union all select 4,'n1','c2',1.1
union all select 5,'n1','c2',1.2
union all select 6,'n1','c2',1.2
union all select 7,'n2','c3',1.5
union all select 8,'n2','c3',1.5
union all select 9,'n2','c4',1.5
union all select 10,'n2','c4',1.5
union all select 11,'n2','c4',1.6
union all select 12,'n2','c5',1.6select * from t aa
where (select count(*) from t bb where aa.b=bb.b and aa.c=bb.c and aa.d=bb.d)>1
select * from T as tmp
where (select count(*) from T where B=tmp.B and C=tmp.C and D=tmp.D)>1请问各位大虾,谁能帮在下解释一下!!!
where (select count(*) from T where B=tmp.B and C=tmp.C and D=tmp.D)>1---->这个就是通过子查询并关联..也就是本身和本身关联,当然关联之后的记录大于一就说明存在重复的记录...
B=tmp.B and C=tmp.C and D=tmp.D这就是关联的条件...
select a.* from t a,t b
where b.b=a.b
and b.c=a.c
and b.d=a.d
and b.n<>a.n
where b.b=a.b
and b.c=a.c
and b.d=a.d
and b.n<>a.n
你确定吗~~~?
我认为:
select * from t aa
where (select count(*) from t bb where aa.b=bb.b and aa.c=bb.c and aa.d=bb.d)>1