「SQL」错题本

---

sqlzoo

1. List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT game.mdate,team1,
sum(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,team2,
sum(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 
FROM game LEFT JOIN goal  ON(game.id = matchid) 
GROUP BY mdate, matchid, team1, team2

使用左连接
goal表是进球的记录，如果双方都没有进球，则在表中没有体现，所以需要左连接显示所有的比赛记录

數據庫有三個表格
movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)
actor演員(id編號, name姓名)
casting角色(movieid電影編號, actorid演員編號, ord角色次序)
角色次序代表第1主角是1, 第2主角是2…如此類推.

2. 列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。

select distinct(title),name
  from actor 
  join casting on actor.id=actorid
  join movie on movie.id = movieid 
where movieid in 
  (select movieid
  from actor 
  join casting on actor.id=actorid
  join movie on movie.id = movieid
  where name='Julie Andrews')
  and ord=1

演员于1934和1980两次参演同一部电影，title的筛选不唯一，自查询中应使用movieid

3. 列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

SELECT title,COUNT(actorid) AS count
FROM casting 
JOIN actor ON actor.id=casting.actorid
JOIN movie ON movie.id=casting.movieid
WHERE yr=1978 
GROUP BY title
ORDER BY count DESC

并没有搞明白，为什么一定要连接actor表，但不加这张表就是结果有偏差 ？？

牛客

1. 删除emp_no重复的记录，只保留最小的id对应的记录。

删除语句报错
DELETE FROM titles_test WHERE id NOT IN
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)
报错：ERROR 1093 (HY000): You can’t specify target table ‘titles_test’ for update in FROM clause

思路没有问题
MySQL的UPDATE或DELETE中子查询不能为同一张表
解决办法：将结果再次select
需要注意的是：如果不给子查询添加别名，会报错：ERROR 1248 (42000): Every derived table must have its own alias

 DELETE FROM titles_test 
WHERE id NOT IN (
    SELECT * 
    FROM(
        SELECT MIN(id) 
        FROM titles_test 
        GROUP BY emp_no
) AS a);

2. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。

update titles_test SET
emp_no = replace(emp_no, 10001, 10005)
where id = 5;

replace into titles_test values
(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');

第一次知道replace into这种用法，在有冲突时先删除原有记录再添加新的，缺失字段null填充