mysql - 连接不同列中的 2 个查询

Question

我需要在数据库中加入 2 个查询，但将其放在 2 个不同的列中

我尝试了联合，但结果在同一列“总全职”中

(SELECT (SUM(servicehours) + SUM(`teachinghours`) + SUM(`researchhours`)) as 'Total fulltime'
 FROM staff_hour sh,staff s,role r 
WHERE r.roleid =s.roleid  
AND s.staffid = sh.staffid
 AND r.roleid='2'
 AND sh.staffperiodyear = 'FY2018') 
union 
(SELECT (SUM(servicehours) + SUM(`teachinghours`) + SUM(`researchhours`)) as 'Total parttime' 
FROM staff_hour sh,staff s,role r 
WHERE r.roleid =s.roleid  
AND s.staffid = sh.staffid
 AND r.roleid='3' 
AND sh.staffperiodyear = 'FY2018')

我如何加入它，以便有 2 个不同的列(“总全职”和“总兼职”)以及总和？

Answer 1

您可以通过交叉连接来使用查询

select  t1.Total_fulltime
      , t2.Total_parttime
      ,  t1.Total_fulltime + t2.Total_parttime total_sum
  from  (SELECT (SUM(servicehours) + SUM(`teachinghours`) + SUM(`researchhours`)) as Total_fulltime
   FROM staff_hour sh,staff s,role r 
  WHERE r.roleid =s.roleid  
  AND s.staffid = sh.staffid
   AND r.roleid='2'
   AND sh.staffperiodyear = 'FY2018')  t1
   CROSS JOIN 
  (SELECT (SUM(servicehours) + SUM(`teachinghours`) + SUM(`researchhours`)) as Total_parttime
  FROM staff_hour sh,staff s,role r 
  WHERE r.roleid =s.roleid  
  AND s.staffid = sh.staffid
   AND r.roleid='3' 
  AND sh.staffperiodyear = 'FY2018') t2