select a,count(a) from test where c=4 group by a用此句應該成!!
select a,count(a) from test group by a,c
use pubs create table #test ( a int, b int, c int ) insert into #test select 1 , 2 , 4 union select 1 , 3 , 4 union select 2 , 3 , 4 union select 1 , 2 , 3 select * from #testa b c ----------- ----------- ----------- 1 2 3 1 2 4 1 3 4 2 3 4(所影响的行数为 4 行)select sum(case a when 1 then a else 0 end) as total from #test where c=4 total ----------- 2(所影响的行数为 1 行)-- use pubs drop table #test
a b c 1 2 4 1 3 4 2 3 4 1 2 3 2 3 4 ........... ............ 很多的纪录 我要得到 a acount 1 2 2 2 ..... ..... 很多的纪录
select a,count(a) from aa where c=4 group by a
select a,count(a) as acount from aa where c=4 group by a having count(a)>1
create table #test
(
a int,
b int,
c int
)
insert into #test
select 1 , 2 , 4
union select 1 , 3 , 4
union select 2 , 3 , 4
union select 1 , 2 , 3
select * from #testa b c
----------- ----------- -----------
1 2 3
1 2 4
1 3 4
2 3 4(所影响的行数为 4 行)select sum(case a when 1 then a else 0 end) as total from #test where c=4 total
-----------
2(所影响的行数为 1 行)-- use pubs drop table #test
1 2 4
1 3 4
2 3 4
1 2 3
2 3 4
...........
............
很多的纪录
我要得到
a acount
1 2
2 2
.....
.....
很多的纪录
from aa
where c=4
group by a
having count(a)>1