100分，只为见识一下SQL高手！！！
假设存在一张表t1,有字段N,B,C,D,记录如下：
NBCD
1n1c11.1
2n1c11.1
3n1c11.3
4n1c21.1
5n1c21.2
6n1c21.2
7n2c31.5
8n2c31.5
9n2c41.5
10n2c41.5
11n2c41.6
12n2c51.6
请问要想得到以下内容，sql语句应该怎么写？
NBCD
1n1c11.1
2n1c11.1
5n1c21.2
6n1c21.2
7n2c31.5
8n2c31.5
9n2c41.5
10n2c41.5
[解决办法]
select * from t_call where call_id in(
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)
[解决办法]
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
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)
[解决办法]
--建立测试环境
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
/*显示结果
NBCD
1n1c11.100
2n1c11.100
5n1c21.200
6n1c21.200
7n2c31.500
8n2c31.500
9n2c41.500
10n2c41.500
*/
--删除测试环境
drop table #test
[解决办法]
其实方法是有挺多的...还可以用虚拟表等等方法处理,,当然具体情况具体定...
[解决办法]
declare @T table (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 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 行）
[解决办法]
select * from T as tmp
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这就是关联的条件...
