ALTER FUNCTION [dbo].[fnSplit]
(
@string VARCHAR(max),
@split VARCHAR(2)
)
RETURNS @t TABLE(col VARCHAR(MAX))
AS
BEGIN
WHILE (CHARINDEX(@split, @string) <> 0)
BEGIN
INSERT @t
(
col
)
VALUES
(
SUBSTRING(@string, 1, CHARINDEX(@split, @string) -1)
)
SET @string = STUFF(@string, 1, CHARINDEX(@split, @string), '')
END
IF (@string <> '')
BEGIN
INSERT @t
(
col
)
VALUES
(
@string
)
END
RETURN
END
调用
declare @AccidentType varchar(2000)
set @AccidentType='18,17,16,20'
----------------按照月份统计-------------
select * from dbo.fnSplit(@AccidentType,',')c
select
convert(varchar(7),AccidentTime,120)month,
count(1)count
from dbo.AccidentReporting a
where
exists(select 1 from dbo.fnSplit(@AccidentType,',')c where a.AccidentType like '%'+c.col+'%')
group by convert(varchar(7),AccidentTime,120)
截图