玩玩SQL~查询当月排班各区间的班别~SQL改善完成!

恩~遇到一个不知道如何用纯SQL写法的当月排班班别区间

只能想到用T-SQL来达成写法...

先给前置资料的SQL

declare @Shift table(
Name nvarchar(20)
,SetDate date
,Code nvarchar(5)
)
insert into @Shift
values(\'小明\',\'2024/10/1\',\'A\'),(\'小明\',\'2024/10/2\',\'A\'),(\'小明\',\'2024/10/3\',\'A\'),(\'小明\',\'2024/10/4\',\'A\')
,(\'小明\',\'2024/10/5\',\'休\'),(\'小明\',\'2024/10/6\',\'休\'),(\'小明\',\'2024/10/7\',\'A\'),(\'小明\',\'2024/10/8\',\'A\')
,(\'小明\',\'2024/10/9\',\'A\'),(\'小明\',\'2024/10/10\',\'休\'),(\'小明\',\'2024/10/11\',\'A\'),(\'小明\',\'2024/10/12\',\'休\')
,(\'小明\',\'2024/10/13\',\'休\'),(\'小明\',\'2024/10/14\',\'B\'),(\'小明\',\'2024/10/15\',\'B\'),(\'小明\',\'2024/10/16\',\'B\')
,(\'小明\',\'2024/10/17\',\'B\'),(\'小明\',\'2024/10/18\',\'B\'),(\'小明\',\'2024/10/19\',\'休\'),(\'小明\',\'2024/10/20\',\'休\')
,(\'小明\',\'2024/10/21\',\'A\'),(\'小明\',\'2024/10/22\',\'A\'),(\'小明\',\'2024/10/23\',\'A\'),(\'小明\',\'2024/10/24\',\'A\')
,(\'小明\',\'2024/10/25\',\'A\'),(\'小明\',\'2024/10/26\',\'休\'),(\'小明\',\'2024/10/27\',\'休\'),(\'小明\',\'2024/10/28\',\'A\')
,(\'小明\',\'2024/10/29\',\'A\'),(\'小明\',\'2024/10/30\',\'A\'),(\'小明\',\'2024/10/31\',\'A\')
,(\'大白\',\'2024/10/1\',\'特\'),(\'大白\',\'2024/10/2\',\'特\'),(\'大白\',\'2024/10/3\',\'B\'),(\'大白\',\'2024/10/4\',\'B\')
,(\'大白\',\'2024/10/5\',\'休\'),(\'大白\',\'2024/10/6\',\'休\'),(\'大白\',\'2024/10/7\',\'B\'),(\'大白\',\'2024/10/8\',\'B\')
,(\'大白\',\'2024/10/9\',\'B\'),(\'大白\',\'2024/10/10\',\'休\'),(\'大白\',\'2024/10/11\',\'B\'),(\'大白\',\'2024/10/12\',\'休\')
,(\'大白\',\'2024/10/13\',\'休\'),(\'大白\',\'2024/10/14\',\'A\'),(\'大白\',\'2024/10/15\',\'A\'),(\'大白\',\'2024/10/16\',\'A\')
,(\'大白\',\'2024/10/17\',\'A\'),(\'大白\',\'2024/10/18\',\'A\'),(\'大白\',\'2024/10/19\',\'休\'),(\'大白\',\'2024/10/20\',\'休\')
,(\'大白\',\'2024/10/21\',\'B\'),(\'大白\',\'2024/10/22\',\'B\'),(\'大白\',\'2024/10/23\',\'B\'),(\'大白\',\'2024/10/24\',\'B\')
,(\'大白\',\'2024/10/25\',\'B\'),(\'大白\',\'2024/10/26\',\'休\'),(\'大白\',\'2024/10/27\',\'休\'),(\'大白\',\'2024/10/28\',\'B\')
,(\'大白\',\'2024/10/29\',\'B\'),(\'大白\',\'2024/10/30\',\'B\'),(\'大白\',\'2024/10/31\',\'B\')

--A代表 08-16 早班时段
--B代表 13-21 午班时段

--想要的结果：
--大白 , B:1~13、A:14~20、B:21~31
--小明 , A:1~13、B:14~20、A:21~31

我用T-SQL后，得到此结果~

看有哪位高手比较厉害的SQL写法...^^

我再分享我写的T-SQL

好像我表达的不好~我贴出T-SQL好了~看如何改善成为纯SQL

我也把直转横列出~比较看得懂..

select *
from (
select Name
,day(SetDate) DayNum
,Code
from @Shift
) k
pivot (
max(Code)
for DayNum in(
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
,[11],[12],[13],[14],[15],[16],[17],[18]
,[19],[20],[21],[22],[23],[24],[25],[26]
,[27],[28],[29],[30],[31]
)
) p

T-SQL语法的写法....0.0

declare @ShiftMonth table(
Name nvarchar(20)
,MonthCode nvarchar(max)
)

declare @ShiftName table(
Sort int
,Name nvarchar(20)
)

declare @ShiftSet table(
Name nvarchar(20)
,SetDate date
,Code nvarchar(5)
,LastDate date
,LastCode nvarchar(5)
)

declare @Count int = 0
declare @Code nvarchar(5) = \'\'
declare @LastCode nvarchar(5) = \'\'
declare @StartDate date,@EndDate date
declare @UpdStartDate date,@UpdEndDate date
declare @i int = 0,@n int=0
declare @MonthCode nvarchar(max) = \'\'
declare @MonthStart date = \'2024/10/1\'
declare @MonthEnd date = dateadd(day,-1,dateadd(month,1,@MonthStart))

insert into @ShiftName
select Row_Number()Over(order by Name) Sort
,Name
from @Shift
where SetDate between @MonthStart and @MonthEnd
group by Name

declare @NameCount int = isNull((select count(0) from @ShiftName),0)
declare @Name nvarchar(20)
declare @k int = 0

while(@k<@NameCount)
begin
set @k = @k + 1
select @Name=Name
from @ShiftName
where Sort = @k

insert into @ShiftSet
select *
,isNull((
select top 1 b.SetDate
from @Shift b
where a.Name = b.Name
and Code not in(\'休\',\'特\')
and a.SetDate < b.SetDate
),a.SetDate) LastDay
,isNull((
select top 1 b.Code
from @Shift b
where a.Name = b.Name
and Code not in(\'休\',\'特\')
and a.SetDate < b.SetDate
),a.Code) LastCode
from @Shift a
where Code not in(\'休\',\'特\')
and Name = @Name

set @i = 0
set @n = 0
set @Count = isNull((select count(0) from @ShiftSet),0)
set @MonthCode = \'\'

set @StartDate = @MonthStart

while(@i<@Count)
begin
set @i = @i + 1

if(@i=1)
begin
select top 1 @EndDate=LastDate
,@Code=Code
,@LastCode=LastCode
from @ShiftSet
order by SetDate
end

select @UpdStartDate=SetDate
,@UpdEndDate=dateadd(day,-1,LastDate)
,@Code=Code
,@LastCode=LastCode
from (
select Row_Number()Over(order by SetDate) Sort
,*
from @ShiftSet
) k
where Sort = @i
and Code<>LastCode

if(@Code<>@LastCode)
begin
set @n = @n + 1
set @EndDate = @UpdEndDate
if(@MonthCode=\'\')
begin
set @MonthCode = Convert(varchar,@Code) + \':\' + Convert(varchar,Day(@StartDate)) + \'~\' + Convert(varchar,Day(@EndDate))
end
else
begin
set @MonthCode = @MonthCode + \',\' + Convert(varchar,@Code) + \':\' + Convert(varchar,Day(@StartDate)) + \'~\' + Convert(varchar,Day(@EndDate))
end
set @StartDate = dateadd(day,1,@EndDate)
set @Code = @LastCode
end
end

if(@Count>0)
begin
select @EndDate=max(SetDate)
from @Shift
where Name = @Name

if(@MonthCode=\'\')
begin
set @MonthCode = Convert(varchar,@Code) + \':\' + Convert(varchar,Day(@StartDate)) + \'~\' + Convert(varchar,Day(@EndDate))
end
else
begin
set @MonthCode = @MonthCode + \',\' + Convert(varchar,@Code) + \':\' + Convert(varchar,Day(@StartDate)) + \'~\' + Convert(varchar,Day(@EndDate))
end

insert into @ShiftMonth
select @Name
,@MonthCode

delete from @ShiftSet
end

end

select *
from @ShiftMonth

10/14号更新

谢谢一级屠猪士用 WITH 方式提醒原来 WITH 的用法，是会继承上个资料表的查询，生成独立的资料表谢谢 rogeryao、pilipala 想法，将排班的班别改为连续班别，会比较简单判断由其知道 2022 有个新函数【IGNORE NULLS】更简单应用填入班别

我这个SQL版本比较旧是2016版本，有些新函数不能使用~只能写比较多SQL来查询了@@..

经改善的SQL如下，谢谢三位高手的指导~

WITH
NewShift as (
select Name
,SetDate
,isNull((
select top 1 b.Code
from @Shift b
where a.Name = b.Name
and b.Code not in(N\'特\',N\'休\')
and a.SetDate >= b.SetDate
order by b.SetDate desc
),(
select top 1 b.Code
from @Shift b
where a.Name = b.Name
and b.Code not in(N\'特\',N\'休\')
and a.SetDate < b.SetDate
order by b.SetDate desc
)) Code
from @Shift a
)
,CTE01 AS (
SELECT M.Name,M.SetDate,M.Code
,1 + SUM(M.Num) OVER (PARTITION BY M.Name ORDER BY M.SetDate) AS No
FROM (
SELECT NewShift.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM NewShift
) M
)
,CTE02 AS (
SELECT Name,Code,No
,Min(SetDate) AS MinDate
,Max(SetDate) AS MaxDate
FROM CTE01
GROUP BY Name,Code,No
)
,CTE03 AS (
SELECT Name,No,Code
,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE02
)

SELECT Name
,stuff((
select \',\' + PartStr
from CTE03 b
where a.Name = b.Name
order by no
for xml path(\'\')
),1,1,\'\') as AllStr
--,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE03 a
GROUP BY Name

谢谢 rogeryao 再次测试国外网站 db<>fiddle 测试，因为文化特性要补上 N 来修正语言问题

3 个回答

旧至新
新至旧
最高Like数

一级屠猪士

iT邦大师 1 级 ‧ 2024-10-13 20:59:25

最佳解答

按我前往

with t1 as (
select boy, sdate
     , case when code = \'休\' or  code = \'特\' then null
       else code
       end
  from it1013
), t2 as (
select *
     , sum(case when code is null then 0 else 1 end) over(order by boy, sdate) as code_grp
  from t1
), t3 as (
select boy, sdate
     , first_value(code) over(partition by code_grp order by sdate) as new_code
  from t2
), t4 as (
select boy, sdate, new_code
     , sum(case when new_code is null then 0 else 1 end) over(order by boy, sdate desc) as new_code_grp
  from t3
), t5 as (
select boy, sdate
     , first_value(new_code) over(partition by new_code_grp order by sdate desc) as new_code2
  from t4
), t6 as (
select boy, sdate
     , new_code2
     , lag(new_code2) over(partition by boy order by sdate) lagcode
  from t5
), t7 as (
select boy, sdate, new_code2 as code
     , case when new_code2 = \'A\' and lagcode = \'B\' then true
            when new_code2 = \'B\' and lagcode = \'A\' then true
            else false
       end as cut
  from t6
), t8 as (
select boy, sdate, code, date_part(\'day\', sdate)::int as cutdate
  from t7
 where cut
    or date_part(\'day\', sdate)::int = 1
), t9 as (
select boy, code, cutdate, coalesce(lead(cutdate) over (partition by boy order by sdate) - 1, 31) as enddate
  from t8
) , t10 as (
select boy
     , cutdate
     , code || \':\' || cutdate::text || \'~\' || enddate::text as datestr
  from t9
)
select boy
     , string_agg(datestr, \',\' order by cutdate)
  from t10
 group by boy;

result:

 boy  |       string_agg
------+------------------------
 大白 | B:1~13,A:14~20,B:21~31
 小明 | A:1~13,B:14~20,A:21~31
(2 rows)

看更多先前的...收起先前的...

一级屠猪士

iT邦大师 1 级 ‧
2024-10-13 21:12:14

完整的应该是在 coalesce(lead(cutdate) over (partition by boy order by sdate) - 1, 31)
这里的31 要用日期函数计算出当月的最后一天.
但是我们使用的DB 不同, 这对纯真的人大大来说,小菜一碟,所以我就偷懒直接使用31了.

完整的应该是在 ```coalesce(lead(cutdate) over (partition by boy order by sdate) - 1, 31)``` 这里的31 要用日期函数计算出当月的最后一天. 但是我们使用的DB 不同, 这对纯真的人大大来说,小菜一碟,所以我就偷懒直接使用31了.

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-13 21:13:17

挖~~SQL化厉害~

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-13 21:17:41

用 with 这个的确应用~我还没有熟练XD..

修改

一级屠猪士

iT邦大师 1 级 ‧
2024-10-13 21:24:35

用 window function, sum(), first_value(), lag(), lead(), 组合拳.string_agg() 聚合.
先把休假变成 null
sum(), first_value() 来填充 null, 正向一次,反向一次(大白月初两天), 然后lag(), 然后判断差异,算出分切点日期,
然后再将分切点日期, lead 上去减一天得到每组的区间,
然后将区间变成 sdate~edate ,然后 string_agg 聚合起来.

用 window function, sum(), first_value(), lag(), lead(), 组合拳.string_agg() 聚合. 先把休假变成 null sum(), first_value() 来填充 null, 正向一次,反向一次(大白月初两天), 然后lag(), 然后判断差异,算出分切点日期, 然后再将分切点日期, lead 上去减一天得到每组的区间, 然后将区间变成 sdate~edate ,然后 string_agg 聚合起来.

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-13 22:27:03

好的~我想想with如何应用~谢谢

修改

pilipala

iT邦研究生 5 级 ‧ 2024-10-13 21:05:47

正常流程应该是，理论班表 => 实际出勤班表 => 帮 HR 勾稽两者异常之处，让 HR 去釐清判断是否合理

休假日前后是 A、B 班别时，有逻辑可以判断是归属哪个班别吗？

纯真的人

iT邦大师 1 级 ‧
2024-10-13 21:16:24

班别是若遇当月休假换班别~就归属前一个班别~
例如 10/13 休 14/14 换成A班
那么 10/13 会往前抓到最后一天的班别若是B班~
那么10/13就算B班薪资

但是像月初就特休就会往后抓班别来判断

至于HR勾稽~他们轮班比较没办做吧~
因为他们要排下个月班时~
会一个组讨论~哪天谁可以上班~谁有事休假~
过年是没有休假的~用轮班处理(会给特别加班费)

我这个题目範例是用固定排班来叙述的~
不是用轮班比较麻烦输入@@...

班别是若遇当月休假换班别~就归属前一个班别~ 例如 10/13 休 14/14 换成A班 那么 10/13 会往前抓到最后一天的班别若是B班~ 那么10/13就算B班薪资 但是像月初就特休就会往后抓班别来判断 至于HR勾稽~他们轮班比较没办做吧~ 因为他们要排下个月班时~ 会一个组讨论~哪天谁可以上班~谁有事休假~ 过年是没有休假的~用轮班处理(会给特别加班费) 我这个题目範例是用固定排班来叙述的~ 不是用轮班比较麻烦输入@@...

修改

pilipala

iT邦研究生 5 级 ‧
2024-10-14 03:25:40

Step1：把特、休字样改成所属班别，使用 SQL 2022 视窗函数新参数 IGNORE NULLS 来整理
Step2：连续日期、连续班别来判断群组
Step3：产生对应需求的资料

; 
WITH T1 AS 
( 
	SELECT * ,
		IIF(Code IN (\'A\' , \'B\') , Code , NULL) AS NewCode
	FROM @Shift
)
, T2 AS
(
	SELECT * ,
		COALESCE(
			NewCode ,
			LAG(NewCode) IGNORE NULLS OVER (PARTITION BY Name ORDER BY SetDate) ,
			LEAD(NewCode) IGNORE NULLS OVER (PARTITION BY Name ORDER BY SetDate)) AS GroupNO
	FROM T1
)
, T3 AS 
(
	SELECT 
		* ,
		DATEADD(
			d , 
			ROW_NUMBER() OVER (PARTITION BY Name , GroupNO ORDER BY SetDate) * -1 , 
			SetDate) AS GroupDate
	FROM T2
)
, T4 AS
(
	SELECT 
			T3.Name , 
			T3.GroupDate ,
			T3.GroupNO ,
			MIN(SetDate) AS MinDate ,
			T3.GroupNO + \'：\' + CAST(DAY(MIN(SetDate)) AS varchar(2)) + \'~\' + CAST(DAY(MAX(SetDate)) AS varchar(2)) AS CodeInfo
	FROM T3
	GROUP BY Name , GroupDate , GroupNO
)
SELECT
	Name ,
	STRING_AGG(CodeInfo , \' , \') WITHIN GROUP (ORDER BY MinDate)
FROM T4
GROUP BY Name

Step1：把特、休字样改成所属班别，使用 SQL 2022 视窗函数新参数 IGNORE NULLS 来整理 Step2：连续日期、连续班别来判断群组 Step3：产生对应需求的资料 ``` ; WITH T1 AS ( SELECT * , IIF(Code IN ('A' , 'B') , Code , NULL) AS NewCode FROM @Shift ) , T2 AS ( SELECT * , COALESCE( NewCode , LAG(NewCode) IGNORE NULLS OVER (PARTITION BY Name ORDER BY SetDate) , LEAD(NewCode) IGNORE NULLS OVER (PARTITION BY Name ORDER BY SetDate)) AS GroupNO FROM T1 ) , T3 AS ( SELECT * , DATEADD( d , ROW_NUMBER() OVER (PARTITION BY Name , GroupNO ORDER BY SetDate) * -1 , SetDate) AS GroupDate FROM T2 ) , T4 AS ( SELECT T3.Name , T3.GroupDate , T3.GroupNO , MIN(SetDate) AS MinDate , T3.GroupNO + '：' + CAST(DAY(MIN(SetDate)) AS varchar(2)) + '~' + CAST(DAY(MAX(SetDate)) AS varchar(2)) AS CodeInfo FROM T3 GROUP BY Name , GroupDate , GroupNO ) SELECT Name , STRING_AGG(CodeInfo , ' , ') WITHIN GROUP (ORDER BY MinDate) FROM T4 GROUP BY Name ```

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-14 08:56:35

喔~了解~
你採用的是删除特、休，改为Null值
利用 SQL 2022新视窗函数 IGNORE NULLS 填满上次日期的班别
再利用连续班别，算出区段~

我的SQL版本比较旧~
所以重点就是变成连续班别填满，SQL就会比较简单

喔~了解~ 你採用的是删除特、休，改为Null值 利用 SQL 2022新视窗函数 IGNORE NULLS 填满上次日期的班别 再利用连续班别，算出区段~ 我的SQL版本比较旧~ 所以重点就是变成连续班别填满，SQL就会比较简单

修改

rogeryao

iT邦超人 7 级 ‧ 2024-10-13 23:24:33

Code : 只有班别代号，不含休假或特休

CREATE TABLE da (
Name nvarchar(20),
SetDate date,
Code nvarchar(5));

INSERT INTO da (Name, SetDate,Code) VALUES
(N\'小明\',\'2024/10/1\',\'A\'),(N\'小明\',\'2024/10/2\',\'A\'),(N\'小明\',\'2024/10/3\',\'A\'),(N\'小明\',\'2024/10/4\',\'A\')
,(N\'小明\',\'2024/10/5\',N\'A\'),(N\'小明\',\'2024/10/6\',N\'A\'),(N\'小明\',\'2024/10/7\',\'A\'),(N\'小明\',\'2024/10/8\',\'A\')
,(N\'小明\',\'2024/10/9\',\'A\'),(N\'小明\',\'2024/10/10\',N\'A\'),(N\'小明\',\'2024/10/11\',\'A\'),(N\'小明\',\'2024/10/12\',N\'A\')
,(N\'小明\',\'2024/10/13\',N\'A\'),(N\'小明\',\'2024/10/14\',\'B\'),(N\'小明\',\'2024/10/15\',\'B\'),(N\'小明\',\'2024/10/16\',\'B\')
,(N\'小明\',\'2024/10/17\',\'B\'),(N\'小明\',\'2024/10/18\',\'B\'),(N\'小明\',\'2024/10/19\',N\'B\'),(N\'小明\',\'2024/10/20\',N\'B\')
,(N\'小明\',\'2024/10/21\',\'A\'),(N\'小明\',\'2024/10/22\',\'A\'),(N\'小明\',\'2024/10/23\',\'A\'),(N\'小明\',\'2024/10/24\',\'A\')
,(N\'小明\',\'2024/10/25\',\'A\'),(N\'小明\',\'2024/10/26\',N\'A\'),(N\'小明\',\'2024/10/27\',N\'A\'),(N\'小明\',\'2024/10/28\',\'A\')
,(N\'小明\',\'2024/10/29\',\'A\'),(N\'小明\',\'2024/10/30\',\'A\'),(N\'小明\',\'2024/10/31\',\'A\')
,(N\'大白\',\'2024/10/1\',N\'B\'),(N\'大白\',\'2024/10/2\',N\'B\'),(N\'大白\',\'2024/10/3\',\'B\'),(N\'大白\',\'2024/10/4\',\'B\')
,(N\'大白\',\'2024/10/5\',N\'B\'),(N\'大白\',\'2024/10/6\',N\'B\'),(N\'大白\',\'2024/10/7\',\'B\'),(N\'大白\',\'2024/10/8\',\'B\')
,(N\'大白\',\'2024/10/9\',\'B\'),(N\'大白\',\'2024/10/10\',N\'B\'),(N\'大白\',\'2024/10/11\',\'B\'),(N\'大白\',\'2024/10/12\',N\'B\')
,(N\'大白\',\'2024/10/13\',N\'B\'),(N\'大白\',\'2024/10/14\',\'A\'),(N\'大白\',\'2024/10/15\',\'A\'),(N\'大白\',\'2024/10/16\',\'A\')
,(N\'大白\',\'2024/10/17\',\'A\'),(N\'大白\',\'2024/10/18\',\'A\'),(N\'大白\',\'2024/10/19\',N\'A\'),(N\'大白\',\'2024/10/20\',N\'A\')
,(N\'大白\',\'2024/10/21\',\'B\'),(N\'大白\',\'2024/10/22\',\'B\'),(N\'大白\',\'2024/10/23\',\'B\'),(N\'大白\',\'2024/10/24\',\'B\')
,(N\'大白\',\'2024/10/25\',\'B\'),(N\'大白\',\'2024/10/26\',N\'B\'),(N\'大白\',\'2024/10/27\',N\'B\'),(N\'大白\',\'2024/10/28\',\'B\')
,(N\'大白\',\'2024/10/29\',\'B\'),(N\'大白\',\'2024/10/30\',\'B\'),(N\'大白\',\'2024/10/31\',\'B\');

WITH CTE01 AS (SELECT M.Name,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name ORDER BY M.SetDate) AS No
FROM (
SELECT da.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM da) M),  
CTE02 AS (
SELECT Name,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE01
GROUP BY Name,Code,No),
CTE03 AS (
SELECT Name,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE02)

SELECT Name,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE03
GROUP BY Name

Demo

休假或特休

CREATE TABLE dc (
Name nvarchar(20),
SetDate date,
Holiday nvarchar(5));

INSERT INTO dc (Name, SetDate,Holiday) VALUES
(N\'小明\',\'2024/10/5\',N\'休\'),
(N\'小明\',\'2024/10/6\',N\'休\'),
(N\'小明\',\'2024/10/10\',N\'休\'),
(N\'小明\',\'2024/10/12\',N\'休\'),
(N\'小明\',\'2024/10/13\',N\'休\'),
(N\'小明\',\'2024/10/19\',N\'休\'),
(N\'小明\',\'2024/10/20\',N\'休\'),
(N\'小明\',\'2024/10/26\',N\'休\'),
(N\'小明\',\'2024/10/27\',N\'休\'),
(N\'大白\',\'2024/10/1\',N\'特\'),
(N\'大白\',\'2024/10/2\',N\'特\'),
(N\'大白\',\'2024/10/5\',N\'休\'),
(N\'大白\',\'2024/10/6\',N\'休\'),
(N\'大白\',\'2024/10/10\',N\'休\'),
(N\'大白\',\'2024/10/12\',N\'休\'),
(N\'大白\',\'2024/10/13\',N\'休\'),
(N\'大白\',\'2024/10/19\',N\'休\'),
(N\'大白\',\'2024/10/20\',N\'休\'),
(N\'大白\',\'2024/10/26\',N\'休\'),
(N\'大白\',\'2024/10/27\',N\'休\');

SELECT F.Name,F.SetDate,F.Holiday,1 + SUM(F.HNum) OVER (
PARTITION BY F.Name ORDER BY F.SetDate) AS HNo
FROM (
SELECT dc.*,
CASE WHEN Holiday = LAG(Holiday,1,Holiday) OVER (
PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS HNum
FROM dc) F

Demo
休假或特休 dc 与 da left join 应该可以产生出图二，
此处 SQL 只便于找出休假或特休群组，不在赘述。

参考资料 : SQL 依时间排序连续资料的分组

Code 可以包含休、特的解法在底下区 :『修正版』

看更多先前的...收起先前的...

纯真的人

iT邦大师 1 级 ‧
2024-10-14 00:05:35

喔~
你採用没有混合休假的排班~
纯粹班别哪到哪~的确是很好查询
休假有另外的资料表纪录在混合进来~

可惜他们班表功能是这样混合的

喔~ 你採用没有混合休假的排班~ 纯粹班别哪到哪~的确是很好查询 休假有另外的资料表纪录在混合进来~ 可惜他们班表功能是这样混合的 ![https://ithelp.ithome.com.tw/upload/images/20241014/20061369il92hfNuOX.png](https://ithelp.ithome.com.tw/upload/images/20241014/20061369il92hfNuOX.png)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-14 00:42:12

班别是若遇当月休假换班别~就归属前一个班别~
例如 10/13 休 14/14 换成A班
那么 10/13 会往前抓到最后一天的班别若是B班~
那么10/13就算B班薪资

『那么 10/13 会往前抓到最后一天的班别若是B班~』
其实,这样的论述可能是有问题的
一般来说应该是先有排班表，不同的班别薪资也会不一样
10/13 请假时排到什么班表，就扣除那个班表的钱
而不是往前推最后一次有上班的班表
仔细算薪资会有差别

或许有一天会发生
10/13 往前推是 B 班
但是人事部门算薪资时是用 A 班计算

班别是若遇当月休假换班别~就归属前一个班别~ 例如 10/13 休 14/14 换成A班 那么 10/13 会往前抓到最后一天的班别若是B班~ 那么10/13就算B班薪资 『那么 10/13 会往前抓到最后一天的班别若是B班~』 其实,这样的论述可能是有问题的 一般来说应该是先有排班表，不同的班别薪资也会不一样 10/13 请假时排到什么班表，就扣除那个班表的钱 而不是往前推最后一次有上班的班表 仔细算薪资会有差别 或许有一天会发生 10/13 往前推是 B 班 但是人事部门算薪资时是用 A 班计算

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-14 01:18:25

这个就看人资订下规则为準啰(通常会发公告，或者写在员工手册里面)~
我们看他怎么解释~就怎样设定了~

这个就看人资订下规则为準啰(通常会发公告，或者写在员工手册里面)~ 我们看他怎么解释~就怎样设定了~

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-14 12:18:20

Code 可以包含休、特

CREATE TABLE da (
Name nvarchar(20),
SetDate date,
Code nvarchar(5));

INSERT INTO da (Name, SetDate,Code) VALUES
(N\'小明\',\'2024/10/1\',\'A\'),(N\'小明\',\'2024/10/2\',\'A\'),(N\'小明\',\'2024/10/3\',\'A\'),(N\'小明\',\'2024/10/4\',\'A\')
,(N\'小明\',\'2024/10/5\',N\'休\'),(N\'小明\',\'2024/10/6\',N\'休\'),(N\'小明\',\'2024/10/7\',\'A\'),(N\'小明\',\'2024/10/8\',\'A\')
,(N\'小明\',\'2024/10/9\',\'A\'),(N\'小明\',\'2024/10/10\',N\'休\'),(N\'小明\',\'2024/10/11\',\'A\'),(N\'小明\',\'2024/10/12\',N\'休\')
,(N\'小明\',\'2024/10/13\',N\'休\'),(N\'小明\',\'2024/10/14\',\'B\'),(N\'小明\',\'2024/10/15\',\'B\'),(N\'小明\',\'2024/10/16\',\'B\')
,(N\'小明\',\'2024/10/17\',\'B\'),(N\'小明\',\'2024/10/18\',\'B\'),(N\'小明\',\'2024/10/19\',N\'休\'),(N\'小明\',\'2024/10/20\',N\'休\')
,(N\'小明\',\'2024/10/21\',\'A\'),(N\'小明\',\'2024/10/22\',\'A\'),(N\'小明\',\'2024/10/23\',\'A\'),(N\'小明\',\'2024/10/24\',\'A\')
,(N\'小明\',\'2024/10/25\',\'A\'),(N\'小明\',\'2024/10/26\',N\'休\'),(N\'小明\',\'2024/10/27\',N\'休\'),(N\'小明\',\'2024/10/28\',\'A\')
,(N\'小明\',\'2024/10/29\',\'A\'),(N\'小明\',\'2024/10/30\',\'A\'),(N\'小明\',\'2024/10/31\',\'A\')
,(N\'大白\',\'2024/10/1\',N\'特\'),(N\'大白\',\'2024/10/2\',N\'特\'),(N\'大白\',\'2024/10/3\',\'B\'),(N\'大白\',\'2024/10/4\',\'B\')
,(N\'大白\',\'2024/10/5\',N\'休\'),(N\'大白\',\'2024/10/6\',N\'休\'),(N\'大白\',\'2024/10/7\',\'B\'),(N\'大白\',\'2024/10/8\',\'B\')
,(N\'大白\',\'2024/10/9\',\'B\'),(N\'大白\',\'2024/10/10\',N\'休\'),(N\'大白\',\'2024/10/11\',\'B\'),(N\'大白\',\'2024/10/12\',N\'休\')
,(N\'大白\',\'2024/10/13\',N\'休\'),(N\'大白\',\'2024/10/14\',\'A\'),(N\'大白\',\'2024/10/15\',\'A\'),(N\'大白\',\'2024/10/16\',\'A\')
,(N\'大白\',\'2024/10/17\',\'A\'),(N\'大白\',\'2024/10/18\',\'A\'),(N\'大白\',\'2024/10/19\',N\'休\'),(N\'大白\',\'2024/10/20\',N\'休\')
,(N\'大白\',\'2024/10/21\',\'B\'),(N\'大白\',\'2024/10/22\',\'B\'),(N\'大白\',\'2024/10/23\',\'B\'),(N\'大白\',\'2024/10/24\',\'B\')
,(N\'大白\',\'2024/10/25\',\'B\'),(N\'大白\',\'2024/10/26\',N\'休\'),(N\'大白\',\'2024/10/27\',N\'休\'),(N\'大白\',\'2024/10/28\',\'B\')
,(N\'大白\',\'2024/10/29\',\'B\'),(N\'大白\',\'2024/10/30\',\'B\'),(N\'大白\',\'2024/10/31\',\'B\');

-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
WITH CTE01 AS (
SELECT da.Name,da.SetDate,
CASE WHEN da.SetDate < K.SetDate THEN K.Code ELSE da.Code END AS Code
FROM da
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS RN,*
FROM da
WHERE Code IN (\'A\',\'B\')) AS K
WHERE RN =1  
) AS K ON K.Name=da.Name),
-- 向下补班别  
CTE02 AS (
SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id,
Name,SetDate,
CASE WHEN Code NOT IN (\'A\',\'B\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT id,Name,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT id,Name,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (SELECT M.Name,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,Code,No),
CTE06 AS (
SELECT Name,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05)

SELECT Name,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE06
GROUP BY Name

Demo

Demo:SQL 2016

Code 可以包含休、特 ``` CREATE TABLE da ( Name nvarchar(20), SetDate date, Code nvarchar(5)); INSERT INTO da (Name, SetDate,Code) VALUES (N'小明','2024/10/1','A'),(N'小明','2024/10/2','A'),(N'小明','2024/10/3','A'),(N'小明','2024/10/4','A') ,(N'小明','2024/10/5',N'休'),(N'小明','2024/10/6',N'休'),(N'小明','2024/10/7','A'),(N'小明','2024/10/8','A') ,(N'小明','2024/10/9','A'),(N'小明','2024/10/10',N'休'),(N'小明','2024/10/11','A'),(N'小明','2024/10/12',N'休') ,(N'小明','2024/10/13',N'休'),(N'小明','2024/10/14','B'),(N'小明','2024/10/15','B'),(N'小明','2024/10/16','B') ,(N'小明','2024/10/17','B'),(N'小明','2024/10/18','B'),(N'小明','2024/10/19',N'休'),(N'小明','2024/10/20',N'休') ,(N'小明','2024/10/21','A'),(N'小明','2024/10/22','A'),(N'小明','2024/10/23','A'),(N'小明','2024/10/24','A') ,(N'小明','2024/10/25','A'),(N'小明','2024/10/26',N'休'),(N'小明','2024/10/27',N'休'),(N'小明','2024/10/28','A') ,(N'小明','2024/10/29','A'),(N'小明','2024/10/30','A'),(N'小明','2024/10/31','A') ,(N'大白','2024/10/1',N'特'),(N'大白','2024/10/2',N'特'),(N'大白','2024/10/3','B'),(N'大白','2024/10/4','B') ,(N'大白','2024/10/5',N'休'),(N'大白','2024/10/6',N'休'),(N'大白','2024/10/7','B'),(N'大白','2024/10/8','B') ,(N'大白','2024/10/9','B'),(N'大白','2024/10/10',N'休'),(N'大白','2024/10/11','B'),(N'大白','2024/10/12',N'休') ,(N'大白','2024/10/13',N'休'),(N'大白','2024/10/14','A'),(N'大白','2024/10/15','A'),(N'大白','2024/10/16','A') ,(N'大白','2024/10/17','A'),(N'大白','2024/10/18','A'),(N'大白','2024/10/19',N'休'),(N'大白','2024/10/20',N'休') ,(N'大白','2024/10/21','B'),(N'大白','2024/10/22','B'),(N'大白','2024/10/23','B'),(N'大白','2024/10/24','B') ,(N'大白','2024/10/25','B'),(N'大白','2024/10/26',N'休'),(N'大白','2024/10/27',N'休'),(N'大白','2024/10/28','B') ,(N'大白','2024/10/29','B'),(N'大白','2024/10/30','B'),(N'大白','2024/10/31','B'); ``` ``` -- 每月 1 日'休'、'特', 找到第一个班别向上补 WITH CTE01 AS ( SELECT da.Name,da.SetDate, CASE WHEN da.SetDate < K.SetDate THEN K.Code ELSE da.Code END AS Code FROM da LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS RN,* FROM da WHERE Code IN ('A','B')) AS K WHERE RN =1 ) AS K ON K.Name=da.Name), -- 向下补班别 CTE02 AS ( SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id, Name,SetDate, CASE WHEN Code NOT IN ('A','B') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT id,Name,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT id,Name,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS (SELECT M.Name,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,Code,No), CTE06 AS ( SELECT Name,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05) SELECT Name,STRING_AGG(PartStr,',') WITHIN GROUP (ORDER BY No) AS AllStr FROM CTE06 GROUP BY Name ``` [Demo](https://dbfiddle.uk/0_Iwm5vB) [Demo:SQL 2016](https://dbfiddle.uk/0z7AEKcL)

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-14 14:51:43

收到 2016 新写法了~谢谢!

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-15 09:25:15

我今天测试你的写法~(班别向上补及班别向下补)
left join效能比较好~
用子查询的方式~跑1万多笔很久快1分钟都没结果(我按中止)
用left join查询，1万多笔满马上就出现了^_^

我今天测试你的写法~(班别向上补及班别向下补) left join效能比较好~ 用子查询的方式~跑1万多笔很久快1分钟都没结果(我按中止) 用left join查询，1万多笔满马上就出现了^_^

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-15 10:14:54

早上本想请你测试执行速度的差异
真是心有灵犀

早上本想请你测试执行速度的差异 真是心有灵犀

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-15 10:23:11

哈~毕竟改程式都有先后顺序~
要等我有时间~才能空处理~

哈~毕竟改程式都有先后顺序~ 要等我有时间~才能空处理~

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-15 10:48:28

你写的这种『子查询』方式
我几乎不使用，都改用 LEFT JOIN 处理
直觉上他是针对每一笔下一道 SQL

你可以用 SQL Server Profiler 看看
2 个 SQL的差异

你写的这种『子查询』方式 我几乎不使用，都改用 LEFT JOIN 处理 直觉上他是针对每一笔下一道 SQL 你可以用 SQL Server Profiler 看看 2 个 SQL的差异

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-15 11:35:04

子查询其实弊端很大~
因为他是针对每一笔~
另开资料表总查询~
所以跑很久~
LEFT JOIN 是先把总表处理好，在比对join有没有而已~
因为他不是独立去查询的~所以会比较快

子查询其实弊端很大~ 因为他是针对每一笔~ 另开资料表总查询~ 所以跑很久~ LEFT JOIN 是先把总表处理好，在比对join有没有而已~ 因为他不是独立去查询的~所以会比较快

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-15 23:38:33

今天有试了 WITH + 加上用你的

WITH
略....
LEFT JOIN (
   略......
)  AS K

跑了一个历史打卡资料看看(4万多笔)，结果跑了22秒...0.0a

后来想了一下~可能WITH会不断递迴关係~

我就移出 WITH 外面

用 declare @Tmp table 暂存一个资料表~1秒左右就跑完了@@...

declare @Tmp table
略....
insert into @Tmp
略....
LEFT JOIN (
   略......
)  AS K

WITH
略....

今天有试了 WITH + 加上用你的 ``` WITH 略.... LEFT JOIN ( 略...... ) AS K ``` 跑了一个历史打卡资料看看(4万多笔)，结果跑了22秒...0.0a 后来想了一下~可能WITH会不断递迴关係~ 我就移出 WITH 外面 用 declare @Tmp table 暂存一个资料表~1秒左右就跑完了@@... ``` declare @Tmp table 略.... insert into @Tmp 略.... LEFT JOIN ( 略...... ) AS K WITH 略.... ```

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-15 23:58:57

可能WITH会不断递迴关係~

应该不会吧
WITH 只是把原本一大串的 SQL分段处理
就上面的 Case 而言也可以写成一大串，但是可读性就变差了，
除错也会变得很困难

历史打卡资料的问题或许跟上面的 Case 有些差异
但是你硬把 SQL 套进去
可能就会不如预期吧

可能WITH会不断递迴关係~ 应该不会吧 WITH 只是把原本一大串的 SQL分段处理 就上面的 Case 而言也可以写成一大串，但是可读性就变差了， 除错也会变得很困难 历史打卡资料的问题或许跟上面的 Case 有些差异 但是你硬把 SQL 套进去 可能就会不如预期吧

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 01:00:47

刚想到上面的 SQL 有个小瑕疵：

-- 向下补班别  
CTE02 AS (
SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id,
CTE03 AS (
...
SELECT id,Name,SetDate,CodeX, 
... 
SELECT id,Name,SetDate,CodeX,
这个 id 是多余的

另外，有个潜藏的问题

PARTITION BY Name 
应该要改成下式，要加入年月(2024-10)
PARTITION BY Name,Left(SetDate,7)
否则，当只查询一个人而且跨月时会有问题。

原本是（小明、大白，10月）
大白 2024-10-01 特
大白 2024-10-02 特
取代休、特
大白 2024-10-01 B
大白 2024-10-02 B
................................................
当（大白，9～10月）
取代休、特会变成
大白 2024-10-01 A
大白 2024-10-02 A

A 是 9月延伸而产生的
Demo:问题示範

刚想到上面的 SQL 有个小瑕疵： ``` -- 向下补班别 CTE02 AS ( SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id, CTE03 AS ( ... SELECT id,Name,SetDate,CodeX, ... SELECT id,Name,SetDate,CodeX, 这个 id 是多余的 ``` 另外，有个潜藏的问题 ``` PARTITION BY Name 应该要改成下式，要加入年月(2024-10) PARTITION BY Name,Left(SetDate,7) 否则，当只查询一个人而且跨月时会有问题。 ``` 原本是（小明、大白，10月） 大白 2024-10-01 特 大白 2024-10-02 特 取代休、特 大白 2024-10-01 B 大白 2024-10-02 B ................................................ 当（大白，9～10月） 取代休、特会变成 大白 2024-10-01 A 大白 2024-10-02 A A 是 9月延伸而产生的 [Demo:问题示範](https://dbfiddle.uk/OB1B6lAn)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 08:39:09

『修正版』

CREATE TABLE da (
Name nvarchar(20),
SetDate date,
Code nvarchar(5));

INSERT INTO da (Name, SetDate,Code) VALUES
(N\'大白\',\'2024/09/1\',\'A\'),(N\'大白\',\'2024/09/2\',\'A\'),(N\'大白\',\'2024/09/3\',\'A\'),(N\'大白\',\'2024/09/4\',\'A\')
,(N\'大白\',\'2024/09/5\',N\'休\'),(N\'大白\',\'2024/09/6\',N\'休\'),(N\'大白\',\'2024/09/7\',\'A\'),(N\'大白\',\'2024/09/8\',\'A\')
,(N\'大白\',\'2024/09/9\',\'A\'),(N\'大白\',\'2024/09/10\',N\'休\'),(N\'大白\',\'2024/09/11\',\'A\'),(N\'大白\',\'2024/09/12\',N\'休\')
,(N\'大白\',\'2024/09/13\',N\'休\'),(N\'大白\',\'2024/09/14\',\'B\'),(N\'大白\',\'2024/09/15\',\'B\'),(N\'大白\',\'2024/09/16\',\'B\')
,(N\'大白\',\'2024/09/17\',\'B\'),(N\'大白\',\'2024/09/18\',\'B\'),(N\'大白\',\'2024/09/19\',N\'休\'),(N\'大白\',\'2024/09/20\',N\'休\')
,(N\'大白\',\'2024/09/21\',\'A\'),(N\'大白\',\'2024/09/22\',\'A\'),(N\'大白\',\'2024/09/23\',\'A\'),(N\'大白\',\'2024/09/24\',\'A\')
,(N\'大白\',\'2024/09/25\',\'A\'),(N\'大白\',\'2024/09/26\',N\'休\'),(N\'大白\',\'2024/09/27\',N\'休\'),(N\'大白\',\'2024/09/28\',\'A\')
,(N\'大白\',\'2024/09/29\',\'A\'),(N\'大白\',\'2024/09/30\',\'A\')
,(N\'大白\',\'2024/10/1\',N\'特\'),(N\'大白\',\'2024/10/2\',N\'特\'),(N\'大白\',\'2024/10/3\',\'B\'),(N\'大白\',\'2024/10/4\',\'B\')
,(N\'大白\',\'2024/10/5\',N\'休\'),(N\'大白\',\'2024/10/6\',N\'休\'),(N\'大白\',\'2024/10/7\',\'B\'),(N\'大白\',\'2024/10/8\',\'B\')
,(N\'大白\',\'2024/10/9\',\'B\'),(N\'大白\',\'2024/10/10\',N\'休\'),(N\'大白\',\'2024/10/11\',\'B\'),(N\'大白\',\'2024/10/12\',N\'休\')
,(N\'大白\',\'2024/10/13\',N\'休\'),(N\'大白\',\'2024/10/14\',\'A\'),(N\'大白\',\'2024/10/15\',\'A\'),(N\'大白\',\'2024/10/16\',\'A\')
,(N\'大白\',\'2024/10/17\',\'A\'),(N\'大白\',\'2024/10/18\',\'A\'),(N\'大白\',\'2024/10/19\',N\'休\'),(N\'大白\',\'2024/10/20\',N\'休\')
,(N\'大白\',\'2024/10/21\',\'B\'),(N\'大白\',\'2024/10/22\',\'B\'),(N\'大白\',\'2024/10/23\',\'B\'),(N\'大白\',\'2024/10/24\',\'B\')
,(N\'大白\',\'2024/10/25\',\'B\'),(N\'大白\',\'2024/10/26\',N\'休\'),(N\'大白\',\'2024/10/27\',N\'休\'),(N\'大白\',\'2024/10/28\',\'B\')
,(N\'大白\',\'2024/10/29\',\'B\'),(N\'大白\',\'2024/10/30\',\'B\'),(N\'大白\',\'2024/10/31\',\'B\')
,(N\'小明\',\'2024/10/1\',\'A\'),(N\'小明\',\'2024/10/2\',\'A\'),(N\'小明\',\'2024/10/3\',\'A\'),(N\'小明\',\'2024/10/4\',\'A\')
,(N\'小明\',\'2024/10/5\',N\'休\'),(N\'小明\',\'2024/10/6\',N\'休\'),(N\'小明\',\'2024/10/7\',\'A\'),(N\'小明\',\'2024/10/8\',\'A\')
,(N\'小明\',\'2024/10/9\',\'A\'),(N\'小明\',\'2024/10/10\',N\'休\'),(N\'小明\',\'2024/10/11\',\'A\'),(N\'小明\',\'2024/10/12\',N\'休\')
,(N\'小明\',\'2024/10/13\',N\'休\'),(N\'小明\',\'2024/10/14\',\'B\'),(N\'小明\',\'2024/10/15\',\'B\'),(N\'小明\',\'2024/10/16\',\'B\')
,(N\'小明\',\'2024/10/17\',\'B\'),(N\'小明\',\'2024/10/18\',\'B\'),(N\'小明\',\'2024/10/19\',N\'休\'),(N\'小明\',\'2024/10/20\',N\'休\')
,(N\'小明\',\'2024/10/21\',\'A\'),(N\'小明\',\'2024/10/22\',\'A\'),(N\'小明\',\'2024/10/23\',\'A\'),(N\'小明\',\'2024/10/24\',\'A\')
,(N\'小明\',\'2024/10/25\',\'A\'),(N\'小明\',\'2024/10/26\',N\'休\'),(N\'小明\',\'2024/10/27\',N\'休\'),(N\'小明\',\'2024/10/28\',\'A\')
,(N\'小明\',\'2024/10/29\',\'A\'),(N\'小明\',\'2024/10/30\',\'A\'),(N\'小明\',\'2024/10/31\',\'A\');

-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS \'YM\',SetDate,Code
FROM da),  
-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN (\'A\',\'B\')) AS K
WHERE RN =1  
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下补班别  
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN (\'A\',\'B\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT Name,YM,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT Name,YM,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (
SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,YM,Code,No),
CTE06 AS (
SELECT Name,YM,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05)
/*
SELECT Name,YM,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE06
GROUP BY Name,YM  
*/
SELECT Name,YM
,stuff((
	select \',\' + PartStr
	from CTE06 b
	where a.Name = b.Name and a.YM = b.YM
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE06 a
GROUP BY Name,YM

Demo:SQL 2016

『修正版』 ``` CREATE TABLE da ( Name nvarchar(20), SetDate date, Code nvarchar(5)); INSERT INTO da (Name, SetDate,Code) VALUES (N'大白','2024/09/1','A'),(N'大白','2024/09/2','A'),(N'大白','2024/09/3','A'),(N'大白','2024/09/4','A') ,(N'大白','2024/09/5',N'休'),(N'大白','2024/09/6',N'休'),(N'大白','2024/09/7','A'),(N'大白','2024/09/8','A') ,(N'大白','2024/09/9','A'),(N'大白','2024/09/10',N'休'),(N'大白','2024/09/11','A'),(N'大白','2024/09/12',N'休') ,(N'大白','2024/09/13',N'休'),(N'大白','2024/09/14','B'),(N'大白','2024/09/15','B'),(N'大白','2024/09/16','B') ,(N'大白','2024/09/17','B'),(N'大白','2024/09/18','B'),(N'大白','2024/09/19',N'休'),(N'大白','2024/09/20',N'休') ,(N'大白','2024/09/21','A'),(N'大白','2024/09/22','A'),(N'大白','2024/09/23','A'),(N'大白','2024/09/24','A') ,(N'大白','2024/09/25','A'),(N'大白','2024/09/26',N'休'),(N'大白','2024/09/27',N'休'),(N'大白','2024/09/28','A') ,(N'大白','2024/09/29','A'),(N'大白','2024/09/30','A') ,(N'大白','2024/10/1',N'特'),(N'大白','2024/10/2',N'特'),(N'大白','2024/10/3','B'),(N'大白','2024/10/4','B') ,(N'大白','2024/10/5',N'休'),(N'大白','2024/10/6',N'休'),(N'大白','2024/10/7','B'),(N'大白','2024/10/8','B') ,(N'大白','2024/10/9','B'),(N'大白','2024/10/10',N'休'),(N'大白','2024/10/11','B'),(N'大白','2024/10/12',N'休') ,(N'大白','2024/10/13',N'休'),(N'大白','2024/10/14','A'),(N'大白','2024/10/15','A'),(N'大白','2024/10/16','A') ,(N'大白','2024/10/17','A'),(N'大白','2024/10/18','A'),(N'大白','2024/10/19',N'休'),(N'大白','2024/10/20',N'休') ,(N'大白','2024/10/21','B'),(N'大白','2024/10/22','B'),(N'大白','2024/10/23','B'),(N'大白','2024/10/24','B') ,(N'大白','2024/10/25','B'),(N'大白','2024/10/26',N'休'),(N'大白','2024/10/27',N'休'),(N'大白','2024/10/28','B') ,(N'大白','2024/10/29','B'),(N'大白','2024/10/30','B'),(N'大白','2024/10/31','B') ,(N'小明','2024/10/1','A'),(N'小明','2024/10/2','A'),(N'小明','2024/10/3','A'),(N'小明','2024/10/4','A') ,(N'小明','2024/10/5',N'休'),(N'小明','2024/10/6',N'休'),(N'小明','2024/10/7','A'),(N'小明','2024/10/8','A') ,(N'小明','2024/10/9','A'),(N'小明','2024/10/10',N'休'),(N'小明','2024/10/11','A'),(N'小明','2024/10/12',N'休') ,(N'小明','2024/10/13',N'休'),(N'小明','2024/10/14','B'),(N'小明','2024/10/15','B'),(N'小明','2024/10/16','B') ,(N'小明','2024/10/17','B'),(N'小明','2024/10/18','B'),(N'小明','2024/10/19',N'休'),(N'小明','2024/10/20',N'休') ,(N'小明','2024/10/21','A'),(N'小明','2024/10/22','A'),(N'小明','2024/10/23','A'),(N'小明','2024/10/24','A') ,(N'小明','2024/10/25','A'),(N'小明','2024/10/26',N'休'),(N'小明','2024/10/27',N'休'),(N'小明','2024/10/28','A') ,(N'小明','2024/10/29','A'),(N'小明','2024/10/30','A'),(N'小明','2024/10/31','A'); ``` ``` -- 分割年月 WITH CTE00 AS ( SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code FROM da), -- 每月 1 日'休'、'特', 找到第一个班别向上补 CTE01 AS ( SELECT CTE00.Name,CTE00.YM,CTE00.SetDate, CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code FROM CTE00 LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,* FROM CTE00 WHERE Code IN ('A','B')) AS K WHERE RN =1 ) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM), -- 向下补班别 CTE02 AS ( SELECT Name,SetDate,YM, CASE WHEN Code NOT IN ('A','B') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT Name,YM,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT Name,YM,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.YM = step_2.YM AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS ( SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,YM,Code,No), CTE06 AS ( SELECT Name,YM,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05) /* SELECT Name,YM,STRING_AGG(PartStr,',') WITHIN GROUP (ORDER BY No) AS AllStr FROM CTE06 GROUP BY Name,YM */ SELECT Name,YM ,stuff(( select ',' + PartStr from CTE06 b where a.Name = b.Name and a.YM = b.YM order by no for xml path('') ),1,1,'') as AllStr FROM CTE06 a GROUP BY Name,YM ``` [Demo:SQL 2016](https://dbfiddle.uk/-Q2U3dyj)

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 08:56:06

那句SQL要用在跨月列出的话
我知道怎样改~就加上跨月条件

Row_Number() OVER (PARTITION BY Name,format(SetDate,\'yyyy-MM\') ORDER BY SetDate) AS id

--在此join条件加上跨月
LEFT JOIN (
   略......
)  AS K on format(a.SetDate,\'yyyy-MM\') = format(k.SetDate,\'yyyy-MM\')

你的跨月写法也是满有趣的~

Left(SetDate,7)

然后~列出跑秒数过久的SQL好了~
昨天讲错了~是当月排班列出

跑21秒的SQL

declare @StartDate date = \'2024/10/1\'
,@DNO varchar(20) = \'xxx\'

declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate))

declare @Shift table(
	GroupName nvarchar(50)
	,PNo nvarchar(50)
	,PName nvarchar(50)
	,SetDate date
	,Code nvarchar(10)
)

insert into @Shift
略.....(当月排班)

;WITH
NewShift as (
	select a.GroupName
	,a.PNo
	,a.PName
	,a.SetDate
	,isNull((
		CASE WHEN a.SetDate > K1.SetDate THEN k1.Code ELSE (case when a.Code in(N\'特\',N\'休\') then null else a.Code end) END
	),(
		CASE WHEN a.SetDate < K2.SetDate THEN k2.Code ELSE a.Code END
	)) Code
	from @Shift a
	left join (
		select *
		from (
			SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate ) AS RN
			,*
			FROM @Shift b
			WHERE b.Code not in(N\'特\',N\'休\')
		) k
		where RN = 1
	) k1 on a.PNo = k1.PNo
	left join (
		select *
		from (
			SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate desc) AS RN
			,*
			FROM @Shift b
			WHERE b.Code not in(N\'特\',N\'休\')
		) k
		where RN = 1
	) k2 on a.PNo = k2.PNo
)
,CTE01 AS (
	select GroupName
	,PNo
	,PName
	,M.SetDate
	,M.Code
	,1 + SUM(M.Num) OVER (PARTITION BY M.PNo ORDER BY M.SetDate) AS No
	FROM (
		SELECT a.*,
			CASE WHEN Code = LAG(Code,1,Code) OVER (PARTITION BY PNo ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
		FROM NewShift a
	) M
)
,CTE02 AS (
	select GroupName
	,PNo
	,PName
	,Code
	,No
	,Min(SetDate) AS MinDate
	,Max(SetDate) AS MaxDate
	FROM CTE01
	GROUP BY GroupName
	,PNo
	,PName
	,Code
	,No
)
,CTE03 AS (
	select GroupName
	,PNo
	,PName
	,No
	,Code
	,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
	FROM CTE02
)

select GroupName
,PNo
,PName
,stuff((
	select \',\' + PartStr
	from CTE03 b
	where a.PNo = b.PNo
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE03 a
GROUP BY GroupName
,PNo
,PName

跑1秒的SQL

declare @StartDate date = \'2024/10/1\'
,@DNO varchar(20) = \'xxx\'

declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate))

declare @Shift table(
	GroupName nvarchar(50)
	,PNo nvarchar(50)
	,PName nvarchar(50)
	,SetDate date
	,Code nvarchar(10)
)

insert into @Shift
略.....(当月排班)

declare @NewShift table(
	GroupName nvarchar(50)
	,PNo nvarchar(50)
	,PName nvarchar(50)
	,SetDate date
	,Code nvarchar(10)
)
insert into @NewShift
select a.GroupName
,a.PNo
,a.PName
,a.SetDate
,isNull((
	CASE WHEN a.SetDate > K1.SetDate THEN k1.Code ELSE (case when a.Code in(N\'特\',N\'休\') then null else a.Code end) END
),(
	CASE WHEN a.SetDate < K2.SetDate THEN k2.Code ELSE a.Code END
)) Code
from @Shift a
left join (
	select *
	from (
		SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate ) AS RN
		,*
		FROM @Shift b
		WHERE b.Code not in(N\'特\',N\'休\')
	) k
	where RN = 1
) k1 on a.PNo = k1.PNo
left join (
	select *
	from (
		SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate desc) AS RN
		,*
		FROM @Shift b
		WHERE b.Code not in(N\'特\',N\'休\')
	) k
	where RN = 1
) k2 on a.PNo = k2.PNo

;WITH
CTE01 AS (
	select GroupName
	,PNo
	,PName
	,M.SetDate
	,M.Code
	,1 + SUM(M.Num) OVER (PARTITION BY M.PNo ORDER BY M.SetDate) AS No
	FROM (
		SELECT a.*,
			CASE WHEN Code = LAG(Code,1,Code) OVER (PARTITION BY PNo ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
		FROM @NewShift a
	) M
)
,CTE02 AS (
	select GroupName
	,PNo
	,PName
	,Code
	,No
	,Min(SetDate) AS MinDate
	,Max(SetDate) AS MaxDate
	FROM CTE01
	GROUP BY GroupName
	,PNo
	,PName
	,Code
	,No
)
,CTE03 AS (
	select GroupName
	,PNo
	,PName
	,No
	,Code
	,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
	FROM CTE02
)

select GroupName
,PNo
,PName
,stuff((
	select \',\' + PartStr
	from CTE03 b
	where a.PNo = b.PNo
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE03 a
GROUP BY GroupName
,PNo
,PName

你可以看看问题在哪里，我只是把 NewShift 表，移到 WITH 外面，就变快了@@...

那句SQL要用在跨月列出的话 我知道怎样改~就加上跨月条件 ``` Row_Number() OVER (PARTITION BY Name,format(SetDate,'yyyy-MM') ORDER BY SetDate) AS id --在此join条件加上跨月 LEFT JOIN ( 略...... ) AS K on format(a.SetDate,'yyyy-MM') = format(k.SetDate,'yyyy-MM') ``` 你的跨月写法也是满有趣的~ ``` Left(SetDate,7) ``` 然后~列出跑秒数过久的SQL好了~ 昨天讲错了~是当月排班列出 跑21秒的SQL ``` declare @StartDate date = '2024/10/1' ,@DNO varchar(20) = 'xxx' declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate)) declare @Shift table( GroupName nvarchar(50) ,PNo nvarchar(50) ,PName nvarchar(50) ,SetDate date ,Code nvarchar(10) ) insert into @Shift 略.....(当月排班) ;WITH NewShift as ( select a.GroupName ,a.PNo ,a.PName ,a.SetDate ,isNull(( CASE WHEN a.SetDate > K1.SetDate THEN k1.Code ELSE (case when a.Code in(N'特',N'休') then null else a.Code end) END ),( CASE WHEN a.SetDate < K2.SetDate THEN k2.Code ELSE a.Code END )) Code from @Shift a left join ( select * from ( SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate ) AS RN ,* FROM @Shift b WHERE b.Code not in(N'特',N'休') ) k where RN = 1 ) k1 on a.PNo = k1.PNo left join ( select * from ( SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate desc) AS RN ,* FROM @Shift b WHERE b.Code not in(N'特',N'休') ) k where RN = 1 ) k2 on a.PNo = k2.PNo ) ,CTE01 AS ( select GroupName ,PNo ,PName ,M.SetDate ,M.Code ,1 + SUM(M.Num) OVER (PARTITION BY M.PNo ORDER BY M.SetDate) AS No FROM ( SELECT a.*, CASE WHEN Code = LAG(Code,1,Code) OVER (PARTITION BY PNo ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM NewShift a ) M ) ,CTE02 AS ( select GroupName ,PNo ,PName ,Code ,No ,Min(SetDate) AS MinDate ,Max(SetDate) AS MaxDate FROM CTE01 GROUP BY GroupName ,PNo ,PName ,Code ,No ) ,CTE03 AS ( select GroupName ,PNo ,PName ,No ,Code ,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE02 ) select GroupName ,PNo ,PName ,stuff(( select ',' + PartStr from CTE03 b where a.PNo = b.PNo order by no for xml path('') ),1,1,'') as AllStr FROM CTE03 a GROUP BY GroupName ,PNo ,PName ``` ![https://ithelp.ithome.com.tw/upload/images/20241016/20061369mMFKjoQLtW.png](https://ithelp.ithome.com.tw/upload/images/20241016/20061369mMFKjoQLtW.png) 跑1秒的SQL ``` declare @StartDate date = '2024/10/1' ,@DNO varchar(20) = 'xxx' declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate)) declare @Shift table( GroupName nvarchar(50) ,PNo nvarchar(50) ,PName nvarchar(50) ,SetDate date ,Code nvarchar(10) ) insert into @Shift 略.....(当月排班) declare @NewShift table( GroupName nvarchar(50) ,PNo nvarchar(50) ,PName nvarchar(50) ,SetDate date ,Code nvarchar(10) ) insert into @NewShift select a.GroupName ,a.PNo ,a.PName ,a.SetDate ,isNull(( CASE WHEN a.SetDate > K1.SetDate THEN k1.Code ELSE (case when a.Code in(N'特',N'休') then null else a.Code end) END ),( CASE WHEN a.SetDate < K2.SetDate THEN k2.Code ELSE a.Code END )) Code from @Shift a left join ( select * from ( SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate ) AS RN ,* FROM @Shift b WHERE b.Code not in(N'特',N'休') ) k where RN = 1 ) k1 on a.PNo = k1.PNo left join ( select * from ( SELECT Row_Number() OVER (PARTITION BY PNO ORDER BY b.SetDate desc) AS RN ,* FROM @Shift b WHERE b.Code not in(N'特',N'休') ) k where RN = 1 ) k2 on a.PNo = k2.PNo ;WITH CTE01 AS ( select GroupName ,PNo ,PName ,M.SetDate ,M.Code ,1 + SUM(M.Num) OVER (PARTITION BY M.PNo ORDER BY M.SetDate) AS No FROM ( SELECT a.*, CASE WHEN Code = LAG(Code,1,Code) OVER (PARTITION BY PNo ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM @NewShift a ) M ) ,CTE02 AS ( select GroupName ,PNo ,PName ,Code ,No ,Min(SetDate) AS MinDate ,Max(SetDate) AS MaxDate FROM CTE01 GROUP BY GroupName ,PNo ,PName ,Code ,No ) ,CTE03 AS ( select GroupName ,PNo ,PName ,No ,Code ,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE02 ) select GroupName ,PNo ,PName ,stuff(( select ',' + PartStr from CTE03 b where a.PNo = b.PNo order by no for xml path('') ),1,1,'') as AllStr FROM CTE03 a GROUP BY GroupName ,PNo ,PName ``` ![https://ithelp.ithome.com.tw/upload/images/20241016/20061369qHPUxauRbg.png](https://ithelp.ithome.com.tw/upload/images/20241016/20061369qHPUxauRbg.png) 你可以看看问题在哪里，我只是把 NewShift 表，移到 WITH 外面，就变快了@@...

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 09:25:15

,CTE01 AS (

其实你只要测这一行以上的速度就可以了，以下的程式码是一样的
若是差距很大的话，我只能猜测是 SQL Sever 配置记忆体的方式不一样，
才导致计算速度的落差

``` ,CTE01 AS ( ``` 其实你只要测这一行以上的速度就可以了，以下的程式码是一样的 若是差距很大的话，我只能猜测是 SQL Sever 配置记忆体的方式不一样， 才导致计算速度的落差

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 09:43:17

结果我~SQL一段一段的慢慢拆开测试是哪边有问题...
结果是出在最后面@@...

;WITH
NewShift as (
略...
)
,CTE01 AS (
略...
)
,CTE02 AS (
略...
)
,CTE03 AS (
略...
)

--27位员工 * 31天 = 837笔 跑0.5秒
select GroupName
,PNo
,PName
FROM CTE03 a
GROUP BY GroupName
,PNo
,PName

--27位员工 * 31天 = 837笔 跑21秒
select GroupName
,PNo
,PName
,stuff((
	select \',\' + PartStr
	from CTE03 b
	where a.PNo = b.PNo
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE03 a
GROUP BY GroupName
,PNo
,PName

结果我~SQL一段一段的慢慢拆开测试是哪边有问题... 结果是出在最后面@@... ``` ;WITH NewShift as ( 略... ) ,CTE01 AS ( 略... ) ,CTE02 AS ( 略... ) ,CTE03 AS ( 略... ) --27位员工 * 31天 = 837笔跑0.5秒 select GroupName ,PNo ,PName FROM CTE03 a GROUP BY GroupName ,PNo ,PName --27位员工 * 31天 = 837笔跑21秒 select GroupName ,PNo ,PName ,stuff(( select ',' + PartStr from CTE03 b where a.PNo = b.PNo order by no for xml path('') ),1,1,'') as AllStr FROM CTE03 a GROUP BY GroupName ,PNo ,PName ```

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 09:47:42

把那段子查询注解后~速度就正常了0.0...

select GroupName
,PNo
,PName
--,stuff((
--	select \',\' + PartStr
--	from CTE03 b
--	where a.PNo = b.PNo
--	order by no
--	for xml path(\'\')
--),1,1,\'\') as AllStr
FROM CTE03 a
GROUP BY GroupName
,PNo
,PName

把那段子查询注解后~速度就正常了0.0... ``` select GroupName ,PNo ,PName --,stuff(( -- select ',' + PartStr -- from CTE03 b -- where a.PNo = b.PNo -- order by no -- for xml path('') --),1,1,'') as AllStr FROM CTE03 a GROUP BY GroupName ,PNo ,PName ```

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 10:49:54

看起来~只有升级2017版后~
这个函数才能解决效率变差的问题吧@@

STRING_AGG()

看起来~只有升级2017版后~ 这个函数才能解决效率变差的问题吧@@ ``` STRING_AGG() ```

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 11:34:39

你有用我的修正版跟你的最后一版
去比对资料正确性跟速度吗？

因为我一直觉得 WITH 这一段 SQL 的逻辑怪怪的
;WITH
NewShift as

你有用我的修正版跟你的最后一版 去比对资料正确性跟速度吗？ 因为我一直觉得 WITH 这一段 SQL 的逻辑怪怪的 ;WITH NewShift as

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 13:55:49

我直接套你的SQL
2笔很快~
可是笔数一多~好像就跑不完...@@

declare @StartDate date = \'2024/8/1\'
,@DNO varchar(20) = \'xxxx\'

declare @da table(
Name nvarchar(20),
SetDate date,
Code nvarchar(5));
declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate))

INSERT INTO @da (Name, SetDate,Code)
略....(实际当月班别)

SELECT *
FROM @da;

-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
WITH 
CTE01 AS (
	SELECT a.Name,a.SetDate,
	CASE WHEN a.SetDate < K.SetDate THEN K.Code ELSE a.Code END AS Code
	FROM @da a
	LEFT JOIN (
		SELECT * FROM (
		SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS RN,*
		FROM @da
		WHERE Code IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\')) AS K
		WHERE RN =1  
	) AS K ON K.Name=a.Name
)

-- 向下补班别  
,CTE02 AS (
	SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id,
	Name,SetDate,
	CASE WHEN Code NOT IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\') THEN NULL ELSE Code END AS CodeX
	FROM CTE01
)
,CTE03 AS (
	SELECT step_l.Name,step_l.SetDate,step_2.CodeX as Code
	FROM ( 
		SELECT id,Name,SetDate,CodeX, 
		SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name ORDER BY SetDate) as rank_id 
		FROM CTE02
	) as step_l 
	LEFT JOIN ( 
		SELECT id,Name,SetDate,CodeX, 
		Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS join_id 
		FROM CTE02 WHERE CodeX IS NOT NULL
	) as step_2 
	ON step_l.Name = step_2.Name 
	AND step_l.rank_id = step_2.join_id
)

-- Code 内无\'休\'、\'特\'
,CTE04 AS (
	SELECT M.Name,M.SetDate,M.Code,1 + SUM(M.Num) OVER (PARTITION BY M.Name ORDER BY M.SetDate) AS No
	FROM (
		SELECT CTE03.*,
		CASE WHEN Code = LAG(Code,1,Code) OVER (
		PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
		FROM CTE03
	) M
)
,CTE05 AS (
	SELECT Name,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
	FROM CTE04
	GROUP BY Name,Code,No
)
,CTE06 AS (
	SELECT Name,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
	FROM CTE05
)

SELECT Name
,stuff((
	select \',\' + PartStr
	from CTE06 b
	where a.Name = b.Name
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
--,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE06 a
GROUP BY Name

跑了13分钟...我直接按中止查询@@...

我直接套你的SQL 2笔很快~ 可是笔数一多~好像就跑不完...@@ ``` declare @StartDate date = '2024/8/1' ,@DNO varchar(20) = 'xxxx' declare @da table( Name nvarchar(20), SetDate date, Code nvarchar(5)); declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate)) INSERT INTO @da (Name, SetDate,Code) 略....(实际当月班别) SELECT * FROM @da; -- 每月 1 日'休'、'特', 找到第一个班别向上补 WITH CTE01 AS ( SELECT a.Name,a.SetDate, CASE WHEN a.SetDate < K.SetDate THEN K.Code ELSE a.Code END AS Code FROM @da a LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS RN,* FROM @da WHERE Code IN ('2A','2C','3A','3B','3C')) AS K WHERE RN =1 ) AS K ON K.Name=a.Name ) -- 向下补班别 ,CTE02 AS ( SELECT Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS id, Name,SetDate, CASE WHEN Code NOT IN ('2A','2C','3A','3B','3C') THEN NULL ELSE Code END AS CodeX FROM CTE01 ) ,CTE03 AS ( SELECT step_l.Name,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT id,Name,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name ORDER BY SetDate) as rank_id FROM CTE02 ) as step_l LEFT JOIN ( SELECT id,Name,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL ) as step_2 ON step_l.Name = step_2.Name AND step_l.rank_id = step_2.join_id ) -- Code 内无'休'、'特' ,CTE04 AS ( SELECT M.Name,M.SetDate,M.Code,1 + SUM(M.Num) OVER (PARTITION BY M.Name ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03 ) M ) ,CTE05 AS ( SELECT Name,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,Code,No ) ,CTE06 AS ( SELECT Name,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05 ) SELECT Name ,stuff(( select ',' + PartStr from CTE06 b where a.Name = b.Name order by no for xml path('') ),1,1,'') as AllStr --,STRING_AGG(PartStr,',') WITHIN GROUP (ORDER BY No) AS AllStr FROM CTE06 a GROUP BY Name ``` ![https://ithelp.ithome.com.tw/upload/images/20241016/20061369aPH7ClOGbF.png](https://ithelp.ithome.com.tw/upload/images/20241016/20061369aPH7ClOGbF.png) 跑了13分钟...我直接按中止查询@@...

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 14:00:27

这内容不是我最后一版的修正版（2024-10-16 08:39:09）
修正版内有 YM 这个栏位

这内容不是我最后一版的修正版（2024-10-16 08:39:09） 修正版内有 YM 这个栏位

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 14:05:56

后来我改成实际资料表储存~速度就查到了@@a

好像是因为 declare 就宣告会影响WITH效能存取..

我在试试（2024-10-16 08:39:09）若用 declare 是不是可以@@...

后来我改成实际资料表储存~速度就查到了@@a ![https://ithelp.ithome.com.tw/upload/images/20241016/20061369oudbemfAIR.png](https://ithelp.ithome.com.tw/upload/images/20241016/20061369oudbemfAIR.png) 好像是因为 declare 就宣告会影响WITH效能存取.. 我在试试（2024-10-16 08:39:09）若用 declare 是不是可以@@...

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 14:18:39

果然是 declare 宣告表格问题@@..
但是处理查询又不能真的存表格..(因为那是很多join表格来的)

declare @StartDate date = \'2024/8/1\'
,@DNO varchar(20) = \'xxxx\'

declare @da table(
Name nvarchar(20),
SetDate date,
Code nvarchar(5));
declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate))

INSERT INTO @da (Name, SetDate,Code)
略............(实际班别资料)

SELECT *
FROM @da;

-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS \'YM\',SetDate,Code
FROM @da),  
-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\')) AS K
WHERE RN =1  
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下补班别  
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT Name,YM,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT Name,YM,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (
SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,YM,Code,No),
CTE06 AS (
SELECT Name,YM,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05)
/*
SELECT Name,YM,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE06
GROUP BY Name,YM  
*/
SELECT Name,YM
,stuff((
	select \',\' + PartStr
	from CTE06 b
	where a.Name = b.Name and a.YM = b.YM
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE06 a
GROUP BY Name,YM

跑太久~按中止

果然是 declare 宣告表格问题@@.. 但是处理查询又不能真的存表格..(因为那是很多join表格来的) ``` declare @StartDate date = '2024/8/1' ,@DNO varchar(20) = 'xxxx' declare @da table( Name nvarchar(20), SetDate date, Code nvarchar(5)); declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate)) INSERT INTO @da (Name, SetDate,Code) 略............(实际班别资料) SELECT * FROM @da; -- 分割年月 WITH CTE00 AS ( SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code FROM @da), -- 每月 1 日'休'、'特', 找到第一个班别向上补 CTE01 AS ( SELECT CTE00.Name,CTE00.YM,CTE00.SetDate, CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code FROM CTE00 LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,* FROM CTE00 WHERE Code IN ('2A','2C','3A','3B','3C')) AS K WHERE RN =1 ) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM), -- 向下补班别 CTE02 AS ( SELECT Name,SetDate,YM, CASE WHEN Code NOT IN ('2A','2C','3A','3B','3C') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT Name,YM,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT Name,YM,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.YM = step_2.YM AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS ( SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,YM,Code,No), CTE06 AS ( SELECT Name,YM,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05) /* SELECT Name,YM,STRING_AGG(PartStr,',') WITHIN GROUP (ORDER BY No) AS AllStr FROM CTE06 GROUP BY Name,YM */ SELECT Name,YM ,stuff(( select ',' + PartStr from CTE06 b where a.Name = b.Name and a.YM = b.YM order by no for xml path('') ),1,1,'') as AllStr FROM CTE06 a GROUP BY Name,YM ``` ![https://ithelp.ithome.com.tw/upload/images/20241016/20061369JBV8P65NtK.png](https://ithelp.ithome.com.tw/upload/images/20241016/20061369JBV8P65NtK.png) 跑太久~按中止

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 14:21:59

我的解法不需要计算当月最后一天

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 14:25:08

恩~我在想想~如果存实际表格的话~
他们这个月有改班表才刷新~实际表格资料~
其他报表查询就直接抓已纪录的实际表格资料

恩~我在想想~如果存实际表格的话~ 他们这个月有改班表才刷新~实际表格资料~ 其他报表查询就直接抓已纪录的实际表格资料

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 14:51:29

刚刚~想到如果把 declare 改成宣告暂存表格 #da
查询速度就ok了@@~

declare @StartDate date = \'2024/8/1\'
,@DNO varchar(20) = \'xxx\'

create table #da(
Name nvarchar(20),
SetDate date,
Code nvarchar(5));
declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate))

INSERT INTO #da (Name, SetDate,Code)
略.......

SELECT *
FROM #da;

-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS \'YM\',SetDate,Code
FROM #da),  
-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\')) AS K
WHERE RN =1  
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下补班别  
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN (\'2A\',\'2C\',\'3A\',\'3B\',\'3C\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT Name,YM,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT Name,YM,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (
SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,YM,Code,No),
CTE06 AS (
SELECT Name,YM,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05)
/*
SELECT Name,YM,STRING_AGG(PartStr,\',\') WITHIN GROUP (ORDER BY No) AS AllStr
FROM CTE06
GROUP BY Name,YM  
*/
SELECT Name,YM
,stuff((
	select \',\' + PartStr
	from CTE06 b
	where a.Name = b.Name and a.YM = b.YM
	order by no
	for xml path(\'\')
),1,1,\'\') as AllStr
FROM CTE06 a
GROUP BY Name,YM

DROP TABLE #da

刚刚~想到如果把 declare 改成宣告暂存表格 #da 查询速度就ok了@@~ ``` declare @StartDate date = '2024/8/1' ,@DNO varchar(20) = 'xxx' create table #da( Name nvarchar(20), SetDate date, Code nvarchar(5)); declare @EndDate date = dateadd(day,-1,dateadd(month,1,@StartDate)) INSERT INTO #da (Name, SetDate,Code) 略....... SELECT * FROM #da; -- 分割年月 WITH CTE00 AS ( SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code FROM #da), -- 每月 1 日'休'、'特', 找到第一个班别向上补 CTE01 AS ( SELECT CTE00.Name,CTE00.YM,CTE00.SetDate, CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code FROM CTE00 LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,* FROM CTE00 WHERE Code IN ('2A','2C','3A','3B','3C')) AS K WHERE RN =1 ) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM), -- 向下补班别 CTE02 AS ( SELECT Name,SetDate,YM, CASE WHEN Code NOT IN ('2A','2C','3A','3B','3C') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT Name,YM,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT Name,YM,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.YM = step_2.YM AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS ( SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,YM,Code,No), CTE06 AS ( SELECT Name,YM,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05) /* SELECT Name,YM,STRING_AGG(PartStr,',') WITHIN GROUP (ORDER BY No) AS AllStr FROM CTE06 GROUP BY Name,YM */ SELECT Name,YM ,stuff(( select ',' + PartStr from CTE06 b where a.Name = b.Name and a.YM = b.YM order by no for xml path('') ),1,1,'') as AllStr FROM CTE06 a GROUP BY Name,YM DROP TABLE #da ```

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 14:57:34

有用我的修正版跟你的最后一版
去比对资料正确性跟速度吗？

有用我的修正版跟你的最后一版 去比对资料正确性跟速度吗？

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 14:58:26

这三种表格用法~在微软解释记忆体部分好像我看不出差异..

create table da
create table #da
declare @da table

https://learn.microsoft.com/zh-tw/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver16

这三种表格用法~在微软解释记忆体部分好像我看不出差异.. create table da create table #da declare @da table https://learn.microsoft.com/zh-tw/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver16

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 15:03:24

速度你比较快~
资料正确性你的对~
从到职日开始上班的日期也正确~

速度你比较快~ 资料正确性你的对~ 从到职日开始上班的日期也正确~ ![https://ithelp.ithome.com.tw/upload/images/20241016/200613699uzoLZMDRX.png](https://ithelp.ithome.com.tw/upload/images/20241016/200613699uzoLZMDRX.png)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-16 15:19:16

恭喜，打完收工

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-16 15:20:26

贴到正式空间的资料库查询也没问题~谢谢你!

贴到正式空间的资料库查询也没问题~谢谢你!![/images/emoticon/emoticon12.gif](/images/emoticon/emoticon12.gif)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-17 21:20:14

案例一：递迴查询取代 xml path 组合字串

-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS \'YM\',SetDate,Code
FROM da),  
-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN (\'A\',\'B\')) AS K
WHERE RN =1  
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下补班别  
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN (\'A\',\'B\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT Name,YM,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT Name,YM,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (
SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,YM,Code,No),
CTE06 AS (
SELECT Name,YM,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05),
-- 递迴查询组合字串
CTE07 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY No) AS PX
FROM CTE06),  
CTE08 AS (
SELECT Name,YM,No,PX,
CAST(PartStr as nvarchar(200)) as AllStr,1 as ID
FROM CTE07
UNION ALL
SELECT NS.Name,NS.YM,NS.No,NS.PX,
CAST(CTE08.AllStr +\',\'+ NS.PartStr as nvarchar(200)) as AllStr,
ID + 1 AS ID
FROM CTE07 AS NS
INNER JOIN CTE08 ON NS.Name = CTE08.Name
AND NS.YM = CTE08.YM
AND NS.PX - 1 = CTE08.PX
)
--
SELECT A.Name,A.YM,A.AllStr
FROM CTE08 AS A
INNER JOIN (  
SELECT Name,YM,MAX(ID) AS MAXID
FROM CTE08
GROUP BY Name,YM) AS B ON B.Name = A.Name 
AND B.YM = A.YM
AND B.MAXID = A.ID
ORDER BY Name,YM

Demo:SQL 2016

递迴查询组合字串:测试资料

案例二：递迴查询取代 xml path 组合字串

-- 分割年月
WITH CTE00 AS (
SELECT Name,LEFT(SetDate,7) AS \'YM\',SetDate,Code
FROM da),  
-- 每月 1 日\'休\'、\'特\', 找到第一个班别向上补
CTE01 AS (
SELECT CTE00.Name,CTE00.YM,CTE00.SetDate,
CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code
FROM CTE00
LEFT JOIN (
SELECT * FROM (
SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,*
FROM CTE00
WHERE Code IN (\'A\',\'B\')) AS K
WHERE RN =1  
) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM),
-- 向下补班别  
CTE02 AS (
SELECT Name,SetDate,YM,
CASE WHEN Code NOT IN (\'A\',\'B\') THEN NULL ELSE Code END AS CodeX
FROM CTE01),
--
CTE03 AS (
SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code
FROM ( 
SELECT Name,YM,SetDate,CodeX, 
SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id 
FROM CTE02) as step_l 
LEFT JOIN ( 
SELECT Name,YM,SetDate,CodeX, 
Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id 
FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 
ON step_l.Name = step_2.Name 
AND step_l.YM = step_2.YM
AND step_l.rank_id = step_2.join_id),
-- Code 内无\'休\'、\'特\'
CTE04 AS (
SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER (
PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No
FROM (
SELECT CTE03.*,
CASE WHEN Code = LAG(Code,1,Code) OVER (
PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num
FROM CTE03) M),  
CTE05 AS (
SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate
FROM CTE04
GROUP BY Name,YM,Code,No),
CTE06 AS (
SELECT Name,YM,No,Code,Code+\':\'+Right(MinDate,2)+\'~\'+Right(MaxDate,2) AS PartStr
FROM CTE05),
-- 递迴查询组合字串
CTE07 AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY No) AS PX
FROM CTE06),  
CTE08 AS (
SELECT Name,YM,No,PX,
CAST(PartStr as nvarchar(200)) as AllStr,1 as ID
FROM CTE07
UNION ALL
SELECT NS.Name,NS.YM,NS.No,NS.PX,
CAST(CTE08.AllStr +\',\'+ NS.PartStr as nvarchar(200)) as AllStr,
ID + 1 AS ID
FROM CTE07 AS NS
INNER JOIN CTE08 ON NS.Name = CTE08.Name
AND NS.YM = CTE08.YM
AND NS.PX - 1 = CTE08.PX
)
--
SELECT Name,YM,AllStr
FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY ID Desc) AS Pos
FROM CTE08
) T
WHERE Pos = 1 
ORDER BY Name,YM

Demo:SQL 2016

案例一：递迴查询取代 xml path 组合字串 ``` -- 分割年月 WITH CTE00 AS ( SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code FROM da), -- 每月 1 日'休'、'特', 找到第一个班别向上补 CTE01 AS ( SELECT CTE00.Name,CTE00.YM,CTE00.SetDate, CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code FROM CTE00 LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,* FROM CTE00 WHERE Code IN ('A','B')) AS K WHERE RN =1 ) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM), -- 向下补班别 CTE02 AS ( SELECT Name,SetDate,YM, CASE WHEN Code NOT IN ('A','B') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT Name,YM,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT Name,YM,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.YM = step_2.YM AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS ( SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,YM,Code,No), CTE06 AS ( SELECT Name,YM,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05), -- 递迴查询组合字串 CTE07 AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY No) AS PX FROM CTE06), CTE08 AS ( SELECT Name,YM,No,PX, CAST(PartStr as nvarchar(200)) as AllStr,1 as ID FROM CTE07 UNION ALL SELECT NS.Name,NS.YM,NS.No,NS.PX, CAST(CTE08.AllStr +','+ NS.PartStr as nvarchar(200)) as AllStr, ID + 1 AS ID FROM CTE07 AS NS INNER JOIN CTE08 ON NS.Name = CTE08.Name AND NS.YM = CTE08.YM AND NS.PX - 1 = CTE08.PX ) -- SELECT A.Name,A.YM,A.AllStr FROM CTE08 AS A INNER JOIN ( SELECT Name,YM,MAX(ID) AS MAXID FROM CTE08 GROUP BY Name,YM) AS B ON B.Name = A.Name AND B.YM = A.YM AND B.MAXID = A.ID ORDER BY Name,YM ``` [Demo:SQL 2016](https://dbfiddle.uk/NXwOGUBa) [ 递迴查询组合字串:测试资料](https://dbfiddle.uk/QvG3dXxp) 案例二：递迴查询取代 xml path 组合字串 ``` -- 分割年月 WITH CTE00 AS ( SELECT Name,LEFT(SetDate,7) AS 'YM',SetDate,Code FROM da), -- 每月 1 日'休'、'特', 找到第一个班别向上补 CTE01 AS ( SELECT CTE00.Name,CTE00.YM,CTE00.SetDate, CASE WHEN CTE00.SetDate < K.SetDate THEN K.Code ELSE CTE00.Code END AS Code FROM CTE00 LEFT JOIN ( SELECT * FROM ( SELECT Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS RN,* FROM CTE00 WHERE Code IN ('A','B')) AS K WHERE RN =1 ) AS K ON K.Name=CTE00.Name AND K.YM=CTE00.YM), -- 向下补班别 CTE02 AS ( SELECT Name,SetDate,YM, CASE WHEN Code NOT IN ('A','B') THEN NULL ELSE Code END AS CodeX FROM CTE01), -- CTE03 AS ( SELECT step_l.Name,step_l.YM,step_l.SetDate,step_2.CodeX as Code FROM ( SELECT Name,YM,SetDate,CodeX, SUM(CASE WHEN CodeX IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY Name,YM ORDER BY SetDate) as rank_id FROM CTE02) as step_l LEFT JOIN ( SELECT Name,YM,SetDate,CodeX, Row_Number() OVER (PARTITION BY Name,YM ORDER BY SetDate) AS join_id FROM CTE02 WHERE CodeX IS NOT NULL) as step_2 ON step_l.Name = step_2.Name AND step_l.YM = step_2.YM AND step_l.rank_id = step_2.join_id), -- Code 内无'休'、'特' CTE04 AS ( SELECT M.Name,M.YM,M.SetDate,M.Code,1 + SUM(M.Num) OVER ( PARTITION BY M.Name,M.YM ORDER BY M.SetDate) AS No FROM ( SELECT CTE03.*, CASE WHEN Code = LAG(Code,1,Code) OVER ( PARTITION BY Name,YM ORDER BY SetDate) THEN 0 ELSE 1 END AS Num FROM CTE03) M), CTE05 AS ( SELECT Name,YM,Code,No,Min(SetDate) AS MinDate,Max(SetDate) AS MaxDate FROM CTE04 GROUP BY Name,YM,Code,No), CTE06 AS ( SELECT Name,YM,No,Code,Code+':'+Right(MinDate,2)+'~'+Right(MaxDate,2) AS PartStr FROM CTE05), -- 递迴查询组合字串 CTE07 AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY No) AS PX FROM CTE06), CTE08 AS ( SELECT Name,YM,No,PX, CAST(PartStr as nvarchar(200)) as AllStr,1 as ID FROM CTE07 UNION ALL SELECT NS.Name,NS.YM,NS.No,NS.PX, CAST(CTE08.AllStr +','+ NS.PartStr as nvarchar(200)) as AllStr, ID + 1 AS ID FROM CTE07 AS NS INNER JOIN CTE08 ON NS.Name = CTE08.Name AND NS.YM = CTE08.YM AND NS.PX - 1 = CTE08.PX ) -- SELECT Name,YM,AllStr FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Name,YM ORDER BY ID Desc) AS Pos FROM CTE08 ) T WHERE Pos = 1 ORDER BY Name,YM ``` [Demo:SQL 2016](https://dbfiddle.uk/qLkyDu19)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-17 21:30:11

可否帮忙用大量数据测一下最后一版（使用 xml path 组合字串）
与案例一、案例二递迴查询组合字串时间相差多少，谢谢。

可否帮忙用大量数据测一下最后一版（使用 xml path 组合字串） 与案例一、案例二递迴查询组合字串时间相差多少，谢谢。

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-18 08:35:03

两个都查询过久取消~

单纯2笔的资料~马上出来~

两个都查询过久取消~ ![https://ithelp.ithome.com.tw/upload/images/20241018/20061369bRVHWR0n4w.png](https://ithelp.ithome.com.tw/upload/images/20241018/20061369bRVHWR0n4w.png) ![https://ithelp.ithome.com.tw/upload/images/20241018/20061369HBeyGHaKeu.png](https://ithelp.ithome.com.tw/upload/images/20241018/20061369HBeyGHaKeu.png) 单纯2笔的资料~马上出来~ ![https://ithelp.ithome.com.tw/upload/images/20241018/20061369JPyZynLHha.png](https://ithelp.ithome.com.tw/upload/images/20241018/20061369JPyZynLHha.png)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-18 10:44:30

为取得时间相差多少，我在 Win 11 上的 SQL 2019 建立 1380 笔资料
1.STRING_AGG : 0.084 秒
2.xml path : 2.435 秒
3.递迴查询组合字串用 ROW_NUMBER 取得指定笔资料 : 14.874 秒
4.递迴查询组合字串 INNER JOIN + MAX 取得指定笔资料 : 30.976 秒

结论 :
1.指令优先选择 : STRING_AGG > xml path > 递迴查询组合字串
2.取得指定笔资料 : ROW_NUMBER > INNER JOIN + MAX

为取得时间相差多少，我在 Win 11 上的 SQL 2019 建立 1380 笔资料 1.STRING_AGG : 0.084 秒 2.xml path : 2.435 秒 3.递迴查询组合字串用 ROW_NUMBER 取得指定笔资料 : 14.874 秒 4.递迴查询组合字串 INNER JOIN + MAX 取得指定笔资料 : 30.976 秒 结论 : 1.指令优先选择 : STRING_AGG > xml path > 递迴查询组合字串 2.取得指定笔资料 : ROW_NUMBER > INNER JOIN + MAX

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-18 11:10:43

果然微软推出的新函数 STRING_AGG ，优化效能很多了~

若我把整个历史资料4万多笔~的确都跑不完😂😂😂

果然微软推出的新函数 STRING_AGG ，优化效能很多了~ 若我把整个历史资料4万多笔~的确都跑不完😂😂😂 ![https://ithelp.ithome.com.tw/upload/images/20241018/20061369xglQwvtdXS.png](https://ithelp.ithome.com.tw/upload/images/20241018/20061369xglQwvtdXS.png)

修改

rogeryao

iT邦超人 7 级 ‧
2024-10-18 11:36:45

[SQL Server]STRING_AGG 在2017版以前的替代方案(SQLCLR)
或许你可以用这个方法在 2016 建立自己的 STRING_AGG

[[SQL Server]STRING_AGG 在2017版以前的替代方案(SQLCLR)](https://dotblogs.com.tw/stanley14/2018/05/26/TSQL_STRING_AGG_INSQL2016) 或许你可以用这个方法在 2016 建立自己的 STRING_AGG

修改

纯真的人

iT邦大师 1 级 ‧
2024-10-18 11:58:57

恩~不过我没有主机权限~这个SQLCLR就放弃吧

我的工作是将已开发的程式码，转交另一个工程师(机房)验证上线

恩~不过我没有主机权限~这个SQLCLR就放弃吧 我的工作是将已开发的程式码，转交另一个工程师(机房)验证上线

修改

玩玩SQL~查询当月排班各区间的班别~SQL改善完成!

3 个回答

相关文章