SQL 查询同时获取 group by 和不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3473239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to get group by and distinct values at the same time
提问by wsb3383
I'm having trouble trying to define the SQL query for this table:
我在尝试为此表定义 SQL 查询时遇到问题：
There's a table of patients and their weight readings recorded on visits with the following columns:
有一个患者表格及其在访问时记录的体重读数，其中包含以下列：
- patient ID
- weight reading
- visit ID (one per visit)
- 病人编号
- 体重读数
- 访问 ID（每次访问一个）
In other words, if in two records two visit IDs are the same, then two weight readings have been taken on that same visit date.
换句话说，如果两个记录中的两个访问 ID 相同，则在同一个访问日期获取了两个体重读数。
I have this query to "get all patients with at least two weight readings above 150":
我有这个查询来“让所有患者的体重读数至少超过 150”：
select patient_id
from patients
where weight_val > 50
group by patient_id
having count(*) >= 2
Here's my problem: What if I want to modify this query so that I can query the following:
这是我的问题：如果我想修改此查询以便我可以查询以下内容，该怎么办：
- "get all patients with at least two weight readings above 150 on different visits"
- "get all patients with at least two weight readings above 150 on the same visit"
- “让所有患者在不同的访问中至少有两个体重读数高于 150”
- “让所有患者在同一次就诊中至少有两个体重读数高于 150”
Is it possible to do it without removing the "group by" statement? if not, what is your recommended approach? I'm also open to adding a date column instead of visit ID if it makes it easier (i'm using Oracle).
是否可以在不删除“group by”语句的情况下执行此操作？如果没有，您推荐的方法是什么？如果方便的话，我也愿意添加日期列而不是访问 ID（我使用的是 Oracle）。
回答by OMG Ponies
Patients with at least two weight readings above 150 on differentvisits
不同就诊中至少有两个体重读数高于 150 的患者
Use:
用：
SELECT p.patient_id
FROM PATIENTS p
WHERE p.weight_val > 150
GROUP BY p.patient_id
HAVING COUNT(DISTINCT p.visit_id) >= 2
Patients with at least two weight readings above 150 on the samevisit
患者具有至少两个重量读数高于150上相同的访问
Use:
用：
SELECT DISTINCT p.patient_id
FROM PATIENTS p
WHERE p.weight_val > 150
GROUP BY p.patient_id, p.visit_id
HAVING COUNT(*) >= 2
回答by tinychen
try like this:
试试这样：
1.
1.
select patient_id
from patients
where weight_val > 150
group by patient_id
having count(*) >= 2 and count(*) = count(distinct visit_id);
2.
2.
select patient_id
from patients
where weight_val > 150
group by patient_id
having count(*) >= 2 and count(distinct visit_id) = 1;
