27,579
社区成员
发帖
与我相关
我的任务
分享
艾滋病;
艾滋病;丙肝(含病毒携带者);
艾滋病;丙肝(含病毒携带者);其他传染性疾病;
艾滋病;丙肝(含病毒携带者);其他严重疾病;
艾滋病;丙肝(含病毒携带者);其他严重疾病;吞食异物;
艾滋病;肺结核;
艾滋病;肺结核;丙肝(含病毒携带者);
艾滋病;肺结核;呼吸系统疾病;
艾滋病;肺结核;呼吸系统疾病;消化系统疾病;
艾滋病;肺结核;其他传染性疾病;
艾滋病;肺结核;其他严重疾病;
艾滋病;肺结核;无;
.....
DROP TABLE #LIST
SELECT DATAS='艾滋病;' INTO #LIST UNION ALL
SELECT DATAS='艾滋病;丙肝(含病毒携带者);' UNION ALL
SELECT DATAS='艾滋病;丙肝(含病毒携带者);其他传染性疾病;' UNION ALL
SELECT DATAS='艾滋病;丙肝(含病毒携带者);其他严重疾病;' UNION ALL
SELECT DATAS='艾滋病;丙肝(含病毒携带者);其他严重疾病;吞食异物;' UNION ALL
SELECT DATAS='艾滋病;肺结核;' UNION ALL
SELECT DATAS='艾滋病;肺结核;丙肝(含病毒携带者);' UNION ALL
SELECT DATAS='艾滋病;肺结核;呼吸系统疾病;' UNION ALL
SELECT DATAS='艾滋病;肺结核;呼吸系统疾病;消化系统疾病;' UNION ALL
SELECT DATAS='艾滋病;肺结核;其他传染性疾病;' UNION ALL
SELECT DATAS='艾滋病;肺结核;其他严重疾病;' UNION ALL
SELECT DATAS='艾滋病;肺结核;无;'
---sql server 2005+
SELECT b.value,count(*)
FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(DATAS , ';' , '</v><v>')+ '</v>')
from #LIST
) A
OUTER APPLY ( SELECT value = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N (v)
) B
where b.value>''
group by b.value
---sql server 2017
SELECT b.value,count(*)
FROM #LIST A
CROSS apply string_split(A.DATAS,';') b
where b.value>''
group by b.value
value
-------------------------------------------------- -----------
艾滋病 12
丙肝(含病毒携带者) 5
肺结核 7
呼吸系统疾病 2
其他传染性疾病 2
其他严重疾病 3
吞食异物 1
无 1
消化系统疾病 1
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
info NVARCHAR(MAX)
)
GO
INSERT INTO t(info) values('艾滋病;');
INSERT INTO t(info) values('艾滋病;丙肝(含病毒携带者);');
INSERT INTO t(info) values('艾滋病;丙肝(含病毒携带者);其他传染性疾病;');
INSERT INTO t(info) values('艾滋病;丙肝(含病毒携带者);其他严重疾病;');
INSERT INTO t(info) values('艾滋病;丙肝(含病毒携带者);其他严重疾病;吞食异物;');
INSERT INTO t(info) values('艾滋病;肺结核;');
INSERT INTO t(info) values('艾滋病;肺结核;丙肝(含病毒携带者);');
INSERT INTO t(info) values('艾滋病;肺结核;呼吸系统疾病;');
INSERT INTO t(info) values('艾滋病;肺结核;呼吸系统疾病;消化系统疾病;');
INSERT INTO t(info) values('艾滋病;肺结核;其他传染性疾病;');
INSERT INTO t(info) values('艾滋病;肺结核;其他严重疾病;');
INSERT INTO t(info) values('艾滋病;肺结核;无;');
GO
--以上为测试表及测试数据
--1. 增加字符串分割表值函数
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL
DROP FUNCTION [dbo].Fun_Split
GO
CREATE FUNCTION [dbo].Fun_Split(@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
--2. 查询
SELECT
f.item
,COUNT(f.item) AS cnt
FROM t CROSS APPLY dbo.fun_split(info,';') AS f
GROUP BY f.item
/*
item cnt
-------------------- ----
艾滋病 12
丙肝(含病毒携带者) 5
肺结核 7
呼吸系统疾病 2
其他传染性疾病 2
其他严重疾病 3
吞食异物 1
无 1
消化系统疾病 1
*/
CREATE FUNCTION dbo.f_splite
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE (id INT,col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT
DECLARE @i INT = 1
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
SET @i = @i +1
END
INSERT @re
VALUES ( @i,@s )
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(Name nvarchar(47))
Insert #T
select N'艾滋病;' union all
select N'艾滋病;丙肝(含病毒携带者);' union all
select N'艾滋病;丙肝(含病毒携带者);其他传染性疾病;' union all
select N'艾滋病;丙肝(含病毒携带者);其他严重疾病;' union all
select N'艾滋病;丙肝(含病毒携带者);其他严重疾病;吞食异物;' union all
select N'艾滋病;肺结核;' union all
select N'艾滋病;肺结核;丙肝(含病毒携带者);' union all
select N'艾滋病;肺结核;呼吸系统疾病;' union all
select N'艾滋病;肺结核;呼吸系统疾病;消化系统疾病;' union all
select N'艾滋病;肺结核;其他传染性疾病;' union all
select N'艾滋病;肺结核;其他严重疾病;' union all
select N'艾滋病;肺结核;无;'
Go
--测试数据结束
SELECT t.col AS Name,
COUNT(1) AS Num
FROM #T
CROSS APPLY dbo.f_splite(#T.Name, ';') t
WHERE t.col <> ''
GROUP BY t.col;