--题一: 现在要把三个字段有重复的数据提出来.select a.*
from test a,(select a,b,c from test group by a,b,c having count(*)>1)b
where a.a=b.a and a.b=b.b and a.c=b.c
from test a,(select a,b,c from test group by a,b,c having count(*)>1)b
where a.a=b.a and a.b=b.b and a.c=b.c
from test
group by a,b,c
having count(*)>1
from test
group by a,b,c
--having count(*)>1 --去掉这个判断a,b,c重复的条件
一、
select a.*
from test a join
( select a,b,c
from test
group by a,b,c
having count(*)>1
) b
on a.a=b.a and a.b=b.b and a.c=b.c二、
select a,b,c,sum(d) sd,sum(e) se
from test
group by a,b,c
2/select a,b,c sum(case d when null then 0 else d end ),sum(case e when null then 0 else e end ) from test group by a,b,c
where a.a=b.a and a.b=b.b and a.c=b.c
2select a,b,c,d=sum(d),e=sum(e) from test group by a,b,c
select * from test where a + b + c in ( select a + b + c from test group by a,b,c having count(*)>1 )
--因为a、b、c为定长,所以这样写比较好。2、
select a,b,c,sum(d) 'd',sum(e) 'e' from test group by a,b,c
--这问题的条件也些不确切,d字段 sum与count的结果都等于5,所以有也可能如下:
select a,b,c,count(d) 'd',sum(e) 'e' from test group by a,b,c
1.
select a.*
from test t1
where exists ( select 1 from test
where a=t1.a and b=t1.b and c=t1.c
group by a,b,c
having sum(1)>1
)
----------------------------------------
2.
select a,b,c,sum(isnull(d,0)) sd,sum(isnull(e,0)) se
from test
group by a,b,c
select * from test a where not eixsts( select a,b,c from test b group by a,b,c having count(*)>1 where a.a=b.a and a.b=b.b and a.c=b.c)
group by a,b,c
--having count(*) > 1 我觉得可以不要
select a.* from test a join (
select a,b,c from test
group by a,b,c having count(*)>1
) b
on a.a=b.a and a.b=b.b and a.c=b.cThe two:
select a,b,c,sum(d) ,sum(e) from test
group by a,b,c
select a.*
from test a,(select a,b,c from test group by a,b,c having count(*)>1)b
where a.a=b.a and a.b=b.b and a.c=b.c
select a,b,c,d=sum(d),e=sum(e)
from test
group by a,b,c
having count(*)>1呵呵,和我想法一样,就不多说了
where aa.a=bb.a and aa.b=bb.b and aa.c=bb.c and
bb.a=cc.a and bb.b=cc.b and bb.c=cc.c2 select a,b,c,sum(d),sum(e)from test group by a,b,c
from test
group by a,b,c