mysql - 如何使用 JOIN 连接两个表

Question

任何人都可以帮助我如何在单个表中加入两个查询

发光体详细信息表:

select 
    mac_id, round(sum(luminary_count::int * wattage::int)::numeric/100, 2) as rated_power 
from 
    luminary_details AS A
join 
    geo_light as gl on A.mac_id = gl.id 
group by
    A.mac_id

//lastupdated_all

select mac_id, (tactivep::numeric)
from lastupdated_all AS A
join geo_light as gl on A.mac_id = gl.id 
group by A.mac_id

Answer 1

您可以使用geo_ligth作为主表，并将2个查询左连接为子选择..通过id = mac_id连接

select gl.id,  t1.rated_power, t2.tactivep
from  geo_light  gl
LEFT JOIN  (
  select mac_id, round(sum(luminary_count::int * wattage::int)::numeric/100, 2) as rated_power 
  from luminary_details AS A
  join geo_light as gl on A.mac_id = gl.id 
  GROUP BY A.mac_id
)  t1 on t1.mac_id  = gl.id 
LEFT JOIN  (
  select mac_id, (tactivep::numeric) num_tactivep
  from lastupdated_all AS A
  join geo_light as gl on A.mac_id = gl.id 
  GROUP BY A.mac_id
) t2 on t2.mac_id = gl.id