MySql 合并两个查询(subselect or join or 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.

感谢您的帮助！

推荐答案

您应该放弃使用隐式(逗号分隔)连接，转而使用显式连接.在您的情况下，左(外)连接是合适的.

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 or join or union)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！