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

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

【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】表连接与笛卡尔积

　　基于多个分组的分别累加

;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】窗口函数_分析函数

--【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
　　

　　

 

 

转载于:https://www.cnblogs.com/gered/p/9773566.html