declare @table table (inputer varchar(2),status int) insert into @table select '啊',0 union all select '飞',3 union all select '啊',3 union all select '额',3 union all select '啊',3 union all select '飞',0 union all select '额',3 union all select '额',3select inputer, c=SUM(CASe WHEN STATUS IS not null then 1 ELSE 0 end), b=SUM(CASe STATUS WHEN 0 then 1 ELSE 0 end), c=SUM(CASe STATUS WHEN 3 then 1 ELSE 0 end) from @table GROUP BY inputer
/* inputer c b c ------- ----------- ----------- ----------- 啊 3 1 2 额 3 0 3 飞 2 1 1 */
select inputer ,count(*), count(case when status=0 then 1 else 0 end ) as b , count(case when status=3 then 1 else 0 end ) as c from ACCESS group by inputer
是ACCESS数据库,case不能用啊,
如果是access 没有case whenaccess中大概是这样的:select inputer, SUM(switch(STATUS IS not,1,True,0)), SUM(switch(STATUS=0,1,True,0)), SUM(switch(STATUS=3,1,True,0)) from @table GROUP BY inputer
SELECT inputer, COUNT(*) as a, SUM(iif(STATUS = 0, 1, 0)) as b, SUM(iif(STATUS = 3, 1, 0)) as c from test group BY inputer
declare @table table (inputer varchar(2),status int)
insert into @table
select '啊',0 union all
select '飞',3 union all
select '啊',3 union all
select '额',3 union all
select '啊',3 union all
select '飞',0 union all
select '额',3 union all
select '额',3select inputer,
c=SUM(CASe WHEN STATUS IS not null then 1 ELSE 0 end),
b=SUM(CASe STATUS WHEN 0 then 1 ELSE 0 end),
c=SUM(CASe STATUS WHEN 3 then 1 ELSE 0 end)
from @table
GROUP BY inputer
/*
inputer c b c
------- ----------- ----------- -----------
啊 3 1 2
额 3 0 3
飞 2 1 1
*/
count(case when status=0 then 1 else 0 end ) as b
, count(case when status=3 then 1 else 0 end ) as c
from ACCESS
group by inputer
SUM(switch(STATUS IS not,1,True,0)),
SUM(switch(STATUS=0,1,True,0)),
SUM(switch(STATUS=3,1,True,0))
from @table
GROUP BY inputer
from test
group BY inputer