SQL Server in 12,13,15,18 转换成 in 中使用的格式

1.创建函数

create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end

 

---使用方式
select col from [dbo].[f_split](mm.Meeting_Department,',')

 

----示例子
select *,CONVERT(varchar(100), Meeting_Time, 20) AS MeetingTime from (select row_number()
over(order by CreateDate desc) AS rownum,* ,
(stuff((select ',' + department_name from DepartmentRight depart
where depart.Department_ID in (select col from [dbo].[f_split](mm.Meeting_Department,',') )
for xml path('')),1,1,'')) as cc
from Meeting mm
left join Employee ee
on ee.Employee_ID = mm.CreateUser
) as t where rownum between 1 and 200