문제링크 : [7 kyu]SQL with Street Fighter: Total Wins)
It’s time to assess which of the world’s greatest fighters are through to the 6 coveted places in the semi-finals of the Street Fighter World Fighting Championship. Every fight of the year has been recorded and each fighter’s wins and losses need to be added up.
Each row of the table fighters records, alongside the fighter’s name, whether they won (1) or lost (0), as well as the type of move that ended the bout.
winning_moves
However, due to new health and safety regulations, all ki blasts have been outlawed as a potential fire hazard. Any bout that ended with Hadoken, Shouoken or Kikoken should not be counted in the total wins and losses.
So, your job:
Return name, won, and lost columns displaying the name, total number of wins and total number of losses. Group by the fighter’s name. Do not count any wins or losses where the winning move was Hadoken, Shouoken or Kikoken. Order from most-wins to least Return the top 6. Don’t worry about ties.
SELECT F.name AS name, SUM(F.won) AS won, SUM(F.lost) AS lostFROM fighters F, winning_moves WMWHERE F.move_id = WM.id AND WM.move NOT IN ('Hadoken', 'Shouoken', 'Kikoken')GROUP BY F.name ORDER BY won DESCLIMIT 6;
SELECT name, sum(won) as won, sum(lost) as lost FROM fightersINNER JOIN winning_moves ON fighters.move_id=winning_moves.idWHERE NOT move IN ('Hadoken', 'Shouoken', 'Kikoken')GROUP BY nameORDER by won DESCLIMIT 6;