拼接字符串： select stuff((select ‘,’+alarmtype_name from tb_zngk_znfx_alarmtype where alarmtype_code in (‘10’,‘11’) for xml path(’’)),1,1,’’)
切割字符串： select value as code from fnSplitStr((select a.arithmetic_type from tb_zngk_znfx_camera_info a where a.camera_code = ‘3707020001’),’,’); fnSplitStr函数： CREATE FUNCTION [dbo].[fnSplitStr] ( @sText NVARCHAR(Max), @sDelim CHAR(1) )
RETURNS @retArray TABLE ( value VARCHAR(100) ) AS BEGIN DECLARE @posStart BIGINT, @posNext BIGINT, @valLen BIGINT, @sValue NVARCHAR(100);
IF @sDelim IS NULL BEGIN IF LEN(@sText)>100 SET @sText = SUBSTRING(@sText, 1, 100)
INSERT @retArray (value) VALUES (@sText); END ELSE BEGIN SET @posStart = 1;
WHILE @posStart <= LEN(@sText) BEGIN SET @posNext = CHARINDEX(@sDelim, @sText, @posStart);
IF @posNext <= 0 SET @valLen = LEN(@sText) - @posStart + 1; ELSE SET @valLen = @posNext - @posStart;
SET @sValue = SUBSTRING(@sText, @posStart, @valLen); SET @posStart = @posStart + @valLen + 1;
IF LEN(@sValue) > 0 BEGIN IF LEN(@sValue)>100 SET @sValue = SUBSTRING(@sValue, 1, 100)
INSERT @retArray (value) VALUES (@sValue);END END END RETURN END
最终拼接查询： SELECT t1.camera_code, t1.camera_name, t2.area_code as areaCode, t2.area_name as areaName, (select stuff((select ‘,’+alarmtype_name from tb_zngk_znfx_alarmtype where alarmtype_code in ( select value as code from fnSplitStr((select a.arithmetic_type from tb_zngk_znfx_camera_info a where a.camera_code = t1.camera_code),’,’) ) for xml path(’’)),1,1,’’)) as arithmeticTypeNames FROM tb_zngk_znfx_camera_info t1
