mysql - SQL 选择消除重复项并返回子表上记录的最新日期

Question

我正在尝试从主表 my_form 和通过链接表链接的表中检索数据。操作表可能包含多个标题记录的 action_required = 'Close' 记录。

这是我的三个表:

my_form:
# idmy_form, summary, description, station
'1', 'OSR puncture', 'Puncture while driving to work', 'Thornbury'
'2', 'Windscreen wiper broke', 'Wiper broke while going shopping', 'Bristol'
'3', 'Out of screenwash', 'Run out of screen wash en route to Cardiff', 'Cardiff'


form_action:
# idmy_form, action_id
'1', '201'
'1', '202'
'1', '203'
'2', '301'
'2', '302'
'3', '401'
'3', '402'
'3', '403'
'3', '404'
'3', '405'

actions:
# action_id, action_detail, action_required, action_date
'201', 'Fix', 'Open', '2013-01-01 00:00:00'
'202', 'Fix', 'Amend', '2013-01-04 00:00:00'
'203', 'Fix', 'Close', '2013-01-06 00:00:00'
'301', 'Fix', 'Open', '2013-03-01 00:00:00'
'302', 'Fix', 'Review', '2013-04-12 00:00:00'
'401', 'Fix', 'Open', '2013-09-04 00:00:00'
'402', 'Fix', 'Review', '2013-09-12 00:00:00'
'403', 'Fix', 'Close', '2013-09-17 00:00:00'
'404', 'Fix', 'Reopen', '2013-09-21 00:00:00'
'405', 'Fix', 'Close', '2013-09-23 00:00:00'

到目前为止我的sql是这样的:

SELECT 
mf.idmy_form, 
mf.summary, 
mf.station, 
a.action_id, 
a.action_required, 
a.action_date 
FROM my_form mf
LEFT JOIN (
SELECT 
   act.action_id, 
   act.action_required, 
   act.action_date, 
   fa.idmy_form 
FROM form_action fa
JOIN actions act 
 ON fa.action_id = act.action_id 
 AND act.action_required = 'Close'
) a 
ON a.idmy_form = mf.idmy_form

这将返回以下结果:

# idmy_form, summary, station, action_id, action_required, action_date
'1', 'OSR puncture', 'Thornbury', '203', 'Close', '2013-01-06 00:00:00' 
'2', 'Windscreen wiper broke', 'Bristol', NULL, NULL, NULL
'3', 'Out of screenwash', 'Cardiff', '403', 'Close', '2013-09-17 00:00:00'
'3', 'Out of screenwash', 'Cardiff', '405', 'Close', '2013-09-23 00:00:00'

理想情况下，我只想为相关操作具有 action_required = 'Close' 的每个 header 记录检索最新的日期_completed。所以从上面的结果中删除第三行。

我尝试在内部选择中添加 group by，但它抛出一个 oracle 而不是 group by 表达式缺少右括号。

Answer 1

添加子查询以获取 MAX() 日期。 Here描述了执行此操作的更多方法。

SELECT 
mf.idmy_form, 
mf.summary, 
mf.station, 
a.action_id, 
a.action_required, 
a.action_date 
FROM my_form mf
LEFT JOIN (
SELECT 
   act.action_id, 
   act.action_required, 
   act.action_date, 
   fa.idmy_form 
FROM form_action fa
JOIN actions act 
 ON fa.action_id = act.action_id 
 AND act.action_required = 'Close'
WHERE action_date = (SELECT MAX(action_date) 
                     FROM actions 
                     JOIN form_action USING(action_id)
                     WHERE actions.action_required = 'Close'
                     AND fa.idmy_form = form_action.idmy_form)
) a 
ON a.idmy_form = mf.idmy_form

• 看到它在 sqlfiddle 中实时运行