[SQL]511+512+534+550+569
whiky 人气:0
# 511. 游戏玩法分析 I
**solution**
```mysql
SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;
```
# 512. 游戏玩法分析 II
```mysql
SELECT player_id, device_id
FROM Activity
WHERE (player_id, event_date) IN (SELECT player_id, MIN(event_date)
FROM Activity
GROUP BY player_id);
```
**HAVING不行的原因**
> having子句执行在select 之后, 因此having中的字段必须在select子句中, event_date没有再select子句里,所以不行
# 534. 游戏玩法分析 III
```mysql
SELECT a1.player_id, a1.event_date, SUM(a2.games_played) AS games_played_so_far
FROM Activity a1, Activity a2
WHERE a1.player_id = a2.player_id
AND a1.event_date >= a2.event_date
GROUP BY a1.player_id, a1.event_date --这一定要有a1.event_date，否则Result会根据player_id自动合并
ORDER BY a1.player_id, a1.event_date;
```
**另一种方法：**
```mysql
SELECT player_id, event_date,
CASE WHEN @prev = player_id THEN @cnt := @cnt + games_played
WHEN @prev := player_id THEN @cnt := games_played
END 'games_played_so_far'
FROM (SELECT player_id, event_date, games_played
FROM activity
ORDER BY player_id, event_date) a,
(SELECT @cnt := 0, @prev := null) t;
```
# 550. 游戏玩法分析 IV
**方法一**
```mysql
SELECT ROUND(COUNT(DISTINCT player_id)/(SELECT COUNT(DISTINCT player_id)
FROM Activity), 2) AS fraction
FROM Activity
WHERE (player_id, event_date) IN(SELECT player_id, DATE(MIN(event_date)+1)
FROM Activity
GROUP BY player_id);
```
**方法二**
```mysql
SELECT ROUND(SUM(CASE WHEN DATEDIFF(a.event_date, b.first_date)=1 THEN 1 ELSE 0 END) / (SELECT COUNT(DISTINCT player_id)
FROM Activity), 2) AS fraction
FROM Activity a, --千万不要漏掉这个逗号！！
(SELECT player_id, MIN(event_date) AS first_date
FROM Activity
GROUP BY player_id) b
WHERE a.player_id = b.player_id;
```
# 569. 员工薪水中位数
```mysql
SELECT b.id,b.company,b.salary
-- 3. 连接结果
FROM (
-- 1. 按 company 分组排序，记为 `rk`
SELECT id,company,salary,
CASE @com WHEN company THEN @rk:=@rk+1 ELSE @rk:=1 END rk,
@com:=company
FROM employee,(SELECT @rk:=0, @com:='') a
ORDER BY company,salary) b
LEFT JOIN
(-- 2. 计算各 company 的记录数除以2，记为 `cnt`
SELECT company,COUNT(1)/2 cnt FROM employee GROUP BY company) c
ON b.company=c.company
-- 4. 找出符合中位数要求的记录
WHERE b.rk in (cnt+0.5,cnt+1,cnt);
```
加载全部内容