如何编写这个SQL查询?

I have the following tables:

我有以下表格:

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

I want to write a query that takes an input of a disease id and returns one row for each person in the database with a column for the gender, a column for whether or not they have ever had the disease, and a column for each drug which specifies if they took the drug before contracting the disease. I.E. true would mean drug_start_date < disease_start_date. False would mean drug_start_date>disease_start_date or the person never took that particular drug.

我想写一个查询,输入id的疾病并返回数据库中的一行,每个人对性别列,列是否有过这种疾病,并为每个药物它指定一列如果他们把毒品染上疾病。也就是说，true意味着drug_start_date <迪萨斯e_start_date。false表示drug_start_date> 疾病或者从未服用过这种药物的人。

We currently pull all of the data from the database and use Java to create a 2D array with all of these values. We are investigating moving this logic into the database. Is it possible to create a query that will return the result set as I want it or would I have to create a stored procedure? We are using Postgres, but I assume an SQL answer for another database will easily translate to Postgres.

我们现在从数据库中提取所有数据，并使用Java创建一个包含所有这些值的2D数组。我们正在调查将这个逻辑移到数据库中。是否可以创建一个查询来返回我想要的结果集，还是必须创建一个存储过程?我们使用的是Postgres，但我假设另一个数据库的SQL答案很容易转化为Postgres。

2 个解决方案

#1

Based on the info provided:

根据提供的信息:

   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

..but there's going to be a lot of rows that will look duplicate except for the drug_id column.

. .但是除了drug_id列之外，还有很多行看起来是重复的。

2 个解决方案

#1

更多相关文章

随机推荐