Pilih daripada dua jadual dengan cantuman dalaman dan kekangan

Question

Saya ada dua meja Service 和 Status。服务表只保存一个name和一个id

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

Ada juga jadual status seperti ini

| 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 |

Saya ingin mendapatkan data sedemikian

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

Status terkini dengan data perkhidmatan. Saya telah mencuba kenyataan ini

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

Tetapi yang saya dapat hanyalah

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

Bagaimana saya menukar penyata untuk mendapatkan apa yang saya mahukan?

Untuk menguji SQL Fiddle

Answer 1

Untuk setiap perkhidmatan, gunakan hanya jika tiada perkhidmatan berikutnya wujud NOT EXISTS Status pengembalian:

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)

Anda boleh memilih untuk melaksanakan LEFT JOIN 来返回没有任何状态的服务。如果不需要，请切换到 JOIN.

Answer 2

Anda boleh melakukan ini:

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;

Sertai dengan subquery:

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

Akan menghapuskan semua status kecuali tarikh terkini.