代码之家 › 专栏 › 技术社区 › fdmillion

聚合函数中的多列,但仅适用于与WHERE子句匹配的行

tsql sql-server sql

fdmillion · 技术社区 · 7 年前

示例数据集:

+----------+---------+-----+
| order_id | prod_id | qty |
+----------+---------+-----+
|        1 |       1 |   2 |
|        1 |       1 |   4 |
|        1 |       3 |   1 |
|        2 |       1 |   1 |
|        2 |       2 |   2 |
|        3 |       4 |   1 |
|        3 |       4 |   2 |
|        3 |       2 |   3 |
|        3 |       3 |   5 |
|        4 |       1 |   2 |
|        4 |       2 |   3 |
|        4 |       3 |   3 |
|        4 |       4 |   3 |
|        4 |       6 |   2 |
+----------+---------+-----+

我想做的是构建一个“报告”视图,它将聚合某些基于产品代码的总计。举个例子,我想要一列按订单列出产品代码1的总计,另一列列出产品2和3的总计,还有一列列出产品代码4和6。

请注意,产品代码的值来自一个产品类表,因此我实际上希望得到第1类中的所有产品,即产品1,然后是第2类中的所有产品,即产品2和3,然后是第3类中的所有产品,即产品4和6。

+----------+--------+--------+--------+
| order_id | c1_tot | c2_tot | c3_tot |
+----------+--------+--------+--------+
|        1 |      6 |      1 |      0 |
|        2 |      1 |      2 |      0 |
|        3 |      0 |      8 |      3 |
|        4 |      2 |      6 |      5 |
+----------+--------+--------+--------+

我可以这样做一列使用如下WHERE子句:

SELECT 
    [order_id],
    SUM([qty]) AS [c2]
FROM [orders]
WHERE [prod_id] IN (SELECT [id] FROM [product_class] WHERE [class] = 2)
GROUP BY [order_id]

我提出的另一种方法是存储函数,但这似乎是一种冗长的方法:

SELECT DISTINCT
    [order_id],
    total_products_by_class([order_id],1) AS [c1_tot],
    total_products_by_class([order_id],2) AS [c2_tot],
    total_products_by_class([order_id],3) AS [c3_tot]
FROM [orders]

我怀疑理论上可以用解析函数做些什么?。。。

有没有一种方法可以在不使用存储函数的情况下实现这一点?

更新: 更清楚地说明需要从子查询中提取产品的值。

2 回复 | 直到 7 年前

tarheel 7 年前

我获取了为 orders 并创建了 product_class 问题第二段描述中的表格。

样本数据:

create table #orders
    (
        order_id int
        , prod_id int
        , qty int
    )

create table #product_class
    (
        id int
        , class int
    )

insert into #orders
values (1, 1, 2)
    , (1, 1, 4)
    , (1, 3, 1)
    , (2, 1, 1)
    , (2, 2, 2)
    , (3, 4, 1)
    , (3, 4, 2)
    , (3, 2, 3)
    , (3, 3, 5)
    , (4, 1, 2)
    , (4, 2, 3)
    , (4, 3, 3)
    , (4, 4, 3)
    , (4, 6, 2)

insert into #product_class
values (1, 1)
    , (2, 2)
    , (3, 2)
    , (4, 3)
    , (6, 3)

答复:

从问题中编写的第一个查询开始,我将引用改为 product\u类 表到an inner join 并添加了 class 到 group by . 在那之后,需要做的就是 pivot 获取 班 es来遍历列而不是行。

select b.order_id
, isnull(b.[1], 0) as c1_tot
, isnull(b.[2], 0) as c2_tot
, isnull(b.[3], 0) as c3_tot
from (
    SELECT o.[order_id]
    , pc.class
    , SUM(o.[qty]) AS [c2]
    FROM [#orders] as o
    inner join #product_class as pc on o.prod_id = pc.id
    GROUP BY o.[order_id]
    , pc.class
    ) as a
pivot (max(a.c2) for a.class in ([1], [2], [3])) as b

输出与所需的输出完全匹配。

clinomaniac 7 年前

在一个简单的查询中,可能需要使用CASE语句。

SELECT  [order_id],
      SUM(CASE WHEN prod_id = 1 THEN qty ELSE 0) AS p1_tot,
      SUM(CASE WHEN prod_id = 2 OR prod_id = 3 THEN qty ELSE 0) AS p2and3_tot,
      SUM(CASE WHEN prod_id = 4 OR prod_id = 6 THEN qty ELSE 0) AS p4and6_tot  
FROM [orders] 
    INNER JOIN [product_class] ON [orders].[prod_id] = [product_class].[id]
WHERE [product_class].[class] = 2
GROUP BY [order_id];