select 提货单位,品种等级=max(品种等级),编号,数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,编号 with rollup
給一個類的例子給你 Create table kc(bt varchar(3),kf varchar(3) ,bc int) insert into kc select 'aaa','gg',20 union all select 'aaa','b',30 union all select 'bbb','d',10 union all select 'bbb','f',50 --------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計' when bt is null then '合計' else bt end) ,kf,sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
select 提货单位,品种等级,编号=max(编号),数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,品种等级 with ROLLUP having 提货单位 is not null or (提货单位 is null and 品种等级 is null)
select 提货单位=case when (grouping(提货单位)=1) then '小计' else 提货单位 end,品种等级,编号=max(编号),数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,品种等级 with ROLLUP
上面發錯了,select 提货单位=(case when 提货单位 is not null and 品种等级 is null then '小計' when 提货单位 is null then '合計' else 提货单位 end) ,品种等级,编号=max(编号),数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,品种等级 with ROLLUP having 提货单位 is not null or (提货单位 is null and 品种等级 is null)
select 提货单位=case when when (grouping(提货单位)=1) and grouping(品种等级)=1 then '合计' else 提货单位 end,品种等级=case when grouping(品种等级)=1 then '小计' else 品种等级 end,编号=max(编号),数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,品种等级 with ROLLUP
select 提貨單位=(case when 提貨單位 is not null and 品種等級 is null then '小計' when 提貨單位 is null then '合計' else 提貨單位 end), 品種等級 = (case when 提貨單位 is null or 品種等級 is null then '' else 提貨單位 end) , 編號=( case when 提貨單位 is null or 品種等級 is null then '' else max(編號) end), 數量=sum(數量), 單價=max(單價), 付款方式=( case when 提貨單位 is null or 品種等級 is null then '' else max(付款方式) end) from tab group by 提貨單位,品種等級 with ROLLUP having 提貨單位 is not null or (提貨單位 is null and 品種等級 is null)
參看這個例子就知道了,用isnull Create table kc(bt varchar(3),kf varchar(3) ,bc int) insert into kc select 'aaa','gg',20 union all select 'aaa','b',30 union all select 'bbb','d',10 union all select 'bbb','f',50 --------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計' when bt is null then '合計' else bt end) ,kf=isnull(kf,''),sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
參看這個例子就知道了,用isnull Create table kc(bt varchar(3),kf varchar(3) ,bc int) insert into kc select 'aaa','gg',20 union all select 'aaa','b',30 union all select 'bbb','d',10 union all select 'bbb','f',50 --------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計' when bt is null then '合計' else bt end) ,kf=isnull(kf,''),sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
簡化一下 select 提貨單位=(case when 提貨單位 is not null and 品種等級 is null then '小計' when 提貨單位 is null then '合計' else 提貨單位 end), 品種等級 = (case when 品種等級 is null then '' else 提貨單位 end) , 編號=( case when 品種等級 is null then '' else max(編號) end), 數量=sum(數量), 單價=max(單價), 付款方式=( case when 品種等級 is null then '' else max(付款方式) end) from tab group by 提貨單位,品種等級 with ROLLUP having 提貨單位 is not null or (提貨單位 is null and 品種等級 is null)
from 表
group by 提货单位,编号
with rollup
Create table kc(bt varchar(3),kf varchar(3) ,bc int)
insert into kc
select 'aaa','gg',20 union all select 'aaa','b',30
union all
select 'bbb','d',10 union all select 'bbb','f',50
--------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計'
when bt is null then '合計' else bt end)
,kf,sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
having 提货单位 is not null or (提货单位 is null and 品种等级 is null)
when 提货单位 is null then '合計' else 提货单位 end)
,品种等级,编号=max(编号),数量=sum(数量),单价=max(单价),付款方式=max(付款方式) from 表 group by 提货单位,品种等级 with ROLLUP
having 提货单位 is not null or (提货单位 is null and 品种等级 is null)
when 提貨單位 is null then '合計'
else 提貨單位
end),
品種等級 = (case when 提貨單位 is null or 品種等級 is null then '' else 提貨單位 end) ,
編號=( case when 提貨單位 is null or 品種等級 is null then '' else max(編號) end),
數量=sum(數量),
單價=max(單價),
付款方式=( case when 提貨單位 is null or 品種等級 is null then '' else max(付款方式) end)
from tab
group by 提貨單位,品種等級 with ROLLUP
having 提貨單位 is not null or (提貨單位 is null and 品種等級 is null)
Create table kc(bt varchar(3),kf varchar(3) ,bc int)
insert into kc
select 'aaa','gg',20 union all select 'aaa','b',30
union all
select 'bbb','d',10 union all select 'bbb','f',50
--------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計'
when bt is null then '合計' else bt end)
,kf=isnull(kf,''),sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
Create table kc(bt varchar(3),kf varchar(3) ,bc int)
insert into kc
select 'aaa','gg',20 union all select 'aaa','b',30
union all
select 'bbb','d',10 union all select 'bbb','f',50
--------------------------下面是例子-----------------------select bt=(case when bt is not null and kf is null then '小計'
when bt is null then '合計' else bt end)
,kf=isnull(kf,''),sum(bc) from kc group by bt,kf with cube having bt is not null or (bt is null and kf is null)
select 提貨單位=(case when 提貨單位 is not null and 品種等級 is null then '小計'
when 提貨單位 is null then '合計'
else 提貨單位
end),
品種等級 = (case when 品種等級 is null then '' else 提貨單位 end) ,
編號=( case when 品種等級 is null then '' else max(編號) end),
數量=sum(數量),
單價=max(單價),
付款方式=( case when 品種等級 is null then '' else max(付款方式) end)
from tab
group by 提貨單位,品種等級 with ROLLUP
having 提貨單位 is not null or (提貨單位 is null and 品種等級 is null)