mysql - 将两个表分组到一个查询中

Question

有没有可能同时选择和分组两个表？

我的查询:

SELECT t1.id, t1.estimated_sum_all, t1.story_point_sum_all, 
 t1.time_spent, t2.id, t2.time_spent FROM
 burndown_snap_all t1, status_history_hours t2 WHERE t1.project_id = 72 
 AND t2.project_id = 72 group by t1.id

结果:

问题是我还想对第二个表 ((status_history_hours)) 进行分组，以便在一个查询中获得正确的结果。

示例:

我有两个表:

SELECT * FROM `burndown_snap_all` WHERE project_id=72 

结果:

SELECT * FROM `status_history_hours` WHERE project_id=72 

结果:

我想做的是将第二列连接到结果(time_spent)

Answer 1

是的，您可以对所需的所有表格进行分组

但是 group by 用于聚合函数，例如 sum() 或 count()，因此您可以

SELECT 
    t1.id
    , t1.estimated_sum_all
    , t1.story_point_sum_all
    , sum(t1.time_spent)
    , t2.id
    , sum(t2.time_spent ) 
FROM  burndown_snap_all t1
INNER JOIN status_history_hours t2 ON  t1.project_id = 72  AND t2.project_id = t1.project_id
group by t1.id  , t1.estimated_sum_all , t1.story_point_sum_all , t2.id

否则，如果不需要聚合功能并且需要避免重复的值，则应该使用不同的而不是组

SELECT disticnt 
    t1.id
    , t1.estimated_sum_all
    , t1.story_point_sum_all
    , t1.time_spent
    , t2.id
    , t2.time_spent 
FROM  burndown_snap_all t1
INNER JOIN status_history_hours t2 ON  t1.project_id = 72  AND t2.project_id = t1.project_id