在 INNER JOIN SQL 查询中使用 GROUP BY 和 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15054349/
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
Using GROUP BY and ORDER BY on an INNER JOIN SQL query
提问by Nick
I am using the following query to group work times and expenses for clients from three tables, one for clients, one for work times and one for expenses:
我正在使用以下查询从三个表中对客户的工作时间和费用进行分组，一张用于客户，一张用于工作时间，一张用于费用：
SELECT a.*,
COALESCE(b.totalCount, 0) AS CountWork,
COALESCE(b.totalAmount, 0) AS WorkTotal,
COALESCE(c.totalCount, 0) AS CountExpense,
COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM clients A
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount
FROM work_times
WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01'
GROUP BY Client
) b ON a.Client = b.Client
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount
FROM expenses
WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01'
GROUP BY Client
) c ON a.Client = c.Client
WHERE b.Client IS NOT NULL OR
c.Client IS NOT NULL
You can see the query working in a fiddle here.
您可以在此处查看在小提琴中工作的查询。
I am trying to amend this query so that there is a row for each client for each month sorted by month and then client. I am trying to do so with the following amended query:
我正在尝试修改此查询，以便每个月的每个客户都有一行按月排序，然后按客户排序。我正在尝试使用以下修改后的查询来做到这一点：
SELECT a.*,
COALESCE(b.totalCount, 0) AS CountWork,
COALESCE(b.totalAmount, 0) AS WorkTotal,
COALESCE(c.totalCount, 0) AS CountExpense,
COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM clients A
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM work_times
GROUP BY Month,Client
ORDER BY Month
) b ON a.Client = b.Client
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM expenses
GROUP BY Month,Client
ORDER BY Month,Client
) c ON a.Client = c.Client
WHERE b.Client IS NOT NULL OR
c.Client IS NOT NULL
You can see the amended query in action here.
您可以在此处查看修改后的查询。
It's not working quite right though. Only one row is returned for Client B even though there is a work time in January 2013 and an expense in February 2013 (so there should be 2 rows) and it appears that the rows are being ordered by Client as opposed to Month. Could someone suggest how to amend the query to get the desired output which for the example on the second fiddle would be:
但它工作得并不完全正确。即使有 2013 年 1 月的工作时间和 2013 年 2 月的费用（因此应该有 2 行），也只为客户 B 返回一行，而且这些行似乎是由客户订购的，而不是月份。有人可以建议如何修改查询以获得所需的输出，对于第二个小提琴的示例将是：
╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║
╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣
║ A ║ 1 ║ 10 ║ 1 ║ 10 ║
║ B ║ 1 ║ 20 ║ 0 ║ 0 ║
║ A ║ 1 ║ 15 ║ 0 ║ 0 ║
║ B ║ 0 ║ 0 ║ 1 ║ 10 ║
║ C ║ 1 ║ 10 ║ 0 ║ 0 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝
采纳答案by Taryn
Unless I am missing something in the requirements, what you need to do is get a list of the clients and the dates and then join that to your subqueries. So your query will be:
除非我在要求中遗漏了某些内容，否则您需要做的是获取客户和日期的列表，然后将其加入您的子查询中。所以你的查询将是：
SELECT a.*,
COALESCE(b.totalCount, 0) AS CountWork,
COALESCE(b.totalAmount, 0) AS WorkTotal,
COALESCE(c.totalCount, 0) AS CountExpense,
COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM
(
select distinct c.Client, d.Month
from clients c
cross join
(
select SUBSTR(Date, 1, 7) as Month
from work_times
union
select SUBSTR(Date, 1, 7) as Month
from expenses
) d
) A
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM work_times
GROUP BY Month,Client
ORDER BY Month,Client
) b
ON a.Client = b.Client
and a.month = b.month
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM expenses
GROUP BY Month,Client
ORDER BY Month,Client
) c
ON a.Client = c.Client
and a.month = c.month
WHERE b.Client IS NOT NULL OR
c.Client IS NOT NULL
order by a.month, a.client
See SQL Fiddle with Demo.
The result is:
结果是：
| CLIENT | MONTH | COUNTWORK | WORKTOTAL | COUNTEXPENSE | EXPENSETOTAL |
--------------------------------------------------------------------------
| A | 2013-01 | 1 | 10 | 1 | 10 |
| B | 2013-01 | 1 | 20 | 0 | 0 |
| A | 2013-02 | 1 | 15 | 0 | 0 |
| B | 2013-02 | 0 | 0 | 1 | 20 |
| C | 2013-02 | 1 | 10 | 0 | 0 |
回答by Marlin Pierce
If you do an order by in a sub-query, it doesn't matter, because the outer query may (and may need to) re-order the results. You want to add an order by to the outer query.
如果您在子查询中执行 order by，则没有关系，因为外部查询可能（并且可能需要）对结果重新排序。您想向外部查询添加订单。
Your problem is that you are trying to order by the month and client of the B table, and also order by the month and client of the C table. You need to define the order of B.month, B.client, and C.month and put it into an order by for the outer query.
您的问题是您试图按B表的月份和客户订购，并按C表的月份和客户订购。您需要定义 B.month、B.client 和 C.month 的 order 并将其放入 order by 以供外部查询。
BTW, if you only group by month in the sub-query for the C table, then client is not meaningful. Some databases, like DB2, do not allow you to put an unaggregated field in a select if it is not in the group by.
顺便说一句，如果在C表的子查询中只按月份分组，那么client是没有意义的。某些数据库（如 DB2）不允许您将未聚合的字段放在 select 中，如果它不在 group by 中。
