数据库多表查询

0. 导入文件后，新增白龙马，小龙女，白骨精等游戏角色

1. 查询出每个人打怪最多的一天，并显示名字，时间，打怪数量。
   SELECT a.user_name,b.timestr,kills
   FROM player a
   JOIN player_kills b ON a.id = b.player_id
   WHERE (b.player_id,b.kills) IN (SELECT player_id,MAX(kills) FROM player_kills GROUP BY player_id);

2. 查询出同时具有变化和念经这两项技能的人。
   SELECT a.user_name,b.skill,c.skill
   FROM player a
   JOIN player_skills b ON a.id = b.player_id
   JOIN player_skills c ON c.player_id = b.player_id
   WHERE b.skill=‘念经’ AND c.skill=‘变化’ AND b.skill_level >0 AND c.skill_level>0;

3. 显示有同时具有"念经，变化，腾云"技能的人。
   SELECT a.user_name,b.skill,c.skill,d.skill
   FROM player a
   JOIN player_skills b ON a.id = b.player_id
   JOIN player_skills c ON c.player_id = b.player_id
   JOIN player_skills d ON d.player_id = b.player_id
   WHERE b.skill=‘念经’ AND c.skill=‘变化’ AND d.skill=‘腾云’ AND b.skill_level >0 AND c.skill_level>0 AND d.skill_level>0;

4. 具有4项技能里的两项以上的人。(念经, 变化, 腾云, 必杀技)
   SELECT a.user_name,b.skill,c.skill,d.skill,e.skill
   FROM player a
   LEFT JOIN player_skills b ON a.id = b.player_id AND b.skill=‘念经’ AND b.skill_level >0
   LEFT JOIN player_skills c ON a.id = c.player_id AND c.skill=‘变化’ AND c.skill_level >0
   LEFT JOIN player_skills d ON a.id = d.player_id AND c.skill=‘腾云’ AND d.skill_level >0
   LEFT JOIN player_skills e ON a.id = e.player_id AND e.skill=‘浮水’ AND e.skill_level >0
   WHERE (CASE WHEN b.skill IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN c.skill IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN d.skill IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN e.skill IS NOT NULL THEN 1 ELSE 0 END) >= 2;

5. 查询出每个人3级以上的技能(分组查询)。（人名、技能名、技能等级）
   SELECT a.user_name
   FROM player a
   JOIN player_skills b on a.id = b.player_id
   WHERE b.skill in (‘念经’,‘变化’,‘腾云’,‘浮水’) and b.skill_level>0
   GROUP BY a.user_name HAVING COUNT(*)>=2;