题解 | #牛客每个人最近的登录日期(六)#
牛客每个人最近的登录日期(六)
http://www.nowcoder.com/practice/572a027e52804c058e1f8b0c5e8a65b4
/方法1:/
SELECT user.name,b.date,b.ps_num
FROM (
SELECT p.user_id,p.date,
(
SELECT SUM(b.number)
FROM passing_number AS b
WHERE b.date<=p.date AND p.user_id=b.user_id
) AS ps_num
FROM passing_number AS p
) AS b INNER JOIN user
ON b.user_id=user.id
ORDER BY b.date,user.name
/*方法2:
SELECT user.name,b.date,b.ps_num
FROM (
SELECT p.user_id,p.date,CASE
WHEN p.user_id IN (
SELECT a.user_id
FROM passing_number AS a
WHERE a.date<=p.date
) THEN (
SELECT SUM(b.number)
FROM passing_number AS b
WHERE b.date<=p.date AND p.user_id=b.user_id
)
ELSE p.number
END AS ps_num
FROM passing_number AS p
) AS b INNER JOIN user
ON b.user_id=user.id
ORDER BY b.date,user.name
*/