cte的一些用法

cte的一些用法"/>

cte的一些用法

今天做一个东西 涉及到要进行小组的小计 然后再加上部门的合计，想了一下还是用了cte来做，话少说，看代码

create table my_testCTE
(
 id int identity(1,1),
 [name] varchar(20),
 [type] varchar(20),
 [value] int
)
go

declare @number int
set @number=1
while @number<200
 begin
  declare @type int
  set @type=@number%5
  insert into my_testCTE
   select 'Name_'+cast(@number as varchar(10)),@type,@number
  set @number=@number+1
 end
go

--测试数据和数据库完成

id          name                 type                 value
----------- -------------------- -------------------- -----------
1           Name_1               1                    1
2           Name_2               2                    2
3           Name_3               3                    3
4           Name_4               4                    4
5           Name_5               0                    5
6           Name_6               1                    6
7           Name_7               2                    7
8           Name_8               3                    8
9           Name_9               4                    9

......

with my_cte as
(
 select *,1 as [order]  from my_testCTE
 union
 select 0,'小组总计',type,sum(value),2 from my_testCTE group by [type]
 union
 select 0,'部门总计',max(type)+1,sum(value),3 from my_testCTE
)
select * from my_cte order by type,[order]

结果如下

159         Name_159             4           159         1
164         Name_164             4           164         1
169         Name_169             4           169         1
174         Name_174             4           174         1
179         Name_179             4           179         1
184         Name_184             4           184         1
189         Name_189             4           189         1
194         Name_194             4           194         1
199         Name_199             4           199         1
0           小组总计                 4           4060        2
0           部门总计                 5           19900       3

不好意思数据太多 看不出效果

效果就是 把小组进行计算 然后把结果放到这个小组的后面

最后再加上这个部门的

O