Sql 查询 - 具有最新输入日期的唯一项目

Question

**更新:标题应为:Sql 查询 - 具有最新条目的唯一项目

你好，

我有两张 table 。用户和回复。
用户包含用户详细信息(ID、姓名、电子邮件)
rsvp 中有 rsvp 状态(answerid、userid、eventid、answer)

注意:
答案 1 = 来
答案 2 = 不来
答案 3 = 也许
答案 4(伪答案)= 不回复

请注意，“答案 4”永远不会出现在实际的 rsvp 表数据中。它是使用下面的 SQL 动态构建的

查看谁来、谁不来、谁可能来以及谁未响应的查询如下所示:

 SELECT
  usrs.name,
  usrs.email,

  (SELECT CASE 
  WHEN rsvp.answer = 1 THEN 'Yes, see you there' 
  WHEN rsvp.answer = 2 THEN 'Cannot make it' 
  WHEN rsvp.answer = 4 THEN 'Cannot make it' 
  WHEN rsvp.answer = 3 THEN 'Maybe'
  ELSE 'No Reply' END) as attendance,

  (SELECT CASE 
  WHEN rsvp.answer = 1 THEN "1" 
  WHEN rsvp.answer = 2 THEN "3"
  WHEN rsvp.answer = 4 THEN "4"
  WHEN rsvp.answer = 3 THEN "2"
  ELSE "5" END) as ordering

  FROM jos_users usrs
  LEFT JOIN jos_rsvp as rsvp on usrs.id = rsvp.userid
  WHERE usrs.id NOT IN (62,63,128)
  ORDER BY ordering

同一用户可以回复多次。 IE。首先他们回答"is"，然后他们改变主意并回答“否”。

回复答案不会更新，只是为新答案插入一条新记录。上面的 SQL 的问题是它没有考虑到这种重复。

我正在尝试找出如何更改它，以便结果中仅显示每个用户的最后一个答案。起初看起来很简单，但现在我想不出来。

使用 MySQL。

请帮忙，
脱发
n4~

Answer 1

这个怎么样？

 SELECT
  usrs.name,
  usrs.email,

  (SELECT CASE 
  WHEN rsvp.answer = 1 THEN 'Yes, see you there' 
  WHEN rsvp.answer = 2 THEN 'Cannot make it' 
  WHEN rsvp.answer = 4 THEN 'Cannot make it' 
  WHEN rsvp.answer = 3 THEN 'Maybe'
  ELSE 'No Reply' END) as attendance,

  (SELECT CASE 
  WHEN rsvp.answer = 1 THEN "1" 
  WHEN rsvp.answer = 2 THEN "3"
  WHEN rsvp.answer = 4 THEN "4"
  WHEN rsvp.answer = 3 THEN "2"
  ELSE "5" END) as ordering

  FROM jos_users usrs, jos_rsvp rsvp,
  (SELECT userid, max(answerid) AS latest_answerid
   FROM jos_rsvp
   WHERE userid NOT IN (62,63,128)
   GROUP BY userid
  ) AS latest_rsvp
  WHERE usrs.id = rsvp.userid
  AND rsvp.userid = latest_rsvp.userid
  AND rsvp.answerid = latest_rsvp.latest_answerid
  ORDER BY ordering