SQL查询可同时获取分组依据和不同的值 [英] SQL query to get group by and distinct values at the same time
问题描述
尝试定义此表的SQL查询时遇到问题:
I'm having trouble trying to define the SQL query for this table:
有一张患者表格,其访问时记录的体重读数包括以下几列:
There's a table of patients and their weight readings recorded on visits with the following columns:
- 患者编号
- 体重读数
- 访问ID(每次访问一个)
换句话说,如果两个记录中的两个访问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.
我有这个查询来获取至少两个体重读数高于150的所有患者":
I have this query to "get all patients with at least two weight readings above 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:
- 让所有患者在不同的访问中至少有两个体重读数大于150的患者"
- 让所有患者的两次访问中至少两个体重读数大于150"
是否可以在不删除"group by"语句的情况下做到这一点?如果没有,您推荐的方法是什么?如果愿意,我也愿意添加日期列而不是访问ID(我正在使用Oracle).
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).
推荐答案
患者在不同次访问中的体重读数至少超过150的两个患者
使用:
Patients with at least two weight readings above 150 on different visits
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
在同一次访问中至少有两个体重读数高于150的患者
使用:
Patients with at least two weight readings above 150 on the same visit
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
这篇关于SQL查询可同时获取分组依据和不同的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!