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
