题解 | #牛客每个人最近的登录日期(六)#

牛客每个人最近的登录日期(六)

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
*/

全部评论

相关推荐

叁六玖:你看,最后不是让你加油,就是鼓励你,还祝福你求职顺利。
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务