使用 MS SQL 获取符合特定条件的所有可能组合 [英] Getting all possible combinations which obey certain condition with MS SQL

问题描述

我需要构造一个 SQL 查询，但我不知道该怎么做.如果有人帮助，我会非常感谢.

I need to constract an SQL query but I have no idea how to do it. If someone helps, I'll appriciate it very much.

我有下表

GroupedBYField          ConditionField          ToBeSummeField
     1                        1                      1
     1                        1                      2
     1                        1                      3
     2                        2                     100
     2                        2                     200
     2                        2                     300

并且我需要获得 groupedBYField, SUM(ToBeSummeField) 的所有可能组合，它具有SUM(conditionField) = 2，即下表

and I need to get all the possible combinations of groupedBYField, SUM(ToBeSummeField) which has SUM(conditionField) = 2, that is the following table

GroupedBYField          SumField
     1                     3
     1                     4
     1                     5
     2                    100
     2                    200
     2                    300

感谢您的帮助！

推荐答案

我相信这有效.它也应该适用于出现 0 的 ConditionField 值.
它将在 SQL 2005/2008 上运行.

I believe this works. It should also work where ConditionField values of 0 appear.
It will run on SQL 2005/2008.

它使用递归 CTE 来处理添加到所需值的任意数量的潜在行

It uses a recursive CTE to deal with any number of potential rows adding to the required value

DECLARE @t TABLE 
(GroupedBYField INT
,ConditionField INT
,ToBeSummeField INT
)

INSERT @t
      SELECT 1,1,1
UNION SELECT 1,1,2
UNION SELECT 1,1,3
UNION SELECT 2,2,100
UNION SELECT 2,2,200
UNION SELECT 2,2,300


;WITH numCTE
AS
(
        SELECT ROW_NUMBER() OVER (ORDER BY GroupedBYField
                                           ,ConditionField
                                           ,ToBeSummeField
                                 ) AS id
               ,*
        FROM @t               
)
,myCTE
AS
(
        SELECT id
               ,GroupedBYField
               ,ConditionField
               ,ToBeSummeField
               ,'|' + CAST(id AS VARCHAR(MAX)) + '|' AS LEVEL
        FROM numCTE

        UNION ALL

        SELECT t.id
               ,t.GroupedBYField
               ,m.ConditionField + t.ConditionField
               ,m.ToBeSummeField + t.ToBeSummeField
               ,m.LEVEL + '|' + CAST(t.id AS VARCHAR(11)) + '|' AS LEVEL
        FROM myCTE  AS m
        JOIN numCTE AS t
        ON   t.id             > m.id
        AND  t.GroupedBYField = m.GroupedBYField
        AND  m.LEVEL NOT LIKE '%|' + CAST(t.id AS VARCHAR(MAX)) + '|%'

)
SELECT GroupedBYField
       ,ToBeSummeField
FROM myCTE
WHERE ConditionField = 2 -- amend this value change the target sum
ORDER BY 1,2
OPTION (MAXRECURSION 0)

编辑 - 添加了 maxrecursion 0 以允许它处理任意数量的源行

EDIT - added maxrecursion 0 to permit this to work on any number of source rows

这篇关于使用 MS SQL 获取符合特定条件的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！