sql分級匯總，sql分級

sql分級匯總，sql分級

--測試資料create table tb([DB-ID] varchar(10),ENTITY varchar(10),DATE varchar(10),[CUST-NO] int,AMOUNT decimal(10,2),TAX decimal(10,2))insert tb select 'RCHQ','001','2004-11-10',200000,100.00,17.00union all select 'RCHQ','001','2004-11-10',200000,200.00,34.00union all select 'RCHQ','001','2004-11-12',200000,150.00,25.50union all select 'RCHQ','002','2004-11-10',200000,100.00,17.00union all select 'RCHQ','002','2004-11-10',200000,200.00,34.00union all select 'RCHQ','002','2004-11-12',200000,150.00,25.50go --查詢select [DB-ID],ENTITY,DATE,[CUST-NO],AMOUNT,TAXfrom(    select [DB-ID]=case             when grouping([DB-ID])=1 then '合計'            else [DB-ID] end        ,ENTITY=case             when grouping([DB-ID])=1 then ''            when grouping(ENTITY)=1 then '小計'            else ENTITY end        ,DATE=case             when grouping([DB-ID])=1 then ''            when grouping(ENTITY)=1 then ''            when grouping(DATE)=1 then '小計'            else DATE end        ,[CUST-NO]=case             when grouping([DB-ID])=1 then ''            when grouping(ENTITY)=1 then ''            when grouping(DATE)=1 then ''            when grouping([CUST-NO])=1 then '小計'            else cast([CUST-NO] as varchar) end        ,AMOUNT=sum(AMOUNT),TAX=sum(TAX)        ,s1=grouping([DB-ID]),s2=[DB-ID]        ,s3=grouping(ENTITY),s4=ENTITY        ,s5=grouping(DATE),s6=DATE        ,s7=grouping([CUST-NO]),s8=[CUST-NO]    from tb    group by [DB-ID],ENTITY,DATE,[CUST-NO] with rollup    having grouping([CUST-NO])=1    union all    select [DB-ID],ENTITY,DATE,cast([CUST-NO] as varchar),AMOUNT,TAX        ,s1=0,s2=[DB-ID]        ,s3=0,s4=ENTITY        ,s5=0,s6=DATE        ,s7=0,s8=[CUST-NO]    from tb )a order by s1,s2,s3,s4,s5,s6,s7,s8go --刪除測試drop table tb/**DB-IDENTITYDATECUST-NOAMOUNTTAXRCHQ0012004-11-10200000100.0017.00RCHQ0012004-11-10200000200.0034.00RCHQ0012004-11-10小計300.0051.00RCHQ0012004-11-12200000150.0025.50RCHQ0012004-11-12小計150.0025.50RCHQ001小計450.0076.50RCHQ0022004-11-10200000100.0017.00RCHQ0022004-11-10200000200.0034.00RCHQ0022004-11-10小計300.0051.00RCHQ0022004-11-12200000150.0025.50RCHQ0022004-11-12小計150.0025.50RCHQ002小計450.0076.50RCHQ小計900.00153.00合計900.00153.00*/

sql分類查詢並匯總的問題

明白了

第一個分類
select 供應商名稱,count(姓名)，菜譜名稱，菜譜類別，sum(菜譜單價),sum(數量) from cpxxtb group by 供應商名稱,菜譜名稱，菜譜類別

第二個分類
如果是要根據供應商統計，可以寫成：

select 供應商名稱,count(姓名)，sum(菜譜單價),sum(數量) from cpxxtb group by 供應商名稱

最後最總合計：
select sum(菜譜單價),sum(數量) from cpxxtb

OVER
 
SQL分類匯總

標準答案：

select a.Dc_ID,a.Dc_name,
sum(case when use_status='使用中' then 1 else 0 end) 使用中,
sum(case when use_status='未使用' then 1 else 0 end) 未使用
from a,b where a.Dc_ID=b.Dc_ID
group by a.Dc_ID,a.Dc_name