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:

1. 让所有患者在不同的访问中至少有两个体重读数大于150的患者"
2. 让所有患者的两次访问中至少两个体重读数大于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屋！