sql - Postgres : How to join multiple tables?

Question

我在 postgres10 中有 2 个表，T1 和 T2，如下所示，我想要 3 个不同的查询:

1. 一次查询中每个用户的 nb_hour_doc + nb_hour_interv 的总和；
2. 一次查询中每个用户的 nb_doc + nb_interv 的总和；
3. 每个项目的 nb_hour_doc + nb_hour_interv 的总和；

表T1

| user_id   |      user_name      |  project |  nb_hour_doc |  nb_doc |  doc_start        | doc_end           ||-----------|:-------------------:|---------:|--------------|:-------:|------------------:|-------------------||1001       |Toto                 |OTHERS    |11000         |2        |2018-03-25 12:13:00|2018-03-25 12:15:00||1002       |Diana                |GIT       |7000          |1        |2018-03-25 13:00:00|2018-03-25 13:15:00||1003       |Alex                 |DEV       |7001          |3        |2018-03-25 11:03:05|2018-03-25 11:35:40||1003       |Alex                 |PLUG      |10000         |1        |2018-03-25 12:23:00|2018-03-25 12:25:00||1002       |Diana                |PLUG      |7100          |1        |2018-03-25 12:16:00|2018-03-25 12:51:00|

Table T2:

| user_id | user_name | project | nb_hour_interv | nb_interv | int_start | int_end ||---------|:---------:|--------:|-------------|--------|---------------------|---------------------|| 1003 | Alex | OTHERS | 71 | 2 | 2018-03-25 12:13:00 | 2018-03-25 12:15:00 || 1003 | Alex | DEV | 71 | 1 | 2018-03-25 08:11:00 | 2018-03-25 08:30:00 || 1003 | Alex | OTHERS | 171 | 5 | 2018-03-25 02:08:00 | 2018-03-25 02:45:00 || 1001 | Toto | NEXT | 31 | 4 | 2018-03-25 13:13:00 | 2018-03-25 13:15:00 || 1003 | Alex | PLUG | 10 | 1 | 2018-03-25 14:19:00 | 2018-03-25 14:55:00 || 1002 | Diana | FIRST | 70 | 1 | 2018-03-25 17:43:00 | 2018-03-25 17:55:00 |

and a tables containing the information about user:

users:

| user_id | user_name ||---------|:---------:|| 1002 | Diana || 1003 | Alex || 1004 | Boto || 1001 | Toto |

I use this query but it didn't give the expected result:

select u.user_id, u.user_name, COALESCE(sum(vd.nb_hour_doc),0) as nb_hour_doc, COALESCE(sum(vd.nb_doc),0) as nb_doc, COALESCE(vi.project,'none') as projet_nom_doc, COALESCE(vd.project,'none') as projet_nom_int, 
COALESCE(sum(vi.nb_hour_interv),0) as nb_hour_interv, COALESCE(sum(vi.nb_interv),0) as nb_interv from users u
LEFT JOIN T1 vd ON vd.user_id = u.user_id
LEFT JOIN T2 vi ON vi.user_id = u.user_id

GROUP BY vi.project, u.user_id,  u.user_name, vd.project

谁能帮帮我？

我想要这样的东西:

1. nb_hour_doc + nb_hour_interv 的总和

| user_id   | user_name     | nb_hour_doc+ nb_hour_interv   ||---------  |:---------:    |-----------------------------  || 1002  | Diana     | 7070  || 1003  | Alex  | 17324     || 1004  | Boto  | 0     || 1001  | Toto  | 11031     |

1. 每个用户的 nb_doc + nb_interv 的总和:

| user_id   | user_name     | (nb_doc + nb_interv)  ||---------  |:---------:    |-------------  || 1001      |    Toto       | 6         || 1002      |   Diana       | 3         || 1003      |    Alex       | 13            || 1004      |    Boto       | 0         |

1. 每个项目的 nb_hour_doc + nb_hour_interv 的总和

| project   | (nb_hour_doc+ nb_hour_interv) ||---------  |-------------  || OTHERS    | 11242         || DEV       | 7072          || NEXT      | 31            || PLUG      | 10010         || FIRST     | 70            || GIT       | 7000          |

Answer 1

我认为这应该可以工作，尽管我只在 MariaDb (MySQL) 中测试过它

编辑:修复了最后一个项目查询

SELECT u.user_id, u.user_name, 
   COALESCE((SELECT SUM(nb_hour_doc) FROM T1 WHERE user_id = u.user_id GROUP BY user_id) + 
            (SELECT SUM(nb_hour_interv) FROM T2 WHERE user_id = u.user_id GROUP BY user_id), 0) AS hours
FROM users u

SELECT u.user_id, u.user_name, 
  COALESCE((SELECT SUM(nb_doc) FROM T1 WHERE user_id = u.user_id GROUP BY user_id) + 
           (SELECT SUM(nb_interv) FROM T2 WHERE user_id = u.user_id GROUP BY user_id), 0) AS hours
FROM users u


SELECT z.project, SUM(z.hour)
FROM (SELECT project, nb_hour_doc as hour 
      FROM T1
      UNION ALL
      SELECT project, nb_hour_interv AS hour
      FROM T2
     ) as z
GROUP BY z.project