select name,shop,zk,sum(amount)
from mytable
group by name,shop,zk;
能得到除小计、合计之外的纪录,如果你是要做报表的话,通常的报表工具都提供统计功能,不需要在sql中返回统计值。
from mytable
group by name,shop,zk;
能得到除小计、合计之外的纪录,如果你是要做报表的话,通常的报表工具都提供统计功能,不需要在sql中返回统计值。
from mytable
group by name,shop,zk;
select name,shop,sum(amount)
from mytable
group by name,shop;
我试着写成下面的语句,即不管ZK的字段值如何,只要name shop字段相同即合计到一条:
select CASE WHEN (GROUPING(name) =1 and GROUPING(shop) =1) THEN '合计' WHEN (GROUPING(name) = 1 and GROUPING(shop) = 0) THEN '小计' ELSE ISNULL(name, '') END AS name,CASE WHEN (GROUPING(shop) = 1) THEN '' ELSE ISNULL(shop, '') END AS shop,CASE WHEN (GROUPING(name) = 1) THEN 0 ELSE max(zk) END AS zkl,sum(sl) as sl from mytable group by shop,name with rollup。
现在我希望只有name shop zk都相同才合并到一条纪录,而我又不想再加一层的grouping,即实现上面所说的效果,怎么用一句话实现呢?(用两句话以上是可以实现,只是不知能不能用一句话实现)
when zk=100000001 then '合计'
else name
end as name,
case when zk=100000000 then '小计'
when zk=100000001 then ''
else shop
end as shop,
case when zk=100000000 or zk=100000001 then null
else zk
end as zk,amount
from (
select name,shop,zk,sum(amount) as amount
from mytable
group by name,shop,zk
union all
select name,shop,zk=100000000,sum(amount) as amount
from mytable
group by name,shop
union all
select name=max(Name),shop=max(Name),zk=100000001,sum(amount) as amount
from mytable
) as a
order by Name,Shop,zk这个语句想的我掉了几根头发!
when zk=100000001 then '合计'
else name
end as name,
case when zk=100000000 then '小计'
when zk=100000001 then ''
else shop
end as shop,
case when zk=100000000 or zk=100000001 then null
else zk
end as zk,amount
from (
select name,shop,zk,sum(amount) as amount
from mytable
group by name,shop,zk
union all
select name,shop,zk=100000000,sum(amount) as amount
from mytable
group by name,shop
union all
select name=max(Name),shop=max(shop),zk=100000001,sum(amount) as amount
from mytable
) as a
order by Name,Shop,zk
select name,shop,zk,amount from
(
select
CASE
WHEN (GROUPING(name) = 1 and GROUPING(zk) =1 and GROUPING(shop) =1) THEN '--'
WHEN (GROUPING(name) = 1 and GROUPING(zk) =0 and GROUPING(shop) =1) THEN '--'
ELSE ISNULL(name, '') END AS name,
CASE
WHEN (GROUPING(shop) = 1 and GROUPING(name) = 1 and GROUPING(zk) =1) THEN '合计'
WHEN (GROUPING(shop) = 1 and GROUPING(name) = 0 and GROUPING(zk) =1) THEN '小计'
WHEN (GROUPING(shop) = 1 and GROUPING(name) = 0 and GROUPING(zk) =0) THEN '小计'
ELSE ISNULL(shop, '') END AS shop,
CASE WHEN (GROUPING(zk) = 1) THEN '' ELSE ISNULL(zk,'') END AS zk,
sum(amount) as amount
from mytable
group by name,shop,zk
with rollup
) as A
where (zk<>0 and shop<>'小计') or shop='合计' or shop='小计'