sql – 查找列的字符串值的组合

发布时间：2020-12-12 06:34:51 所属栏目：MsSql教程来源：网络整理

SQL Server代码,如果可能的话.

假设你有一个包含两列的表格.第1列名为Monster,第2列名为Level：

Monster | Level
_______________
Small Beast | 300
Large Beast | 700
Small Dragon | 350
Large Dragon | 800

如何查询此表以获得第1列：Monster的所有可能组合？请记住,表格中的怪物数量可能会有波动.

所以输出将是：

Small Beast,Large Beast
Small Beast,Small Dragon
Small Beast,Large Dragon
Large Beast,Small Dragon
Large Beast,Large Dragon
Small Dragon,Small Beast,Large Beast
Large Dragon,Large Beast

… 等等.

然后我想为组合中的所有怪物添加第2列：Level的总和值并输出它们,如下所示：

Small Beast,Large Beast: 1000
Small Beast,Small Dragon: 650
Large Dragon,Large Beast: 1800

解决方法

您可以使用递归CTE：

;WITH cte AS (
SELECT  Monster,[Level],1 as l
FROM YourTable
UNION ALL
SELECT  c1.Monster+','+c2.Monster,c1.[Level]+c2.[Level],c1.l+1
FROM cte c1
CROSS JOIN YourTable c2
WHERE c1.Monster NOT LIKE '%'+c2.Monster+'%'
)


SELECT *
FROM cte
ORDER BY l
OPTION (MAXRECURSION 0)

输出：

Monster                                             Level   l
Small Beast                                         300     1
Large Beast                                         700     1
Small Dragon                                        350     1
Large Dragon                                        800     1
Large Dragon,Small Beast                            1100    2
Large Dragon,Large Beast                            1500    2
Large Dragon,Small Dragon                           1150    2
Small Dragon,Small Beast                            650     2
Small Dragon,Large Beast                            1050    2
Small Dragon,Large Dragon                           1150    2
Large Beast,Small Beast                             1000    2
Large Beast,Small Dragon                            1050    2
Large Beast,Large Dragon                            1500    2
Small Beast,Large Beast                             1000    2
Small Beast,Small Dragon                            650     2
Small Beast,Large Dragon                            1100    2
Small Beast,Large Dragon,Large Beast                1800    3
Small Beast,Small Dragon               1450    3
Small Beast,Small Dragon,Large Beast                1350    3
Small Beast,Large Dragon               1450    3
...
Large Beast,Small Beast   2150    4
Large Beast,Large Dragon   2150    4
Small Beast,Large Beast   2150    4
Small Beast,Large Beast,Small Dragon   2150    4

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!