作者funk6478 (大恩)
看板Database
标题Re: [SQL ] 栏位字串分离统计的问题
时间Thu Feb 25 11:40:37 2021
※ 引述《otherman (总经理)》之铭言：
: 资料库名称： MSSQL
: 资料库版本： 2014
: 内容/问题描述：
: table a
: seq string
: 1 a1,b1,c1
: 2 a2,c1
: 3 d3
: 4 a2,b1
: 5 z1,z2
: 将string栏位分离後计算数量，然後产出如下统计表
: a1 a2 a3 b1 b2 b3 c1 c2 c3...z1 z2 z3
: =====================================
: 1 2 0 2 0 0 2 0 0 1 1 0
: 我用pivot +string_spilt 来select做出来的结果都不是我想要的，因为栏位不确定
: 请问先进们有相关的统计经验可以指点一下吗？谢谢！
CREATE TABLE [#ttt]
(
[seq] int,
[string] nvarchar(4000)
)
INSERT INTO #ttt ([seq],[string])
VALUES
(1,'a1,b1,c1'),
(2,'a2,c1'),
(3,'d3'),
(4,'a2,b1'),
(5,'z1,z2')
--SELECT * FROM #ttt
CREATE TABLE [#tttt]
(
[string_all] nvarchar(4000)
)
INSERT INTO #tttt ([string_all])
VALUES
('a1'),
('a2'),
('a3'),
('b1'),
('b2'),
('b3'),
('c1'),
('c2'),
('c3'),
('z1'),
('z2'),
('z3')
--SELECT * FROM #tttt
DECLARE @UGG NVARCHAR(MAX)
DECLARE @UHH NVARCHAR(MAX)
DECLARE @UHHH NVARCHAR(MAX)
SELECT @UGG = COALESCE(@UGG + ',','') + [string]
FROM (SELECT [string] from #ttt) as GG
SELECT @UHH = '(''' + replace(@UGG,',','''),(''') + ''')'
SELECT @UHHH = '(''''' + replace(@UGG,',','''''),(''''') + ''''')'
DECLARE @uuu AS nvarchar(4000)
SELECT @uuu = N'
DECLARE @ggg AS TABLE([string_new] nvarchar(4000))
INSERT INTO @ggg
VALUES ' + @UHH + N'
DECLARE @UNIC NVARCHAR(MAX)
SELECT @UNIC = COALESCE(@UNIC + '','','''') + QUOTENAME([string_new])
from (SELECT DISTINCT TOP 1000 [string_new] FROM @ggg
ORDER BY [string_new]
) as QQ
DECLARE @UNICT NVARCHAR(MAX)
SELECT @UNICT = COALESCE(@UNICT + '','','''') + QUOTENAME([string_all])
from (SELECT DISTINCT TOP 1000 [string_all] FROM #tttt
ORDER BY [string_all]
) as QQ
DECLARE @UTEXT NVARCHAR(MAX)
SELECT @UTEXT = N''
DECLARE @gggg AS TABLE([string_new] nvarchar(4000))
INSERT INTO @gggg
VALUES ' + @UHHH + N'
SELECT * FROM (
SELECT [string_all]
,case when [C] = 1 then 1 else 0 end [CG]
FROM #tttt
left join (
SELECT [string_new]
,1 as [C]
FROM @gggg
) as tg
on [string_all] = [string_new]
) as FF
PIVOT ( SUM([CG]) for [string_all] in ('' + @UNICT + N'')) as YY
''
EXEC sp_executesql @UTEXT
'
--select @uuu
EXEC sp_executesql @uuu
DROP TABLE #ttt
DROP TABLE #tttt
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 140.112.207.182 (台湾)
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1614224439.A.FEE.html
1F：推 otherman: 谢谢f大提供这麽详细的解说…我先试一下，感谢！ 02/25 15:59
2F：推 cutekid: 推(Y)，学习+1！ 02/26 18:40