数据库基础习题10-13（含解法）

以MySQL为主，题目来源 https://www.hackerrank.com/domains/sql
本章以join的用法为主，可以先了解一下join的基本用法

10、查询在挑战中获得多个（1个以上）的满分的黑客的hacker_id和name。按照获得满分的挑战数降序排列。如果有多个黑客的满分挑战数相同，那么请按hacker_id升序对它们进行排序。（Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.）数据库如下：

Hacker:

Difficulty:

Challenges:

Submissions

解：（1）四个表的join顺序可以改变，但是on后面的连接方式非常重要，需要非常谨慎处理。
（2）注意select的对象受group by的限制
（3）注意having和where的区别，having只能对group by之后的每个整体进行筛选，不能筛选整体里的对象。
（4）group by x, y 的意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里

select hackers.hacker_id,hackers.name
from submissions 
join challenges on challenges.challenge_id = submissions.challenge_id
join difficulty on challenges.difficulty_level = difficulty.difficulty_level
join hackers on hackers.hacker_id = submissions.hacker_id
where submissions.score = difficulty.score 
group by hackers.hacker_id,hackers.name
having count(submissions.hacker_id)>1
order by count(submissions.hacker_id) desc, submissions.hacker_id asc;

11、（Ollivander’s Inventory）有两张表Wands和Wands_Property，其中Wands包含ID，code，coins_needed和power，Wands_Property包含code，age，is_evil。在age和power相同的情况下，挑选最小的coins_needed。查询对应的ID，age，coins_needed和power。
（Harry Potter and his friends are at Ollivander’s with Ron, finally replacing Charlie’s old broken wand.
Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron’s interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.）

wands：

Wands_Property：

解：

select w.id,p.age,w.coins_needed,w.power
from wands w join wands_property p on w.code=p.code
where p.is_evil!=1 and w.coins_needed = (
	select min(wands.coins_needed)
	from wands join wands_property on wands.code=wands_property.code
	where wands_property.age=p.age and wands.power=w.power
)
order by w.power desc,p.age desc;

倒数第二行子查询中的where不能换成 “group by wands_property.age,wands.power”，因为这样的查询过程没有对外部的表产生影响，相当于只得到单纯的数字，那么第一个where处的 “ w.coins_needed = ” 就不能用等号，要改成in，并且同一个w.coins_needed的数字对应多个ID，导致结果错误。但如果这里用where，则前面可以用 “=”，因为用where一次只会得一个值，并且对外部的表产生了作用。

12、（Challenges）Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Hackers:

Challenges:

解：（1）可以选择在hackers中新建一列然后再筛选，update的用法参考update和alter
（2）having的执行顺序确实在select之前，但是having可以调用select中的别名。（原因我也不知道，百度说法莫衷一是）
（3）注意temp1的命名方式，从原表中提取某些信息组成新表

select c.hacker_id, h.name ,count(c.hacker_id) as c_count
from Hackers as h
    inner join Challenges as c on c.hacker_id = h.hacker_id
group by c.hacker_id, h.name
having 
    c_count = 
        (SELECT MAX(temp1.cnt)
        from (SELECT COUNT(hacker_id) as cnt
             from Challenges
             group by hacker_id
             order by hacker_id) temp1)
    or c_count in 
        (select t.cnt
         from (select count(*) as cnt 
               from challenges
               group by hacker_id) t
         group by t.cnt
         having count(t.cnt) = 1)
order by c_count DESC, c.hacker_id

13、（Contest Leaderboard）The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score 0 of from your result.

Hackers:

Submissions:

解：如下代码查询错误，因为where查出来的结果有重复值，无法去除：

select s.hacker_id,h.name,sum(s.score)
from hackers h join submissions s on h.hacker_id=s.hacker_id
where s.score=(
    select max(score)
    from submissions
    where hacker_id=s.hacker_id and challenge_id=s.challenge_id
)
group by s.hacker_id,h.name
having sum(s.score)>0
order by sum(s.score) desc,s.hacker_id

正确解法：

select h.hacker_id, name, sum(score) as total_score
from hackers h join (
    select hacker_id, max(score) as score 
    from submissions 
    group by challenge_id, hacker_id) max_score
on h.hacker_id=max_score.hacker_id
group by h.hacker_id, name
having total_score > 0
order by total_score desc, h.hacker_id;