mysql - 一对多关系。必须是比使用子查询更好的方法

Question

我有两个表:计算机和消息。这是一对多的关系；每台计算机的多条消息。

create table computers (
  `computer` varchar(45) not null,
  `status` varchar(25),
  primary key (`computer`)
  );

INSERT INTO computers (`computer`, `status`)
VALUES
('fred','completed'),
('barney','incomplete'),
('wilma','completed');

create table messages (
`id` int(11) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `computer` varchar(45) NOT NULL,
  `message` text,
  PRIMARY KEY (`id`)
);

INSERT INTO messages (datecreated, computer,message) 
VALUES
(now(),'fred','start something'),
(now(),'fred','doing something'),
(now(),'fred','end something'),
(now(),'barney','start something'),
(now(),'barney','doing something'),
(now(),'wilma','start something'),
(now(),'wilma','doing something'),
(now(),'wilma','end something');

我试图在每台计算机上获取一行，显示开始和结束消息的状态和日期。

fred  | complete   | 2018-01-29 08:00 | 2018-01-29 08:20
wilma | incomplete | 2018-01-29 08:10 | null

现在我正在使用子查询为每台计算机查询消息表两次。我知道这不是最好的方法。当有 100 台计算机时，它将 CPU 卡在数据库上。我试过工会并加入无济于事。

select C.computer, C.status,
  (select max(M.datecreated) from messages M where C.computer=M.computer and M.message like 'start%') as date_start,
  (select max(M.datecreated) from messages M where C.computer=M.computer and M.message like 'complete%') as date_complete
from computers C
order by computer;

这是一个相关的 SQL Fiddle:http://sqlfiddle.com/#!9/f5c929/1

Answer 1

您可以利用条件聚合来获取这些日期:

select computer,
   -- in case of multiple rows with start you might better use MIN for the start date
   max(case when message like 'start%' then datecreated end) as date_start,
   max(case when message like 'end%'   then datecreated end) as date_complete
from messages
group by computer

参见 Fiddle

现在将其放入派生表并将其加入计算机