mysql - 从以前的最新记录中选择一列

Question

考虑以下 MySQL 表:

APPLICATIONS(包含所有用户的所有应用)

unique_id |  user_id  |  date_of_application  |  date_ended  | score  |  status
--------------------------------------------------------------------------------
1            user_a          2010-09-09          2010-12-24     1.2      Ended
2            user_a          2011-03-03          2011-06-06     1.3      Ended
3            user_a          2011-08-08          <strong>2011-10-10     1.0</strong>      Ended

4            user_b          2010-09-09          2010-12-24     2.2      Ended
5            user_b          2011-03-03          <strong>2011-06-06     1.5</strong>      Ended

6            user_a          2012-01-01                                  Active
7            user_b          2012-01-02                                  Active
8            user_c          2012-01-03                                  Active
9            user_d          2012-01-04                                  Active

期望的结果:

user_id  |  date_of_application  |  score  |  status
------------------------------------------------------
user_a          2011-01-01           <strong>1.0</strong>      Active
user_b          2011-01-02           <strong>1.5</strong>      Active

user_c          2011-01-03           10       Active
user_d          2011-01-04           10       Active

解释；我想选择/显示具有status = 'Active' 的所有记录。此外，那些不是第一次申请的用户(user_a 和 user_b)将把他们的分数设置为以前的，最新的(见申请表中的粗体部分)和'Ended ' 状态。另一方面，首次使用的用户(user_c 和 user_d)的分数将设置为 10。

注意/重申:

• 假设“已结束”申请/记录的分数始终为正数并且不为空
• user_c 和 user_d 是第一次申请者
• 随着时间的推移，applications 表将有相同用户的多个记录，但是用户一次只能有一个“事件”应用程序/记录

我有以下内容开始；这个(或类似的查询)给了我分数列的 NULL 或 0 值

SELECT userid_, date_of_application, status,
score = 
(
     SELECT score 
     FROM applications
     WHERE status = 'Ended' AND 
           date_of_application = (SELECT MAX(date_of_application)
                                  FROM applications
                                  WHERE status='Ended')
)

FROM applications

WHERE 
status = 'Active'

ORDER BY 
score ASC,
date_of_application ASC           

我在这里错过了什么？
TIA。

Answer 1

考虑到您希望分数基于最新的。试试这个-

SELECT apps.user_id, apps.date_of_application, apps.status,
       IFNULL(
              (SELECT app.score 
              FROM applications app
              WHERE  app.user_id = apps.user_id
              AND app.status = 'Ended' 
              ORDER BY app.date_ended DESC
              LIMIT 1), 10) AS score
FROM applications apps
WHERE  apps.status = 'Active'
ORDER BY apps.score ASC,
         apps.date_of_application ASC