SQL - 每组中不同表格项的最大值

Question

SQL - 每组中不同表格项的最大值

3

我有以下SQL表格，带有以下数据：

site-obj-Prices:

id  Parameter   Value   ActionFunc  ActionValue ChainTo ChainOperator   GroupID
1   Locality    0       Set         6           NULL        NULL        1
2   Locality    1       Set         3           NULL        NULL        2
3   Locality    0       Set         15          4           AND         3
4   State       61      Set         15          NULL        NULL        3
5   Locality    0       Set         18          6           AND         4
6   State       61      Set         18          7           AND         4
7   AreaCode    954     Set         18          NULL        NULL        4
8   Locality    0       Add         -1          9           AND         5
9   State       61      Add         -1          10          AND         5
10  AreaCode    954     Add         -1          11          AND         5
11  Supplier    242     Add         -1          NULL        NULL        5
12  Weight      3       Add         3           NULL        NULL        6
13  Weight      3       Add         2           14          AND         7
14  Supplier    242     Add         2           NULL        NULL        7

site-obj-PricesParams:

id  Parameter   Order
1   Locality    0
2   State       1
3   AreaCode    2
4   Weight      3
5   Supplier    4

在下面的查询中，我需要更改ActionLevel以反映

MAX(Order) from [site-obj-PricesParams]

针对每个GroupID中的所有参数，都需要进行操作。

因此，如果我的组有参数'Locality'和'Weight'，则ActionLevel应为3。

任何帮助都将不胜感激。

SELECT 
   id, Parameter, Value, ActionFunc, ActionValue, ChainTo, ChainOperator, GroupID, 
   COUNT(GroupID) OVER(PARTITION BY GroupID) AS ActionLevel
FROM 
   [site-obj-Prices] as Actions
WHERE
   GroupID NOT IN (SELECT [GroupID]
                   FROM [site-obj-Prices] as act
                   INNER JOIN @ParametersList as par ON act.Parameter = par.sKey 
                                                     AND act.Value <> par.sValue
                   UNION 
                   SELECT [GroupID]
                   FROM [site-obj-Prices] as act
                   LEFT JOIN @ParametersList as par ON act.Parameter = par.sKey 
                   WHERE par.sKey IS NULL
                  )
ORDER BY 
   ActionLevel ASC

- Asaf Haddad

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Saad Surya · Accepted Answer

如果我的理解没有错的话，那么希望这个方案能够奏效。

SELECT id,Parameter,Value,ActionFunc,ActionValue,ChainTo,
ChainOperator,GroupID, MAX([Order]) OVER(PARTITION BY GroupID) AS ActionLevel
FROM [site-obj-Prices] as Actions
Inner Join [site-obj-PricesParams] as Params 
On Actions.Parameter = Params.Parameter
Where GroupID NOT IN (
            SELECT [GroupID]
            FROM [site-obj-Prices] as act
            INNER JOIN @ParametersList as par
                ON act.Parameter=par.sKey AND act.Value<>par.sValue
            UNION 
            SELECT [GroupID]
            FROM [site-obj-Prices] as act
            LEFT JOIN @ParametersList as par
                ON act.Parameter=par.sKey WHERE par.sKey IS NULL
    )
    ORDER BY ActionLevel ASC