MYSQL 使用 join 查询多个表

debugcn 发表于 Dev

gdr39

我正在查询 3 个表，并且可以使用对每个表的单个查询来提取正确的数据，当我将它们放在连接语句中时，返回的结果是不同的。

查询：

select advisor, round(sum(shift),1) AS avail_time from shift_track
where date >= 20180319
group by advisor order by advisor ASC

select advisor, round(sum(time/60),1) AS standard_time from shift_report
where date >= 20180319
group by advisor order by advisor ASC

select advisor, round(sum(time/60),1) AS mobile_time from sr_mobile
where date >= 20180319
group by advisor order by advisor ASC

加入我正在尝试：

SELECT shift_track.advisor, shift_track.date, sum(shift_track.shift) AS 
avail_time, 
sum(shift_report.time/60) AS stand_time,
sum(sr_mobile.time/60) AS mobile_time
from shift_track
inner join shift_report on shift_report.advisor=shift_track.advisor
inner join sr_mobile on sr_mobile.advisor=shift_track.advisor
where shift_track.date =20180319
group BY shift_track.advisor ORDER BY shift_track.advisor ASC

我试图从给定日期的每个表中获取顾问的总时间。加入查询似乎跟随堆栈上的其他查询。

另一种方法是在 PHP 中使用单独的查询，然后将结果添加到表中以显示 - 但我确定我应该能够在单个查询中完成它？？

非常感谢任何帮助。

戈登·利诺夫

使用union all：

select advisor, sum(avail_time) as avail_time, 
       sum(standard_time) as standard_time, 
       sum(mobile_time) as mobile_time
from ((select advisor, sum(shift) AS avail_time, NULL as standard_time, NULL as mobile_time
       from shift_track
       where date >= 20180319
       group by advisor
      ) union all
      (select advisor, NULL, sum(time/60) AS standard_time, NULL
       from shift_report
       where date >= 20180319
       group by advisor
      ) union all
      (select advisor, NULL, NULL, sum(time/60) AS mobile_time
       from sr_mobile
       where date >= 20180319
       group by advisor
      )
     ) t
group by advisor
order by advisor;

如果您知道所有顾问都在所有三个表中，那么您可以使用join. 但是，这保证了所有顾问都在结果中。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。