sql统计结果分组

[xiongbing528]

table A

id uid status
1 1 1
2 1 0
3 1 1
4 2 0
5 2 0
6 2 1
7 3 0
8 3 1
现在要统计出这个结果

uid status_1 status_0
1 2 1
2 1 2
3 1 1

按uid分组，统计每个uid的status状态分别为0 和分别为1的数量

sql该如何写呢

[nicenight]

未测试，请lz测试：

select uid, sum(if(status=1, 1, 0)) as status_1, sum(if(status=0, 1, 0)) as status_0
from A
group by uid

或

select uid, count(if(status=1, 1, null)) as status_1, count(if(status=0, 1, null)) as status_0
from A
group by uid

[小小小小周]

[Quote=引用 1 楼 的回复:]

未测试，请lz测试：
SQL code
select uid, sum(if(status=1, 1, 0)) as status_1, sum(if(status=0, 1, 0)) as status_0
from A
group by uid

或
SQL code
select uid, count(if(status=1, 1, null)) as status_1, cou……
[/Quote]
+1

[rucypli]

select uid, sum(if(status=1, 1, 0)) as status_1, sum(if(status=0, 1, 0)) as status_0
from A
group by uid

[matrix_xd]

select count(uid) from A where status=0 group by uid 
union all 
select count(uid) from A where status=1 group by uid;

[Rotel-刘志东]

select uid, sum(if(status=1, 1, 0)) as status_1, sum(if(status=0, 1, 0)) as status_0
from A
group by uid 

[ACMAIN_CHM]

select uid,sum(status) as status_1,sum(1-status) as status_0
from A
group by uid