时间戳

首页 > 解决方案 > SQL select 语句返回一组事件在同一 ID 上多次发生的记录

sql - SQL select 语句返回一组事件在同一 ID 上多次发生的记录

问题描述

假设我有一张桌子：

CREATE TABLE births 
(
    childid INT,
    momid INT,
    eclampsia VARCHAR(1),
    preeclampsia VARCHAR(1),
    hypertension VARCHAR(1)
);

插入记录：

INSERT INTO BIRTHS (CHILDID, MOMID, ECLAMPSIA)
VALUES (654321, 123456, 'Y'),
       (654321, 123456, 'Y'),

INSERT INTO BIRTHS (CHILDID, MOMID, HYPERTENSION)
VALUES (987652, 465468, 'Y'),
       (987987, 465468, 'Y')

INSERT INTO BIRTHS (CHILDID, MOMID)
VALUES (687765, 465468)

INSERT INTO BIRTHS (CHILDID, MOMID, PREECLAMPSIA)
VALUES (649870, 846587, 'Y')

INSERT INTO BIRTHS (CHILDID, MOMID)
VALUES (787463, 846587);

我想返回所有生育过不止一个孩子并且在不止一次怀孕中出现这三种诊断之一的母亲的记录。

我的预期结果是：

 child    momid     eclampsia    preeclampsia    hypertension
 -------------------------------------------------------------
 654321   123456       Y
 431265   123456                     Y
 987652   465468                                     Y
 987987   465468                                     Y

我该怎么写这个？

我有一个草率的查询，不能完全满足我的要求。它在一定程度上起作用，但仍然给我记录，妈妈只对一次怀孕进行了诊断。

select distinct 
    a.*, b.eclampsia, b.preeclampsia, b.hypertension 
from 
    births a
join 
    births b on a.momid = b.momid
where 
    a.childid != b.childid 
    and a.eclampsia = 'y' 
    and (b.eclampsia = 'y' or b.preeclampsia = 'y' or b.hypertension = 'y') 
     or a.preeclampsia = 'y' 
    and (b.preeclampsia = 'y' or b.eclampsia = 'Y' or b.hypertension = 'y') 
     or a.hypertension = 'y' 
    and (b.hypertension = 'y' or b.eclampsia = 'y' or b.preeclampsia = 'y')
order by  
    mapersonid

标签： sqlsql-serverssms

解决方案

这是一种方法。它计算出生表中显示每个母亲的症状之一的记录，使用该计数 > 1 作为显示记录的条件，只要该记录还显示其中一个条件：

SELECT childid, momid, 
    COALESCE(eclampsia, '') AS eclampsia, 
    COALESCE(preeclampsia, '') AS preeclampsia, 
    COALESCE(hypertension, '') AS hypertension
FROM births b1
WHERE (SELECT COUNT(*) FROM births b2 WHERE b2.momid = b1.momid AND
      (ECLAMPSIA = 'Y' OR PREECLAMPSIA = 'Y' OR HYPERTENSION = 'Y')
       GROUP BY momid) > 1  AND
      (ECLAMPSIA = 'Y' OR PREECLAMPSIA = 'Y' OR HYPERTENSION = 'Y')

输出

 child    momid     eclampsia    preeclampsia    hypertension
 654321   123456       Y
 431265   123456                     Y
 987652   465468                                     Y
 987987   465468                                     Y

推荐阅读