T-SQL利用笛卡尔积累计、累加

T-SQL利用笛卡尔积/窗口函数/表连接累计、累加

【1】 笛卡尔积与子查询解决累计

方法1：笛卡尔积

--原始数据

select templateid,needitem1Count from db_tank..TS_CardMain

--累计数据

select t1.templateId,t1.needitem1Count,sum(t2.needitem1count) sum_num from db_tank..TS_CardMain t1

cross join db_tank..TS_CardMain t2

where t2.templateid <= t1.templateid

group by t1.templateid,t1.needitem1Count

方法2：子查询

select templateid,needitem1Count,

(select sum(needitem1Count) from db_tank..TS_CardMain t2 where t2.templateid<=t1.templateid ) as sum_num

from db_tank..TS_CardMain t1

【2】解决分组累加问题：利用表连接、笛卡尔积、子查询

基于多个分组的分别累加

方法1：笛卡尔积

;with temp1 as (

select 1 as id ,1 as num

union all

select 1 as id ,2 as num

union all

select 1 as id ,3 as num

union all

select 2 as id ,4 as num

union all

select 2 as id ,5 as num

union all

select 2 as id ,6 as num

)

select  t1.id,t1.num,sum(t2.num) sum_num from temp1 t1  join temp1 t2 on  t2.id =t1.id AND t2.num <= t1.num

group by t1.id,t1.num

order by id

　　

 解法2：利用子查询

;with temp1 as (

select 1 as id ,1 as num

union all

select 1 as id ,2 as num

union all

select 1 as id ,3 as num

union all

select 2 as id ,4 as num

union all

select 2 as id ,5 as num

union all

select 2 as id ,6 as num

)

select *,(select sum(num) from temp1 where id=t.id and num <= t.num) sum_num from temp1 t

【3】窗口函数_分析函数（sum over）

sql server 2012及以上可用

rows between unbounded preceding and current row

--【3.1】利用sum() over()嵌套使用
;with temp1 as (

select 1 as id ,1 as num union all

select 1 as id ,2 as num union all

select 1 as id ,3 as num union all

select 2 as id ,4 as num union all

select 2 as id ,5 as num union all

select 2 as id ,6 as num

)

select *,sum(num) over(partition by id order by num asc rows between unbounded preceding and current row) from temp1