SQL Server的Compute和Compute By子句

Compute和Compute By子句

既显示明细，也显示小计

1.Compute

select t.city, t.num

from

(

select 'wuhan' as city, 20 as num

union all

select 'guangzhou' as city, 30 as num

union all

select 'wuhan' as city, 40 as num

union all

select 'guangzhou' as city,  50 as num

) t

compute sum(t.num)

结果：

city num

wuhan 20
guangzhou 30
wuhan 40
guangzhou 50

sum

140

或

select t.city, t.type, t.num

from

(

select 'wuhan' as city, 'book' as type, 20 as num

union all

select 'guangzhou' as city, 'clothes' as type, 30 as num

union all

select 'wuhan' as city, 'clothes' as type, 40 as num

union all

select 'guangzhou' as city, 'book' as type, 50 as num

) t

compute(t.num)

结果：

出错，说明compute只能按照一个维度进行度量（要么city, 要么type），compute by同样。

 

2.Compute By子句--必须配合order by使用

select t.city, t.num

from

(

select 'wuhan' as city, 20 as num

union all

select 'guangzhou' as city, 30 as num

union all

select 'wuhan' as city, 40 as num

union all

select 'guangzhou' as city,  50 as num

) t

order by t.city

compute sum(t.num) by t.city

结果：

city num

guangzhou 30
guangzhou 50

sum

80

city num

wuhan 20
wuhan 40

sum

60