英文:
SQL Query matching same values in same column based on criteria
问题
我有一个MySQL表，如下所示。
| donorId | amount | year |
|---|---|---|
| 787 | 9.5 | 2022 |
| 567 | 7.9 | 2021 |
| 787 | 30 | 2022 |
| 456 | 2.5 | 2022 |
| 567 | 26 | 2022 |
| 456 | 26 | 2022 |
我需要找到在2022年至少进行了两次捐赠的所有捐赠者（787、456）。还有一个问题：我不能在查询中使用HAVING。该如何处理？
英文:
I have a table in MySQL as follows.
| donorId | amount | year |
|---|---|---|
| 787 | 9.5 | 2022 |
| 567 | 7.9 | 2021 |
| 787 | 30 | 2022 |
| 456 | 2.5 | 2022 |
| 567 | 26 | 2022 |
| 456 | 26 | 2022 |
I need to find all donors who made at least two constructive in 2022(787, 456). there is also an issue: I can't use HAVING in the query. How to do this?
答案1
得分: 1
这是一个示例，说明如何在查询中不使用子查询的情况下执行此操作：
declare
@year int = '2022'
select
t1.donorId
from yourtable t1
where t1.year = @year
group by
t1.donorId
having count(1) > 1
请注意，我已将原始查询中的子查询重写为仅使用一个查询，并使用HAVING子句来筛选出符合条件的行。
英文:
There is example how to do this without having in your query, using subqueries
declare
@year int = '2022'
select
x.donorId
from (
select
count(1) c,
t.donorId donorId
from yourtable t
where t.year = @year
group by
t.donorId
) x
where x.c > 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论