Question

考虑下列 MySQL 表格:

应用程序(包含所有用户的所有应用程序)

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          2011-10-10     1.0      Ended

4            user_b          2010-09-09          2010-12-24     2.2      Ended
5            user_b          2011-03-03          2011-06-06     1.5      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           1.0      Active
user_b          2011-01-02           1.5      Active

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

要解释; 我想选择/ 播放所有有 < 坚固 > 状态的记录 = 主动 < / 坚固 > 。此外, 那些不是第一次申请的用户( 用户_a 和用户_b) 的得分将设定为“ 坚固” 先前的、最新的 < / 坚固” 的( 应用程序表格中的粗体部分) 。 < / 坚固] 另一方面, 首次申请的用户( 用户_ c 和用户_d) 的得分将设定为 10 。

说明/重申:

Assume that score for Ended applications/records will always be positive and not null
user_c and user_d are first time applicants
the applications table will have multiple records of the same users over time, BUT users can only have ONE Active application/record at a time

I have the following to start with; this(or a query similar to this) gave me either NULL or 0 values for the score column


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

What am I missing here?
TIA.

Answer 1

Considering you want the score to be on the basis of latest. Try this -

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

Answer 2

它应发挥作用:

SELECT user_id, date_of_application, status, coalesce(latest.score, 10) score
FROM applications
LEFT OUTER JOIN 
  (SELECT user_id, score 
   FROM applications a
   WHERE status =  Ended  AND 
         date_of_application = (SELECT MAX(date_of_application)
                                FROM applications
                                WHERE status= Ended  AND user_id = a.user_id)) latest 
                                  ON latest.user_id = applications.user_id
WHERE status =  Active 
ORDER BY date_of_application ASC

友情链接