Ich habe zwei Tische Service 和 Status。服務表只保存一個name和一個id
| id | name | |----|-------| | 1 | Test1 | | 2 | Test2 |
Es gibt auch eine Statustabelle wie diese
| 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 |
Ich m?chte solche Daten erhalten
| id | name | status | timestamp | |----|-------|--------|---------------------------| | 1 | Test1 | OK | October, 15 2015 09:08:07 | | 2 | Test2 | OK | October, 15 2015 10:15:23 |
Aktuellster Stand mit Servicedaten. Ich habe diese Aussage ausprobiert
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
Aber alles was ich bekam war
| id | name | status | timestamp | |----|-------|--------|---------------------------| | 2 | Test2 | OK | October, 15 2015 10:15:23 |
Wie ?ndere ich die Aussage, um das zu bekommen, was ich will?
Zum Testen von SQL Fiddle
2
0
0
對于每項服務，僅當不存在后續(xù)服務時，才使用 NOT EXISTS 返回狀態(tài)：
select ser.id, ser.name, st.status, st.timestamp
from service ser
left join status st1 on ser.id = st1.service_id
where not exists (select 1 from status st2
where st2.service_id = st1.service_id
and st2.timestamp > st1.timestamp)
可以選擇執(zhí)行 LEFT JOIN 來返回沒有任何狀態(tài)的服務。如果不需要，請切換到 JOIN。
你可以這樣做：
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
將消除除最新日期之外的所有狀態(tài)。
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
