MySql合并两个查询(subselect或join或union) [英] MySql Combine two queries (subselect or join or union)
问题描述
我有两个工作查询,我想将它们组合起来以为墙面显示提供很多输出.无论如何我都不是数据库专家,尽管从这里获得了很多信息,但我还是设法从头开始创建了这些查询!
I have two working queries that I would like to combine to provide one lot of output for a wall display. I am NOT a DB person by any means but have managed to create these queries from scratch, albeit with a lot of info from here!
我有四个带有相关列的表:
I have four tables with relevant columns:
Hosts[host, hostid]
Items[hostid, itemid, itemname]
History_unit[itemid, value]
History_log[itemid, value]
hostid和itemid是我们的标识符 history_xxx表就是这样,并且是该数据的每个记录的条目.
hostid and itemid are our identifiers The history_xxx tables are just that, and entry for every record of that data.
尝试将它们组合了许多小时之后,我只是对它的工作还不够了解.
After trying to combine these for too many hours I just don't understand enough to make it work.
查询1
SELECT hosts.host,
max((case when items.name='RP_Dayend_OK' then history_uint.value end) *1000) as 'Day End',
max((case when items.name='RP_Sync_OK' then history_uint.value end) *1000) as 'Sync',
max((case when items.name='RP_Monthend_OK' then history_uint.value end) *1000) as 'Month End',
max(case when items.name='RP_Version' then history_uint.value end) as 'Version'
from hosts, items, history_uint
where hosts.hostid=items.hostid and items.itemid=history_uint.itemid and items.name like '%RP\_%'
group by hosts.host
输出:
Host | Day End | Sync | Month End | Version
Host 1 | date | date | date | 7xx
Host 2 | date | date | date | 7xx
查询2
SELECT hosts.host,
max(case when items.name='RP_Cron' then history_log.value end) as 'cron'
from hosts, items, history_log
where hosts.hostid=items.hostid and items.itemid=history_log.itemid and items.name like '%RP\_%'
group by hosts.host
输出:
Host | Cron
Host 1 | string
Host 2 | string
我想要的是:
Host | Day End | Sync | Month End | Version | Cron
Host 1 | date | date | date | 7xx | string
Host 2 | date | date | date | 7xx | string
我确实管理了一个子选择,但最终每个项目的每个主机都有不同的行,而没有'cron'的数据.我也尝试加入无济于事.只是我缺乏理解.
I did manage a sub select but I ended up with a different row for each host for each item, and no data for 'cron'. I also tried joins to no avail. It is simply my lack of understanding.
感谢您的帮助!
推荐答案
您应该放弃使用隐式(逗号分隔)连接,而应使用显式连接.在您的情况下,使用LEFT(外部)连接是合适的.
You should abandon the use of implicit (comma separated) joins in favour of explicit joins. In your case LEFT (outer) joins are appropriate.
DROP TABLE IF EXISTS Hosts;
DROP TABLE IF EXISTS Items;
DROP TABLE IF EXISTS History_unit;
DROP TABLE IF EXISTS History_uint;
DROP TABLE IF EXISTS History_log;
CREATE TABLE Hosts(host VARCHAR(20), hostid INT);
CREATE TABLE Items(hostid INT, itemid INT, name VARCHAR(20));
CREATE TABLE History_uint(itemid INT, value INT);
CREATE TABLE History_log(itemid INT, value INT);
INSERT INTO HOSTS VALUES ('HOST1',1),('HOST2',2);
INSERT INTO ITEMS VALUES
(1,1,'RP_Dayend_OK'),
(1,2,'RP_Sync_OK'),
(1,3,'RP_Monthend_OK'),
(1,4,'RP_Version' ),
(2,1,'RP_Dayend_OK'),
(2,2,'RP_Sync_OK'),
(2,2,'RP_cron')
;
INSERT INTO HISTORY_uint VALUES
(1,10),(2,10),(3,10),(4,10),
(1,50),(3,60);
INSERT INTO HISTORY_log VALUES
(1,10),(2,10),(3,10),(4,10)
;
SELECT hosts.host,
max((case when items.name='RP_Dayend_OK' then history_uint.value end) *1000) as 'Day End',
max((case when items.name='RP_Sync_OK' then history_uint.value end) *1000) as 'Sync',
max((case when items.name='RP_Monthend_OK' then history_uint.value end) *1000) as 'Month End',
max(case when items.name='RP_Version' then history_uint.value end) as 'Version',
max(case when items.name='RP_Cron' then history_log.value end) as 'cron'
from hosts
left join items on items.hostid = hosts.hostid
left join history_uint on history_uint.itemid = items.itemid
left join history_log on history_log.itemid = items.itemid
where items.name like '%RP\_%'
group by hosts.host;
+-------+---------+-------+-----------+---------+------+
| host | Day End | Sync | Month End | Version | cron |
+-------+---------+-------+-----------+---------+------+
| HOST1 | 50000 | 10000 | 60000 | 10 | NULL |
| HOST2 | 50000 | 10000 | NULL | NULL | 10 |
+-------+---------+-------+-----------+---------+------+
2 rows in set (0.00 sec)
请注意,通常最好由OP提供数据.
Note it's usually best if the OP provides the data.
这篇关于MySql合并两个查询(subselect或join或union)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!