2019.12.1 Leetcode SQL

SELECT a1.player_id,a1.event_date,sum(a2.games_played) as games_played_so_far
From Activity as a1, Activity as a2 
Where a1.player_id = a2.player_id and a1.event_date >= a2.event_date
Group by a1.player_id,a1.event_date

534 游戏玩法
为啥这么简单的表连接我都能做这么久。。。。
因为对表连接不熟悉吧

Select round(SUM(IF(s.event_date - s.first_login = 1,1,0)) / count(distinct s.player_id),2) as fraction
From (
Select a1.player_id,a1.event_date,a2.first_login
From Activity a1 
join( 
    SELECT a1.player_id,MIN(a1.event_date) first_login
    From Activity a1
    Group by player_id) a2
On a1.player_id = a2.player_id) as s

550 游戏玩法
一开始试 count(if()) 一直通不过，用 sum(if()) 就过了
神奇
那什么时候用COUNT(IF())

Select s2.Name
From (
    Select Name,count(*) as num
    From (
        Select e1.Id,e1.Name,e1.Department,e1.ManagerId
        From Employee e1, Employee e2
        Where e1.Id = e2.ManagerId
    ) s
    Group by s.Name
) s2
Where s2.num >=5

两次子查询，晕了晕了
效率很低，第二次刷题的时候改进吧

#总结一下求最大值的两种方法

# 第一种
HAVING MAX(COLUMN)

#第二种
ORDER BY COLUMN DESC LIMIT 1

# Write your MySQL query statement below
Select s.Name AS Name
From (
    Select c.id,c.Name,count(*) as num
    FROM Candidate c
    Join Vote v
    On c.id = v.candidateId
    Group by c.Name 
) s
ORDER BY s.num DESC  LIMIT 1

574 当选者
啦啦啦这个效率好高呀~击败98.68%用户
小开心

SELECT 
    question_id AS 'survey_log'
FROM
    survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id) / count(IF(action = 'show', 1, 0)) DESC
LIMIT 1;

578 查询回答率最高的问题
啊！我知道了count（if（）） 和 sum(if()) 的区别了
明显COUNT(IF()) 的效率更高哦~