SQL从具有内部连接和限制的两个表中选择
我有两个表 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.
相关文章