我有以下三个表
表士兵<>之前Id |名称---------------22 |弗兰克23 |保罗24 |比尔之前表soldier_status_history <>之前soldier__id | date_record | soldier_status-----------------------------------------------------------------2012-12-07 18:09:58 | 3722 | 2012-12-08 18:10:11 | 3822 | 2012-12-10 18:20:25 | 412011-08-17 23:59:10 | 372011-08-19 23:59:1123 | 2011-08-22 00:00:00 |2011-08-25 12:00:22 | 382011-08-24 19:18:43 | 372011-08-29 19:18:44 | 38之前表soldier_values
<>之前Id | key----------------37 |值班38 |可用40 |假期41 |生病之前是否有可能进行一次选择,将它们连接起来,并只返回表soldier_status_history中特定士兵的lastdate_record的soldier_status不是"vacation"或"ill"的记录
我的意思是,对于这个名为"Paul"的人,我们有3条记录,但最后一条是他生病的记录,这样,这个人不应该显示,直到它的状态没有改变。
<>之前2011-08-17 23:59:10 | 372011-08-19 23:59:1123 | 2011-08-22 00:00:00 |select * from
soldier s
left join
(select
soldier_id,
max(date_record) maxDate_record
from soldier_status_history
group by soldier_id
) s2
on (s.id=s2.soldier_id)
left join soldier_status_history s3
on (s2.soldier_id=s3.soldier_id) and (s2.maxDate_record=s3.date_record)
where s3.soldier_status not in (40,41)
试试这个:
SELECT
h.*
FROM Soldier s
INNER JOIN soldier_status_history h ON s.ID = h.soldier_id
INNER JOIN
(
SELECT Soldier_id, MAX(date_record) latestDate
FROM soldier_status_history
GROUP BY soldier_id
) l ON h.soldier_id = s.ID
AND DATE(l.latestDate) = DATE(h.date_record);
SQL Fiddle Demo
这将给你:
| SOLDIER_ID | DATE_RECORD | SOLDIER_STATUS |
-----------------------------------------------------------------
| 22 | December, 10 2012 18:20:25+0000 | 41 |
| 23 | August, 25 2011 12:00:22+0000 | 38 |
| 24 | August, 29 2011 19:18:44+0000 | 38 |