MYSQL LEFT JOIN 与 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16163956/
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
MYSQL LEFT JOIN with GROUP BY
提问by enigmaticus
:) I have 2 queries, and I need to join them, I need to compare the working time of employee depending on activity with total working time of company in the same activity in defined period
:) 我有 2 个查询，我需要加入它们，我需要将员工根据活动的工作时间与公司在规定时间内同一活动的总工作时间进行比较
First query is:
第一个查询是：
SELECT u.login,
a.article,
p.p_article,
(SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS tottime
FROM pos p,users u, articles a
WHERE u.login = p.p_login
AND REPLACE( u.login, '.', '_' ) = 'users_name'
AND p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
AND p.p_article = a.id
GROUP BY a.article
And my second query is:
我的第二个查询是：
SELECT a.article,
p.p_article,
(SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS tottime
FROM pos p, articles a
WHERE p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
AND p.p_article = a.id
GROUP BY a.article
The first query returns me total working time of WORKER grouped by activites, for example:
第一个查询返回按活动分组的 WORKER 的总工作时间，例如：
u.login a.article p.p_article tottime
Ivan Teambuilding 1 3,45
Julie Social_work 2 5,67
The second query returns me total working time of COMPANY grouped by activites, for example:
第二个查询返回按活动分组的 COMPANY 的总工作时间，例如：
a.article p.p_article tottime
Teambuilding 1 150
Social_work 2 260
I want to have something like this, so I can compare the total time of worker per activity with total time of company working hours per activity in specific period:
我想要这样的东西，所以我可以将每项活动的工人总时间与特定时期内每项活动的公司工作时间总时间进行比较：
u.login a.article p.p_article tottime(worker) tottime(company)
Ivan Teambuilding 1 3,45 150
Julie Social_work 2 5,67 260
In case of the NULL values I would like to use LEFT JOIN. I was searching for the solution for 3 hours, and everything I try is not working, so any help would be appreciated.
如果是 NULL 值，我想使用 LEFT JOIN。我一直在寻找解决方案 3 个小时，但我尝试的一切都不起作用，因此我们将不胜感激。
采纳答案by Kickstart
You can just join the 2 queries together as a pair of subselects.
您可以将 2 个查询作为一对子选择连接在一起。
Something like:-
就像是：-
SELECT Sub1.a, Sub1.b, Sub2.c
FROM (SELECT a, b FROM z) Sub1
INNER JOIN (SELECT a, c FROM y) Sub2
ON Sub1.a = Sub2.a
However can't really give you more as you first example query doesn't seem to bring back the details you say (only brings back 3 columns).
但是，不能真正为您提供更多信息，因为您的第一个示例查询似乎没有带回您所说的详细信息（仅带回 3 列）。
EDIT - With the corrected queries
编辑 - 使用更正的查询
SELECT Sub1.login AS User_name, Sub1.article AS Activity, Sub1.p_article AS `Activity id`, Sub1.tottime AS `Totaltime(worker)`, Sub2.tottime AS `Totaltime(company)`
FROM (SELECT u.login,a.article, p.p_article, (SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS tottime
FROM pos p
INNER JOIN users u ON u.login = p.p_login
INNER JOIN articles a ON p.p_article = a.id
WHERE REPLACE( u.login, '.', '_' ) = 'users_name'
AND p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
GROUP BY a.article) Sub1
INNER JOIN
(SELECT a.article, p.p_article, (SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS tottime
FROM pos p
INNER JOIN articles a ON p.p_article = a.id
WHERE p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
GROUP BY a.article) Sub2
ON Sub1.p_article = Sub2.p_article
回答by Bernhard Barker
The simplest would be using sub-queries (though they're in general not too efficient, but those GROUP BY's may make other solutions difficult).
最简单的方法是使用子查询（尽管它们通常效率不高，但这些GROUP BY可能会使其他解决方案变得困难）。
Something like this should do it:
像这样的事情应该这样做：
SELECT a.*, b.tottime AS 'Total time (company)'
FROM
(SELECT u.login, a.article, p.p_article, (SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS 'Total time (worker)'
FROM pos p, users u, articles a
WHERE u.login = p.p_login
AND REPLACE( u.login, '.', '_' ) = 'users_name'
AND p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
AND p.p_article = a.id
GROUP BY a.article) a
LEFT JOIN
(SELECT a.article, p.p_article, (SUM(p.p_going) + SUM(p.p_leaving) + SUM(p.p_working)) AS tottime
FROM pos p, articles a
WHERE p.p_datum >= '2013-04-09'
AND p.p_datum <= '2013-04-16'
AND p.p_article = a.id
GROUP BY a.article) b
ON a.article = b.article /* AND a.p_article = b.p_article ?? */
