代码之家 › 专栏 › 技术社区 › Jay Askren

如何编写此SQL查询?

postgresql database sql

Jay Askren · 技术社区 · 14 年前

我有以下表格:

PERSON_T              DISEASE_T               DRUG_T
=========             ==========              ========
PERSON_ID             DISEASE_ID              DRUG_ID
GENDER                PERSON_ID               PERSON_ID
NAME                  DISEASE_START_DATE      DRUG_START_DATE
                      DISEASE_END_DATE        DRUG_END_DATE

我想编写一个查询,该查询接受疾病ID的输入,并为数据库中的每个人返回一行,其中包含一列用于显示性别,一列用于显示他们是否患有该疾病,以及一列用于显示他们是否在感染该疾病之前服用了该药。也就是说,真的意味着药物开始日期和疾病开始日期。错误的意思是药物开始日期,疾病开始日期,或者这个人从未服用过这种特定的药物。

目前,我们从数据库中提取所有数据,并使用Java创建具有所有这些值的2D数组。我们正在研究将此逻辑移入数据库。是否可以创建一个将按需要返回结果集的查询,或者必须创建一个存储过程?我们使用的是Postgres,但我假设另一个数据库的SQL答案很容易转换为Postgres。

2 回复 | 直到 14 年前

OMG Ponies 14 年前

根据提供的信息:

   SELECT p.name,
          p.gender,
          CASE WHEN d.disease_id IS NULL THEN 'N' ELSE 'Y' END AS had_disease,
          dt.drug_id
     FROM PERSON p
LEFT JOIN DISEASE d ON d.person_id = p.person_id
                   AND d.disease_id = ?
LEFT JOIN DRUG_T dt ON dt.person_id = p.person_id
                   AND dt.drug_start_date < d.disease_start_date

…但是会有很多行看起来是重复的,除了 drug_id 列。

Adam Robinson 14 年前

你基本上是想创造一个 cross-tab 询问药物。虽然有很多OLAP工具可以完成这类工作(包括各种数据切片和切分),但在传统的SQL中完成这类工作并不容易(而且,一般来说,除了最简单的scenari之外,没有某种过程语法是不可能的操作系统)。

使用SQL执行此操作时,基本上有两个选项(更准确地说,您有一期权,以及另一个更复杂但更灵活的期权:

使用一系列 CASE 查询中的语句以生成代表每个单独药物的列。这需要 提前了解可变值列表(即药物)
使用过程性SQL语言(如T-SQL)动态构造一个查询,该查询使用上述case语句,但同时从数据本身获取该值列表。

这两个选项本质上做了相同的事情,您只是在第二个选项中为了灵活性而交换简单性和易维护性。

例如,使用选项1:

select
    p.NAME,
    p.GENDER,
    (case when d.DISEASE_ID is null then 0 else 1 end) as HAD_DISEASE,
    (case when sum(case when dr.DRUG_ID = 1 then 1 else 0 end) > 0 then 1 else 0 end) as TOOK_DRUG_1,
    (case when sum(case when dr.DRUG_ID = 2 then 1 else 0 end) > 0 then 1 else 0 end) as TOOK_DRUG_2,
    (case when sum(case when dr.DRUG_ID = 3 then 1 else 0 end) > 0 then 1 else 0 end) as TOOK_DRUG_3

from PERSON_T p

left join DISEASE_T d on d.PERSON_ID = p.PERSON_ID and d.DISEASE_ID = @DiseaseId
left join DRUG_T dr on dr.PERSON_ID = p.PERSON_ID and dr.DRUG_START_DATE < d.DISEASE_START_DATE

group by p.PERSON_ID, p.NAME, p.GENDER, d.DISEASE_ID

正如你所知道的,这会变得有点困难,因为你得到的只是一些潜在的价值。

另一种选择是动态构造这个查询。我不知道PostgreSQL以及它拥有的过程功能(如果有的话),但是整个过程是这样的:

收集潜力列表 DRUG_ID 值以及列的名称
准备三个字符串值:SQL前缀(第一个与药物相关的 案例 语句,SQL stuffix(最后一个与药物相关的 案例 语句),以及动态部分
结合药物构建动态部分 案例 基于先前检索的列表的语句
将它们组合成一个(希望有效)SQL语句并执行