iT邦幫忙

0

有關mysql兩張表合併語法請教

想請教大家一下我有兩張透過select產生的表,分別如下
表1:
SELECT processors.device_id,ROUND(AVG(processor_usage),2) FROM processors GROUP BY processors.device_id
https://ithelp.ithome.com.tw/upload/images/20190704/20107039zhInpaPpFN.png

表2:
select
devices.device_id,devices.hostname,devices.sysName,mempools.mempool_descr,mempools.mempool_perc,storage.storage_descr,storage.storage_perc
from devices INNER JOIN mempools ON devices.device_id=mempools.device_id INNER JOIN storage ON devices.device_id=storage.device_id
WHERE (mempools.mempool_descr='Physical memory' OR mempools.mempool_descr='Real Memory') AND (storage.storage_descr = '/' OR storage.storage_descr = 'C:')
https://ithelp.ithome.com.tw/upload/images/20190704/201070399soYAtC1sL.png

想問一下要怎麼下SQL語法才能產生類似下面這張透過device_id合併過後表,試了很久都不行,請前輩幫幫忙 感謝!!
https://ithelp.ithome.com.tw/upload/images/20190704/201070395XPRiFwc10.png

2 個回答

1
rogeryao
iT邦高手 7 級 ‧ 2019-07-05 00:07:04
SELECT * FROM (表1 SQL) AS TableA
LEFT JOIN (表2 SQL) AS TableB ON TableB.device_id=TableA.device_id

LEFT JOIN 或 INNER JOIN

真的成功了 謝謝~!

0
tine0961
iT邦新手 5 級 ‧ 2019-07-10 08:18:37

大概寫法如下

select
devices.device_id,devices.hostname,devices.sysName,mempools.mempool_descr,mempools.mempool_perc,storage.storage_descr,storage.storage_perc,ROUND(AVG(processor_usage),2)
from devices
INNER JOIN mempools ON devices.device_id=mempools.device_id
INNER JOIN storage ON devices.device_id=storage.device_id
join processors on devices.device_id==processors.device_id
WHERE (mempools.mempool_descr='Physical memory' OR
mempools.mempool_descr='Real Memory') AND (storage.storage_descr = '/' OR storage.storage_descr = 'C:')
group by devices.device_id,devices.hostname,devices.sysName,mempools.mempool_descr,mempools.mempool_perc,storage.storage_descr,storage.storage_perc

非常感謝 我來試試看!

我要發表回答

立即登入回答