有表如下
f1 name f2 f3
-------------------------------------
aaa a 5 0
bbb b 2 3
aaa a 0 2
ccc c 0 8
bbb b 22 0
...想搞一个select 对相同f1的 f2和f3字段>0次数进行统计,并按f2>0次数最多的进行排序输出,结果如下:bbb b 2 1
aaa a 1 1
ccc c 0 1不知道说清楚没有-_-,谢谢了
f1 name f2 f3
-------------------------------------
aaa a 5 0
bbb b 2 3
aaa a 0 2
ccc c 0 8
bbb b 22 0
...想搞一个select 对相同f1的 f2和f3字段>0次数进行统计,并按f2>0次数最多的进行排序输出,结果如下:bbb b 2 1
aaa a 1 1
ccc c 0 1不知道说清楚没有-_-,谢谢了
-------------------------------------
insert p select 'aaa','a',5,0
union all select 'bbb','b',2,3
union all select 'aaa','a',0,2
union all select 'ccc','c',0,8
union all select 'bbb','b',22,0
select * from p
select f1,name,sum(case when f2>0 then 1 else 0 end),sum(case when f3>0 then 1 else 0 end) from p group by f1,name
-------------------------------------
insert p select 'aaa','a',5,0
union all select 'bbb','b',2,3
union all select 'aaa','a',0,2
union all select 'ccc','c',0,8
union all select 'bbb','b',22,0
select * from p
select f1,name,sum(case when f2>0 then 1 else 0 end)f2,sum(case when f3>0 then 1 else 0 end)f3 from p group by f1,name order by f2 desc
insert test
select 'aaa', 'a',5,0 union
select 'bbb', 'b',2,3 union
select 'aaa', 'a',0,2 union
select 'ccc', 'c',0,8 union
select 'bbb', 'b',22,0
select f1,name,f2=(select count(1) from test where f2>0 and f1=a.f1),
f3=(select count(1) from test where f3>0 and f1=a.f1)
from test a group by f1,[name] order by f2 desc
drop table test
----------------------
f1 name f2 f3
---------- ---- ----------- -----------
bbb b 2 1
aaa a 1 1
ccc c 0 1
???
http://community.csdn.net/Expert/TopicView1.asp?id=5112581