英文:
Select IDs with two or more entries greater than amount
问题
以下是已经翻译好的内容：
我是MySQL新手，遇到了一个作业问题：
1. 获取每个因多次发生40美元以上的罚款而受到惩罚的球员的姓名和缩写。**提示：使用具有聚合功能的子查询**
我可以让MySQL显示我超过40美元的金额，但似乎无法只显示有两次或两次以上罚款的球员。我已经观看了大量教程并阅读了在线论坛，经过两个小时，我最终举起了白旗，请求在这个问题上寻求帮助。
这是表格：
Paymentno, playerno, payment_date, amount
'1','6','1980-12-08','100.00'
'2','44','1981-05-05','75.00'
'3','27','1983-09-10','100.00'
'4','104','1984-12-08','50.00'
'5','44','1980-12-08','25.00'
'6','8','1980-12-08','25.00'
'7','44','1982-12-30','30.00'
'8','27','1984-11-12','75.00'
到目前为止，这是我的代码：
SELECT DISTINCT playerno
FROM penalties
WHERE amount > 40 IN (SELECT playerno
FROM penalties
GROUP BY playerno
HAVING COUNT(DISTINCT playerno) > 1);
它返回的结果如下：
Playerno
'6'
'44'
'27'
'104'
'8'
它应该只返回 playerno 27，我尝试了各种不同的变化。有人可以指导我吗？感谢您的帮助和指导！
希望这对您有帮助。如果您有其他问题，请随时提出。
英文:
I'm new to MySQL, and I'm struggling with a homework problem:
- Get the name and initials of each player who has incurred two or more penalties of more than $40. Hint: use subquery that has an aggregate
I can get MySQL to show me the amounts more than $40, but I can't seem to get it to only show the players that have two or more penalties. I've watched a ton of tutorials and read forums online, and after two hours, I'm finally waiving the white flag and asking for help on this one.
Here is the table:
Paymentno, playerno, payment_date, amount
'1','6','1980-12-08','100.00'
'2','44','1981-05-05','75.00'
'3','27','1983-09-10','100.00'
'4','104','1984-12-08','50.00'
'5','44','1980-12-08','25.00'
'6','8','1980-12-08','25.00'
'7','44','1982-12-30','30.00'
'8','27','1984-11-12','75.00'
Here is my code thus far:
SELECT DISTINCT playerno
FROM penalties
WHERE amount > 40 IN (SELECT playerno
FROM penalties
GROUP BY playerno
HAVING COUNT(DISTINCT playerno) > 1);
And here it was it returns:
Playerno
'6'
'44'
'27'
'104'
'8'
It should only return playerno 27, and I've tried all sorts of different variations. Can someone please point me in the right direction on this one? I appreciate your help and guidance!
答案1
得分: 1
我们可以在这里使用条件聚合：
SELECT playerno
FROM penalties
GROUP BY playerno
HAVING SUM(amount > 40) >= 2;
如果您必须使用子查询解决这个问题，我可以建议以下方法：
SELECT DISTINCT playerno
FROM penalties p1
WHERE amount > 40 AND
EXISTS (SELECT 1
FROM penalties p2
WHERE p2.playerno = p1.playerno AND
p2.paymentno <> p1.paymentno AND
p2.amount > 40);
第二个查询找到所有具有大于40的处罚的球员，同时我们还可以从不同场合的第二条记录中找到处罚也大于40。
英文:
We can use conditional aggregation here:
<!-- language: sql -->
SELECT playerno
FROM penalties
GROUP BY playerno
HAVING SUM(amount > 40) >= 2;
If you must solve this using a subquery, I can suggest the following:
<!-- language: sql -->
SELECT DISTINCT playerno
FROM penalties p1
WHERE amount > 40 AND
EXISTS (SELECT 1
FROM penalties p2
WHERE p2.playerno = p1.playerno AND
p2.paymentno <> p1.paymentno AND
p2.amount > 40);
The second query finds all players having a penalty greater than 40 for whom we can also find a second record from a different occasion where the penalty also happens to be more than 40.
答案2
得分: 0
以下是翻译好的部分：
在你提到的关于使用带有聚合函数的子查询中；
这是一个解决方案。
SELECT playerno
FROM penalties
WHERE playerno IN (
SELECT playerno
FROM penalties
WHERE amount > 40
GROUP BY playerno
HAVING COUNT(*) > 1
)
GROUP BY playerno;
我已经测试过它，这里是一个供你参考的SQLFIDDLE。
英文:
As you have mentioned about; use of a subquery with an aggregate;
Here is a solution.
SELECT playerno
FROM penalties
WHERE playerno IN (
SELECT playerno
FROM penalties
WHERE amount > 40
GROUP BY playerno
HAVING COUNT(*) > 1
)
group by playerno;
I have tested it and here is a SQLFIDDLE for your reference.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论