select '总数' key,总数 value from 表 union all select '素',素 from 表 union all select '荤',荤 from 表
declare @demo table (su int,hun int ,total int) insert into @demo(su,hun,total) select 216,42760,2169192select SUM(case when total <>0 then (total) else 0 end ) as total, SUM(case when su <>0 then (su) else 0 end ) as su, SUM(case when hun <>0 then (hun) else 0 end ) as hun from @demo
declare @demo table (su int,hun int ,total int) insert into @demo(su,hun,total) select 216,42760,2169192select SUM(case when total <>0 then (total) else 0 end ) as total, SUM(case when su <>0 then (su) else 0 end ) as su, SUM(case when hun <>0 then (hun) else 0 end ) as hun from @demo
如果数字不固定怎么办呢? select count(1) as 总数, count(case when su>0 then 1 else null end) as 素, count(case when hun>0 then 1 else null end) as 浑 FROM Dish 这是我生成结果集的sql
union all
select '素',素 from 表
union all
select '荤',荤 from 表
insert into @demo(su,hun,total) select 216,42760,2169192select
SUM(case when total <>0 then (total) else 0 end ) as total,
SUM(case when su <>0 then (su) else 0 end ) as su,
SUM(case when hun <>0 then (hun) else 0 end ) as hun
from @demo
insert into @demo(su,hun,total) select 216,42760,2169192select
SUM(case when total <>0 then (total) else 0 end ) as total,
SUM(case when su <>0 then (su) else 0 end ) as su,
SUM(case when hun <>0 then (hun) else 0 end ) as hun
from @demo
select
count(1) as 总数,
count(case when su>0 then 1 else null end) as 素,
count(case when hun>0 then 1 else null end) as 浑
FROM Dish
这是我生成结果集的sql