ORACLE SQL：在一个语句中依赖于 CODE 的多个 SUMS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/285394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE SQL: Multiple SUMS dependent on CODE in One Statement
提问by Dave Costa
I believe there is a way to do this, but I'm not familiar with ORACLE 10g as many other people are. Here's the scenario:
我相信有一种方法可以做到这一点，但我不像其他许多人那样熟悉 ORACLE 10g。这是场景：
I'm currently converting Classic ASP pages to ASP.net 2.0. I have a query that is creating a report. It's reporting Sales vs. Previous Sales. What is happening currently is one query is going out to the database and grabbing a complete list of locations that sell our products. Then, it loops through every single row of the locations, and runs some summing operations in SQL.
我目前正在将经典 ASP 页面转换为 ASP.net 2.0。我有一个正在创建报告的查询。它报告的是销售额与以前的销售额。目前正在发生的事情是一个查询正在发送到数据库并获取销售我们产品的完整位置列表。然后，它遍历位置的每一行，并在 SQL 中运行一些求和操作。
It goes out to a few other tables, sums up sales quantities, then adds the sum to a table row, etc. Since the locations query returns a lot of results, the query takes a good 2-3 minutes.
它转到其他几个表，汇总销售数量，然后将总和添加到表行等。由于位置查询返回大量结果，查询需要 2-3 分钟。
My question is how can I consolidate these all into one query.
我的问题是如何将所有这些合并为一个查询。
LOCATIONS QUERY:
地点查询：
SELECT DISTINCT t.location,
l.city,
f.year,
f.customer FROM loc t,
location l, father_table f
WHERE f.number = t.number(+)
AND f.code = '0001'
AND f.c_code = '01'
AND t.location= l.code(+)
AND t.code IN ('C', 'S')
AND t.co_code IN ('G', 'V', 'A', 'D')
AND t.year = '2008'
ORDER BY l.city, f.year
The sum query for each of the rows in the above query is this:
上述查询中每一行的总和查询是这样的：
SELECT SUM(nvl(t.sale_quantity,0)) sale_quantity
FROM loc t, father_table f
WHERE f.number = t.number(+)
AND f.code = '0001'
AND f.c_code = '01'
AND f.year = '2008'
AND t.code = 'C'
AND t.location = '1566' <----- EACH ROW'S t.location VALUE
AND t.co_code IN ('G', 'V', 'A', 'D')
GROUP BY t.location, t.code, f.year
Instead of looping through each record of the original query, is there a way I can combine the queries and have the SUM in the Location's query. The key here is that the second query only gets the sales SUM when the t.code = 'C' not 'C' & 'S'
有没有一种方法可以组合查询并在 Location 的查询中包含 SUM，而不是遍历原始查询的每条记录。这里的关键是第二个查询只在 t.code = 'C' 而不是 'C' & 'S' 时获取销售额 SUM
回答by Dave Costa
I think this does what you want. If it's not exactly right, I think the key thing you need to know about is the CASE expression; this is a way to filter within the SUM function.
我认为这可以满足您的要求。如果不完全正确，我认为您需要了解的关键是 CASE 表达式；这是在 SUM 函数中过滤的一种方法。
SELECT t.location,
l.city,
f.year,
f.customer,
SUM( NVL( CASE WHEN t.code ='C' THEN t.sale_quantity ELSE 0 END, 0)) sale_quantity
FROM loc t, location l, father_table f
WHERE f.number = t.number(+)
AND f.code = '0001'
AND f.c_code = '01'
AND t.location= l.code(+)
AND t.code IN ('C', 'S')
AND t.co_code IN ('G', 'V', 'A', 'D')
AND t.year = '2008'
GROUP BY t.location, l.city, f.year
ORDER BY l.city, f.year
