MYSQL左加入GROUP BY [英] MYSQL LEFT JOIN with GROUP BY

问题描述

:)
我有2个查询，我需要加入他们，我需要将在活动期间员工的工作时间与公司在同一活动中的总工作时间进行比较

:) 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

第一个查询是：

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:

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:

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

在NULL值的情况下，我想使用LEFT JOIN。我正在寻找3个小时的解决方案，而我尝试的所有东西都无法正常工作，所以我们将不胜感激。

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.

推荐答案

您可以将两个查询作为一对子查询加入。

You can just join the 2 queries together as a pair of subselects.

类似于： -

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

然而，如果您的第一个示例查询似乎无法恢复您所说的详细信息（仅返回3列），则无法真正给您提供更多信息。

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).

编辑 - 使用更正的查询

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

这篇关于MYSQL左加入GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！