mysql左加入,MYSQL左加入GROUP BY

:)

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

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

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