今天记录一个SQL中的字符串操作,首先来说一个与之相关的业务场景。
有两张表, 一张是教师表,一张是成绩表,成绩表中有一个审卷老师ID的字段(varchar型,id用了‘;’分割,审卷老师个数>=1), 现在查成绩表要求带出所有审卷老师的名字。 在我现在的认知里有两种解决方法:
1.自定义一个类似split方法的sql函数,
(注:这段代码转自一位网友)
1 create FUNCTION [dbo].[F_Split] 2 ( 3 @SplitString nvarchar(max), --源字符串 4 @Separator nvarchar(10)=' ' --分隔符号,默认为空格 5 ) 6 RETURNS @SplitStringsTable TABLE --输出的数据表 7 ( 8 [id] int identity(1,1), 9 [value] nvarchar(max) 10 ) 11 AS 12 BEGIN 13 DECLARE @CurrentIndex int; 14 DECLARE @NextIndex int; 15 DECLARE @ReturnText nvarchar(max); 16 17 SELECT @CurrentIndex=1; 18 WHILE(@CurrentIndex<=len(@SplitString)) 19 BEGIN 20 SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); 21 IF(@NextIndex=0 OR @NextIndex IS NULL) 22 SELECT @NextIndex=len(@SplitString)+1; 23 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); 24 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); 25 SELECT @CurrentIndex=@NextIndex+1; 26 END 27 RETURN; 28 END
然后成绩表和教师表关联查询 , 返回的教师名称字段用listagg进行分组拼接。
2.运用子查询,虽然子查询效率不高,但是在一般数据量较小的业务中使用也是无大碍的,使用模糊查询+listagg函数。
select (select listagg(o.教师名称, ';') within group(order by o.教师名称)
from 教师表 o
where p.教师IDs like concat(concat('%', o.教师ID), '%')) as 教师名称s
from 成绩表 p