SQL查询：对表中所有可能的行组合求和。

Question

SQL Server 2008 R2

示例表结构

create table TempTable
(
    ID int identity,
    value int
)

insert into TempTable values(6)
insert into TempTable values(7)
insert into TempTable values(8)
insert into TempTable values(9)
insert into TempTable values(10)

我希望有这样一个内容： 返回行，使得它们的总和为所有剩余行的总和，如下所示。

6+7
6+8
6+9
6+10

6+7+8
6+7+9
6+7+10


6+8+9
6+8+10

6+9+10

6+7+8+9

6+7+8+9+10

AronLS方案满足了我的需求。然而，我会将问题保持几天的开放状态。 1. 是否有其他/更好的方法可以做到这一点？

- user2647784

---

你正在使用哪个版本的SQL Server？ - bobthedeveloper

为什么你想要 6+7+8+9+10 和 10+6+7+8+9？两者都会得到相同的结果。 - user330315

我会忽略我标记为重复的部分，所以其中一个将被忽略。 - user2647784

Answer 1

这是一个“所有可能排列”的问题，可以通过自连接和递归CTE解决。

With selfrec as (
  Select t.Value, t.ID, 0 as Level From TempTable t
       UNION ALL
  Select t2.Value + t1.Value as Value, t1.ID, Level + 1 From TempTable t1 
  Inner Join selfrec t2 on t1.ID < t2.ID 
  Where Level < 4 -- limit the number of recursions
)
Select * From selfrec
Order By Level

这个条件可以排除匹配它自身的条目，以及重复的排列组合反转：

t1.ID < t2.ID

限制递归的深度，因为我们没有其他的中断条件，将超过允许的递归次数：

Where Level < 4

- AaronLS

---

谢谢，我已经检查了。然而结果有些不同。同时t2.ID出现了错误。 - user2647784

@user2647784，我进行了一些编辑，因为我在其中犯了一个错误，如果你想再试一次的话。祝你好运。 - AaronLS

@user2647784 如果你的数据库配置为区分大小写，那么在ID和Id之间可能会出现错误。 - AaronLS

@user2647784 结果也不会按照您的期望顺序排序。您可以添加一个列来进行字符串连接，以确定源添加的内容。 - AaronLS

@user2647784 添加 order by Level 以获得稍微更好的排序。 - AaronLS

谢谢，这似乎有效。现在我可以使用这个解决方案继续前进了。 - user2647784

Answer 2

顺便提一下——稍作修饰，您就可以看到发生了什么：

CREATE TABLE #TempTable
(
    Expr VARCHAR(10),
    ID INT IDENTITY,
    value INT
)

INSERT INTO #TempTable VALUES('6', 6)  -- a
INSERT INTO #TempTable VALUES('7', 7)  -- b
INSERT INTO #TempTable VALUES('8', 8)  -- c
INSERT INTO #TempTable VALUES('9', 9)  -- d
INSERT INTO #TempTable VALUES('10', 10) -- e

SELECT expr, value FROM #TempTable

-- Choose(n,k) = n!/(n!(n-k)!)  [order not important]
-- n = 5, k = {1,2,3,4,5}

-- C(5,1) + C(5,2) + C(5,3) + C(5,4) + C(5,5)      
--    5   +   10   +   10   +   5    +   1     = 31
-- There should be 31 rows.
------
;
WITH  selfrecCTE AS
( 
   SELECT
      CAST(expr AS VARCHAR(100)) AS Expr
      , T.value
      , T.ID
      , 0 AS Level
   FROM
      #TempTable AS T
   UNION ALL
   SELECT
      CAST(t1.expr + ' + ' + T2.expr AS VARCHAR(100)) AS Expr
      , T2.value + T1.value AS Value
      , T1.ID
      , Level + 1
   FROM
      #TempTable T1
      INNER JOIN selfrecCTE T2
         ON T1.ID < T2.ID
   --WHERE
   --  Level < 4 -- limit the number of recursions
)
   -- 31 rows. OK.
   SELECT  * FROM selfrecCTE
   ORDER By id, expr, Level

我觉得这是一个有趣的消遣。

谢谢你的问题！

- Jim the Frayed