SQL从具有内部连接和限制的两个表中选择

2021-11-20 00:00:00 sql mysql

我有两个表 Service 和 Status.服务表只有一个name和一个id

I have two tables Service and Status. The service table only holds a name and an id

| id |  name |
|----|-------|
|  1 | Test1 |
|  2 | Test2 |

还有一个像这样的状态表

And a Status table like this 

| id | status | service_id |                 timestamp |
|----|--------|------------|---------------------------|
|  1 |     OK |          1 | October, 15 2015 09:03:07 |
|  2 |     OK |          1 | October, 15 2015 09:08:07 |
|  3 |     OK |          2 | October, 15 2015 10:05:23 |
|  4 |     OK |          2 | October, 15 2015 10:15:23 |

我想得到这样的数据

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  1 | Test1 |     OK | October, 15 2015 09:08:07 |
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

带有服务数据的最新状态.我已经尝试过这个声明

The latest Status with the service data. I have tried this statement

SELECT ser.id, ser.name, a.status, a.timestamp
from Service ser
  inner join (select * from status
              order by Status.timestamp
              DESC limit 1) as a
    on a.service_id = ser.id

但我只得到

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

如何更改语句以获得我想要的结果?

How can I change the statement to get what I want?

用于测试 SQL Fiddle

推荐答案

您可以这样做:

SELECT 
  ser.id, 
  ser.name, 
  s.status, 
  s.timestamp 
FROM Service ser 
INNER JOIN status as s ON s.service_id = ser.id
INNER JOIN
(
   SELECT
     service_id, 
     MAX(timestamp) AS MaxDate
   FROM status 
   GROUP BY service_id
) AS a  ON a.service_id = s.service_id 
       AND a.MaxDate = s.timestamp;

与子查询的连接:

SELECT
  service_id, 
  MAX(timestamp) AS MaxDate
FROM status 
GROUP BY service_id

将清除除日期最晚的状态之外的所有状态.

Will eliminate all the statuses except the one with the latest date.

相关文章