mysql - 多对多不同行(基于日期的最后一行)

Question

我有三张 table

图片(表):

Pic_id
Pic name

审核(多对多关系)(tbl):

Pic_id
state_id
date(type: datetime)

状态(表):

state_id
state_name

我可以为特定图片设置不同的状态，但会考虑基于日期的最后一个!

我已尝试以下方法来查询所有图片(应该是不同的)并显示状态(基于审核表中的日期的最后一个状态)，但显然我错了:

SELECT distinct P.pic_id,m.state_id,m.date,st.state_name
FROM Pictures P
 left join moderation m
 on P.pic_id = m.pic_id
  join state st
 on m.state_id = st.state_id
group by P.pic_id
order by m.date ASC

请告诉我正确的方法是什么？

如果您需要更多说明，请告诉我!

Pictures:

pic_id  Pic_name
1   Test1
2   Test2

Moderation:
Pic_id              state_id               date
1           1       2000-01-01  
1           2       2005-01-01
2           2       2000-01-01
2           1       2001-01-01

state:
state_id            state_name
1           accepted    
2           declined

Desired out put:

 P.pic_id,   m.state_id,   m.date,   st.state_name
 1      2        2005-01-01     declined
 2      1        2001-01-01     accepted

Answer 1

执行此操作的常见方法是使用最大聚合将表连接回其自身。

select P.pic_id, m.state_id, m.date, st.state_name
from Pictures P
  join (select pic_id, max(date) maxdate
        from moderation
        group by pic_id
        ) t ON p.pic_id = t.pic_id
  join moderation m on P.pic_id = m.pic_id and t.maxdate = m.date
  join state st on m.state_id = st.state_id

• SQL Fiddle Demo

如果您想要的结果包括所有图片(即使是没有状态的图片)，那么您需要使用外连接。

<小时/>

编辑:如果您担心潜在的联系，这是 MySQL 使事情变得更容易的一次，因为您可以添加一个 group by 它将返回一个随机状态:

select P.pic_id, m.state_id, m.date, st.state_name
from Pictures P
  join (select pic_id, max(date) maxdate
        from moderation
        group by pic_id
        ) t ON p.pic_id = t.pic_id
  join moderation m on P.pic_id = m.pic_id and t.maxdate = m.date
  join state st on m.state_id = st.state_id
group by p.pic_id