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(order by num) as sum_num from temp1 
--select *,sum(num) over(partition by id order by num asc rows between unbounded preceding and current row) from temp1

 　　

【4】笛卡尔积的其他妙用

【4.1】自连接

需求：

　　

 

 

 解决：

　　

 

 

【4.2】笛卡尔积+递归构造日期

需求：

 CREATE TABLE testtable(ID int identity(1,1), NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
 
 INSERT INTO testtable 
 VALUES('bm1','2021-01-10 20:57:02','2021-01-11 10:08:29')
 ,('bm2','2021-01-08 17:23:17','2021-01-10 11:54:23')

 select * from testtable

ID NNAME begintime endtime
1 bm1 2021-01-10 20:57:02.000 2021-01-11 10:08:29.000
2 bm2 2021-01-08 17:23:17.000 2021-01-10 11:54:23.000

按照7:30 到 19:30 为白班 ，19:30 到7:30为晚班拆分为：

ID NNAME begintime endtime remark
1 bm1 2021-01-10 20:57:02.000 2021-01-11 06:59:59.000 2021-01-10 晚班
2 bm1 2021-01-11 07:30:00.000 2021-01-11 10:08:29.000 2021-01-11 白班
3 bm2 2021-01-08 17:23:17.000 2021-01-08 18:59:59.000 2021-01-08 白班
4 bm2 2021-01-08 19:30:00.000 2021-01-09 06:59:59.000 2021-01-08 晚班
5 bm2 2021-01-09 07:30:00.000 2021-01-09 18:59:59.000 2021-01-09 白班
6 bm2 2021-01-09 19:30:00.000 2021-01-10 06:59:59.000 2021-01-09 晚班
7 bm2 2021-01-10 07:30:00.000 2021-01-10 11:54:23.000 2021-01-10 白班

解决：

思路，代码见下面
（1）（即递归CTE，t1表）根据表中最小时间、和最大时间，构造出这段时间所有的上下班时间点行，包含最小时间前一天 和 最大时间后一天；
（2）把构造的每一行都与实际表中的行笛卡尔积，这样我们的每一行都对应上区间的所有白班夜班情况时间段；在大表上建议修改表连接，已亲测没有问题。这样可以及大幅度减少无效行结果集数量；
      from t1
      cross join #a t2
优化改成
      from t1
      join #a t2 on (t2.begintime-t1.endtime<0.5) and (t1.endtime - t2.endtime<0.5)
（3） where 条件，二度精准筛选，提取我们想要的行
（4）select 中的 case when，根据你的需求，做出判断什么情况，应该拿什么值；

--
 
use tempdb;
if object_id('A') is null
    drop table #a
CREATE TABLE #A(ID int identity(1,1), NNAME VARCHAR(20), begintime DATEtime, endtime DATEtime);
   
INSERT INTO #A 
VALUES('bm1','2021-01-10 20:57:00','2021-01-11 10:08:29')
 ,('bm2','2021-01-08 17:23:17','2021-01-10 11:54:23')
 ,('bm3','2021-01-08 07:23:17','2021-01-08 07:54:23')
 ,('bm4','2021-01-09 19:23:17','2021-01-09 19:54:23')

select *　from #a 
---------------------
declare @endtime datetime
select @endtime = max(endtime) from #a
;with t1 as ( --构造扩展
    select cast(convert(varchar(10),min(begintime)-1,120)+' 07:30:00' as datetime) as begintime   --这里修改过 min(begintime)-1,120)
    ,cast(convert(varchar(10),min(begintime)-1,120)+' 19:30:00' as datetime) as endtime          --这里修改过  min(begintime)-1,120)
    from #a
    union all
    select dateadd(hour,12,begintime),dateadd(hour,12,endtime) from t1 
    where begintime< @endtime
)
 
select t2.*,
case when convert(char(10),t1.begintime,120)!=convert(char(10),t2.begintime,120) and (t2.begintime-t1.begintime>0.5) then t1.begintime  --判断是否是跨天，这里修改过，新增--》and (t2.begintime-t1.begintime>0.5)
     when t2.begintime>t1.begintime then t2.begintime  
     else t1.begintime
     end as new_begintime
,case when convert(char(10),t1.endtime,120)!=convert(char(10),t2.endtime,120) and (t2.endtime-t1.endtime>0.5) then t1.endtime  -- 这里修改过， 新增--》and (t2.endtime-t1.endtime>0.5)
     when t2.endtime>t1.endtime then t1.endtime
     else t2.endtime
     end as new_endtime
,case when convert(varchar,t1.endtime,8)='07:30:00' then '夜班' 
 else '白班' 
 end as remark
from t1
join #a t2 on (t2.begintime-t1.endtime<0.5) and (t1.endtime - t2.endtime<0.5)
where t1.endtime>t2.begintime and t1.endtime - t2.begintime<0.5 --从开始时间判断
or (t1.endtime>t2.endtime and t1.endtime-t2.endtime <0.5) --从结束时间判断
or (t1.endtime > t2.begintime and t1.endtime < t2.endtime and t2.endtime-t1.endtime>0.5) --跨天判断
or (t1.begintime > t2.begintime and t1.begintime < t2.endtime and t2.endtime-t1.begintime>0.5) --跨天判断
order by t2.begintime desc,new_begintime

--

 

　　

 

【4.3】控制补全成上面没有空行的值

来源：sql server 技术群，代码参考

需求 ：

　　

 

 

if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
select * into #temp from 
(
select '1'as  'id','44562'as  'date','1'as 's'     union all
select '2'as  'id','44563'as  'date',''as 's'     union all
select '3'as  'id','44564'as  'date',''as 's'     union all
select '4'as  'id','44565'as  'date','5'as 's'     union all
select '5'as  'id','44566'as  'date',''as 's'     union all
select '6'as  'id','44567'as  'date',''as 's'   
) as A


-- 实现
select *,
(
    select s from #temp t 
    where id=(
                select max(id) from #temp q 
                where q.id<=t1.id and q.s!='' 
            ) 
) as new_value
from #temp t1 

　　

 

 update 语句