假设存在一张表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
------解决方案--------------------
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
----------- ---------- ---------- ------------ ---------- ---------- ------------