一对多的关系.会比使用子查询更好 [英] One to many relationship. Got to be a better way than using sub-queries

问题描述

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

I have two tables: computers and messages. It's a one-to-many relationship; multiple messages for each computer.

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');

我正在尝试使每台计算机的一行显示开始和结束消息的状态和日期.

I am trying to get one row per computer that shows the status and the dates of the start and finish messages.

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

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

Right now I'm using sub-queries to query the message table twice for each computer. I know that can't be the best way to do this. It pegs the CPU on the database when there are 100s of computers. I've tried unions and joins to no avail.

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小提琴: http://sqlfiddle.com/#!9/f5c929 /1

Here's a related SQL Fiddle: http://sqlfiddle.com/#!9/f5c929/1

推荐答案

您可以使用GROUP BY和LEFT JOIN

select *
from computers C
left join
(
  select M.computer, max(M.datecreated) date_start
  from messages M
  where M.message like 'start%'
  group by M.computer
) s on C.computer = s.computer
left join
(
  select M.computer, max(M.datecreated) date_end
  from messages M
  where M.message like 'end%'
  group by M.computer
) e on C.computer = e.computer;

dbfiddle演示

从效率的角度来看，您还需要索引messages(computer, message, datecreated)

From the efficiency perspective, you need also index messages(computer, message, datecreated)

这篇关于一对多的关系.会比使用子查询更好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！