我在同時使用leftjoin和groupby時遇到問題。我有多行具有相同user_id的資料，所以我想使用groupby

我正在將第二個表 (job_timetable) 與 call_log table 連接起來。我在 job_timetable 表中的每個 user_id（兩個表中的公共欄位）都有多行資料。所以我使用 group by 。我想為每組 user_id 添加 column( counter) 的值。

表 call_log

     user_id  duration 
      5019    12
      2345    23
      5019    14

表job_timetable

     user_id  counter 
      5019    1
      5019    3
      2345    2
     
      

預期輸出：

     user_id  duration average duration countersum
      5019    26          13             4

      2345    23          23             2

當前輸出：不回傳任何內容

我的查詢有什么問題？

SELECT call_log.user_id,SUM(call_log.duration) as duration,avg(call_log.duration) as 
average_duration,countersum
FROM call_log 
LEFT JOIN (SELECT user_id,sum(counter) as countersum FROM job_timetable 
GROUP BY user_id )  b   on call_log.user_id= b.user_id
where call_log.user_id is not null 
group by call_log.user_id order by  call_log.user_id asc;

uj5u.com熱心網友回復：

你也必須按反和分組。

  create table call_log (
  user_id int(10), 
  duration int(10)      );

  insert into call_log values ( 5019,12),
                              ( 2345,23),
                              ( 5019,14);

  create table job_timetable  (
              user_id int(10), 
               counter int(10) );

  insert into job_timetable values ( 5019,1),
                                   ( 5019,3),
                                   ( 2345,2);


  SELECT call_log.user_id,SUM(call_log.duration) as duration,
                    avg(call_log.duration) as average_duration,
                                                    countersum
    FROM call_log 
    LEFT JOIN ( SELECT user_id,sum(counter) as countersum FROM job_timetable 
    GROUP BY user_id )  b   on call_log.user_id= b.user_id
    where call_log.user_id is not null 
    group by call_log.user_id,countersum 
    order by  call_log.user_id asc;

演示：https : //www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/77

uj5u.com熱心網友回復：

這是我的：

我喜歡將它劃分為像這樣的子查詢這樣的子任務，然后再加入它們以確保安全

 select avg_tbl.user_id tuser_id,
    avg_tbl.duration, avg_tbl.avg_duration,cnt_sum_tbl.countersum
    from 
    (select user_id, sum(duration) duration , avg(duration) avg_duration from call_log
    group by user_id ) avg_tbl
    left join 
    (select user_id, sum(counter) countersum from job_timetable
    group by user_id) cnt_sum_tbl on avg_tbl.user_id = cnt_sum_tbl.user_id

uj5u.com熱心網友回復：

嗯，我做的查詢和你做的有點不同。我剛剛計算了聚合，然后加入了它們。至少它適用于您提供給我們的資料

select cl.user_id, 
       sum_duration,
       avg_duration,
       sum_counter
  from (select user_id,
               sum(duration) sum_duration,
               round(avg(duration), 2) avg_duration
          from call_log
         group by user_id) cl
  join (select user_id,
               sum(counter) sum_counter
          from job_timetable
         group by user_id) jt
   on cl.user_id = jt.user_id
group by cl.user_id

db_fiddle

更新。為了提高可讀性，您可以使用 ctes 而不是子查詢。（從 MySQL 8 開始）

with cl as (select user_id,
               sum(duration) sum_duration,
               round(avg(duration), 2) avg_duration
          from call_log
         group by user_id),
jt as (select user_id,
               sum(counter) sum_counter
           from job_timetable
          group by user_id)

select cl.user_id, 
       sum_duration,
       avg_duration,
       sum_counter
  from cl
  join jt
    on cl.user_id = jt.user_id
 group by cl.user_id

uj5u.com熱心網友回復：

您還需要按反和分組

with call_log as
(select '5019' as user_id,12 as duration from dual
union all
select '2345' as user_id,23 as duration from dual
union all
select '5019' as user_id,14 as duration from dual
),
job_timetable as
(select '5019' as user_id,1 as counter from dual
union all
select '5019' as user_id,3 as counter from dual
union all
select '2345' as user_id,2 as counter from dual
)
select q1.user_id, sum(q1.duration) as total_duration,
avg(q1.duration) as average_duration, q2.counter_sum
from call_log q1 join (select user_id,sum(counter) as counter_sum from job_timetable group by user_id) q2 on (q1.user_id=q2.user_id)
group by q1.user_id, q2.counter_sum

標籤：mysql 加入 通过...分组 左连接

上一篇：LaravelJoin：選擇所有匹配的行

下一篇：如何選擇取消鏈接影像并從多個表中洗掉專案？