以下解决方案的反例?

Question

我最近在以下提示下完成了一个编码挑战：

黑客的总分是他们所有人的最高分之和 的挑战。编写查询以打印hacker_id、名称和 黑客的总分按降序排列。如果更多 超过一名黑客获得相同的总分，然后按以下方式对结果进行排序 升序hacker_id。排除所有总分为 0 的黑客 您的结果。

下表包含比赛数据：

黑客：hacker_id是黑客的ID，名字是名字 的黑客。

提交：submission_id是提交的 ID，hacker_id 是提交内容的黑客的 ID，challenge_id是 提交内容所属挑战的 ID，分数为 提交的分数。

我的解决方案通过了测试用例，但我花了多次迭代才到达那里。

我感觉可能存在使用我的解决方案无法通过的边缘情况/特定输入，但我无法弄清楚。

有什么猜测或反例吗？

我的解决方案：

Select ID, Name, sum(maxscore) as tot From
(Select ID, Name, chal, Max(score) as maxscore From
(Select Submissions.hacker_id as ID, Hackers.name as Name, Submissions.score as score, Submissions.challenge_id as chal
From Submissions
Inner Join Hackers on Submissions.hacker_id = Hackers.hacker_id
Where Submissions.score <> 0)
Group by chal, ID, Name)
Group by ID, Name Order by tot desc, ID asc;

Answer 1

Select a.hacker_id,a.name,x.sum_max
from Hackers a
inner join 
(SELECT hacker_id,sum(max_score) as sum_max from (
Select hacker_id,challenge_id,max(Score) as max_score
from submissions
group by hacker_id,challenge_id)b 
group by hacker_id
having sum_max>0)x on a.hacker_id=x.hacker_id
order by sum_max desc,hacker_id

Answer 2

这是我的

select T.hacker_id, T.name , sum(T.max_score) sum_score from (
Select h.hacker_id, name , challenge_id, max(score) max_score from Hackers h 
inner join submissions s on h.hacker_id = s.hacker_id
group by h.hacker_id, name , challenge_id) as T
group by T.hacker_id, T.name
having sum(T.max_score) > 0
order by sum_score desc , T.hacker_id

Answer 3

MySQL的这段代码成功运行了测试用例。

select m.id, h.name, m.total from 
(select x.hacker_id id, sum(x.max_score) total from
(select h.hacker_id, s.challenge_id, max(s.score) as max_score from submissions as s 
join hackers as h on h.hacker_id = s.hacker_id
group by h.hacker_id, s.challenge_id) as x
group by x.hacker_id
having total <> 0)as m, 
hackers as h
where m.id = h.hacker_id
order by m.total desc, m.id;

在此处输入图像描述

Answer 4

First try this query than vote:
select h.hacker_id,h.name,sum(s.score) as scores from Hackers h 
inner join 
(select hacker_id,max(score) as score from Submissions group by hacker_id,challenge_id) s 
on h.hacker_id=s.hacker_id 
group by h.hacker_id,h.name having scores>0 
order by scores desc,h.hacker_id;

Answer 5

SELECT A, B, sum(C) FROM
(SELECT h.hacker_id AS A, h.name AS B, max(s.score)AS C FROM
Hackers h
JOIN Submissions s ON s.hacker_id=h.hacker_id
GROUP BY h.hacker_id,h.name, s.challenge_id) Sub
GROUP BY A, B
HAVING sum(C)>0
ORDER BY sum(C) DESC, A ASC;

Answer 6

我就是这样做的：

WITH NOW AS (
SELECT CHALLENGE_ID AS CI, HACKER_ID AS HI, MAX(SCORE) AS TSCORE FROM SUBMISSIONS
WHERE HACKER_ID = HACKER_ID GROUP BY CHALLENGE_ID, HACKER_ID 
)
SELECT C.HI, H.NAME, SUM(C.TSCORE) AS [TOTAL]  FROM NOW AS C, HACKERS AS H 
WHERE C.HI = H.HACKER_ID AND C.TSCORE > 0
GROUP BY C.HI, H.NAME ORDER BY TOTAL DESC, C.HI

Answer 7

SELECT 
res.hacker_id as id, 
res.name as name,
SUM(res.max_score) as score
from (
SELECT s.challenge_id as challenge_id, h.hacker_id as hacker_id ,h.name as name,Max(score) as max_score
FROM Hackers as h JOIN Submissions as s
ON h.hacker_id = s.hacker_id
GROUP BY challenge_id ,hacker_id,name 
having max_score <> 0
) as res
GROUP BY hacker_id,name order by score desc,id asc;

Answer 8

试试这个 mysql

SELECT t.hacker_id,t.name,sum(t.score) as score FROM
(select s.hacker_id, h.name, s.challenge_id, max(s.score) as score
FROM submissions s join Hackers h ON s.hacker_id = h.hacker_id
GROUP BY hacker_id, h.name, challenge_id ) as t
where score != 0 group by t.hacker_id,t.name order by sum(t.score) desc,t.hacker_id

Answer 9

oracle solution：

with temp as (
select tp.hacker_id,h.name, sum(tp.score) s from (
select hacker_id ,submission_id, challenge_id ,score, rank() over (partition by 
hacker_id, challenge_id order by score desc,submission_id  )as rank from 
submissions)tp
join hackers h on h.hacker_id=tp.hacker_id
where tp.rank=1 
group by tp.hacker_id,h.name
order by s desc,tp.hacker_id)
select hacker_id,name,s from temp where s <> 0 order by s desc,hacker_id ;

Answer 10

我试图修改艾曼的代码(MS SQL的解决方案(

SELECT TB1.HACKER_ID, TB1.NAME, SUM(TB2.MSCORE) 
FROM HACKERS TB1  /* HERE TB1 represents hackers table */ 
JOIN (SELECT hacker_id, challenge_id, MAX(Score) as MSCORE
FROM Submissions 
GROUP BY hacker_id, challenge_id) TB2 
ON TB1.HACKER_ID = TB2.HACKER_ID /*TB2 represents submission table*/
GROUP BY TB1.HACKER_ID, TB1.NAME HAVING SUM(TB2.MSCORE) > 0 
ORDER BY SUM(TB2.MSCORE) DESC, TB1.HACKER_ID ASC

第一步是获取最高分并按hacker_id和challange_id分组。然后绕回第一行以更好地理解它，其中最高分相加，并按 Id 和名称进一步分组并连接。

Answer 11

由 SQL Server 中的 CTE 执行此操作。易于理解。

WITH cte_tbl (Hack_id, name, score, challenge_id) AS (
SELECT
Hackers.hacker_id, 
Hackers.name, 
MAX(Submissions.score), 
Submissions.challenge_id FROM Hackers
INNER JOIN Submissions ON Hackers.hacker_id = Submissions.hacker_id
WHERE Submissions.score > 0 
GROUP BY Hackers.hacker_id, Hackers.name, Submissions.challenge_id
)
SELECT Hack_id, name, SUM(score) FROM cte_tbl GROUP BY Hack_id, name ORDER BY SUM(score) DESC, Hack_id ASC

Answer 12

select 
hacker_id, 
name, 
SUM(max_score) AS total_score 
FROM 
(
select 
hacker_id, 
name, 
challenge_id, 
MAX(score) AS max_score 
from 
hackers 
inner join submissions using (hacker_id) 
group by 
name, 
hacker_id, 
challenge_id
) 
WHERE 
hacker_id NOT IN (
select 
hacker_id 
from 
(
select 
hacker_id, 
SUM(x) 
FROM 
(
select 
challenge_id, 
hacker_id, 
MAX(score) AS x 
from 
submissions 
group by 
challenge_id, 
hacker_id
) 
group by 
hacker_id 
HAVING 
SUM(x) = 0
) 
group by 
hacker_id
) 
group by 
hacker_id, 
name 
order by 
total_score DESC, 
hacker_id;

Answer 13

我试过这个并为我工作

select H.Hacker_id, h.Name, sum(x.Score) 
from hackers h 
inner join (   
select hacker_id, challenge_id, max(Score) as Score 
from Submissions 
group by hacker_id, challenge_id
) x 
on H.Hacker_id = x.hacker_Id
group by H.Hacker_Id, h.Name
having sum(x.score) > 0
order by 3 desc, h.hacker_id