sql - 使用子查询拒绝某些记录 - MS SQL Server 2008

Question

我有一个查询，我想在其中返回特定的结果，因为已经发生了某些事情，并拒绝发生了某些事情的其他结果，并进一步拒绝了未发生某些事情的其他结果。

这是当前示例输出:

VISIT ID | MRN | ORD NUM | ORD STS | ORD DESC
123456   | 123 | 987654  | ACTIVE  | URINE TEST
123456   | 123 | 987654  | IN PROG | URINE TEST
123456   | 123 | 987654  | COMPLETE| URINE TEST
123456   | 123 | 987321  | ACTIVE  | INSERT FOLEY
123456   | 123 | 987321  | IN PROG | INSERT FOLEY
123456   | 123 | 987321  | COMPLETE| INSERT FOLEY
124578   | 321 | 654321  | ACTIVE  | URINE TEST
124578   | 321 | 654321  | IN PROG | URINE TEST
124578   | 321 | 654321  | COMPLETE| URINE TEST
END OF REPORT

我想要的输出如下:

VISIT ID | MRN | ORD NUM | ORD STS | ORD DESC
123456   | 123 | 987654  | ACTIVE  | URINE TEST
123456   | 123 | 987654  | IN PROG | URINE TEST
123456   | 123 | 987654  | COMPLETE| URINE TEST
123456   | 123 | 987321  | ACTIVE  | INSERT FOLEY
123456   | 123 | 987321  | IN PROG | INSERT FOLEY
123456   | 123 | 987321  | COMPLETE| INSERT FOLEY
END OF REPORT

我只想查看有尿检的访问 ID，如果他们也有 INSERT FOLEY 订单或/和 REMOVE FOLEY 订单。所以如果我进来只做了尿检，那么我就不应该出现在报告上。

这是我的查询:

-- FOLEY CATHETER ORDERS

-- VARIABLE DECLARATION AND INITIALIZATION
DECLARE @SD DATETIME
DECLARE @ED DATETIME
SET @SD = '2013-06-01';
SET @ED = '2013-06-30';

-- COLUMN SELECTION
SELECT PV.PtNo_Num AS 'VISIT ID'
, PV.Med_Rec_No AS 'MRN'
, PV.vst_start_dtime AS 'ADMIT'
, PV.vst_end_dtime AS 'DISC'
, PV.Days_Stay AS 'LOS'
, PV.pt_type AS 'PT TYPE'
, PV.hosp_svc AS 'HOSP SVC'
, SO.ord_no AS 'ORDER NUMBER'
--, SO.ent_dtime AS 'ORDER ENTRY TIME'
--, DATEDIFF(HOUR,PV.vst_start_dtime,SO.ent_dtime) AS 'ADM TO ENTRY HOURS'
, CASE
    WHEN SO.svc_desc = 'INSERT FOLEY CATHETER' THEN 'INSERT FOLEY'
    WHEN SO.svc_desc = 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE' THEN 'INSERT FOLEY'
    WHEN SO.svc_desc = 'REMOVE INDWELLING URINARY CATHETER' THEN 'REMOVE FOLEY'
    ELSE SO.svc_desc
  END AS 'ORD DESC'
, CASE
    WHEN OSM.ord_sts = 'ACTIVE' THEN '1 - ACTIVE'
    WHEN OSM.ord_sts = 'IN PROGRESS' THEN '2 - IN PROGRESS'
    WHEN OSM.ord_sts = 'COMPLETE' THEN '3 - COMPLETE'
    WHEN OSM.ord_sts = 'CANCEL' THEN '4 - CANCEL'
    WHEN OSM.ord_sts = 'DISCONTINUE' THEN '5 - DISCONTINUE'
    WHEN OSM.ord_sts = 'SUSPEND' THEN '6 - SUSPEND'
  END AS 'ORDER STATUS'
, SOS.prcs_dtime AS 'ORDER STATUS TIME'
, DATEDIFF(DAY,PV.vst_start_dtime,SOS.prcs_dtime) AS 'ADM TO ORD STS IN DAYS'

-- DB(S) USED
FROM smsdss.BMH_PLM_PtAcct_V PV
JOIN smsmir.sr_ord SO
ON PV.PtNo_Num = SO.episode_no
JOIN smsmir.sr_ord_sts_hist SOS
ON SO.ord_no = SOS.ord_no
JOIN smsmir.ord_sts_modf_mstr OSM
ON SOS.hist_sts = OSM.ord_sts_modf_cd

-- FILTER(S)
WHERE PV.Adm_Date BETWEEN @SD AND @ED
AND (SO.svc_desc LIKE 'INSERT FOLEY CATHETER'
    OR SO.svc_desc LIKE 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE'
    OR SO.svc_desc LIKE 'REMOVE INDWELLING URINARY CATHETER'
    OR SO.svc_desc LIKE 'URIN%'
    )
-- TRYING TO KICK OUT PATIENTS WHO DID NOT GET A FOLEY CATHETER ORDER
-- OF ANY TYPE
--AND PV.PtNo_Num NOT IN (
--    SELECT SO.ord_no

--   FROM smsdss.BMH_PLM_PtAcct_V PV
--  JOIN smsmir.sr_ord SO
--  ON PV.PtNo_Num = SO.episode_no
--  JOIN smsmir.sr_ord_sts_hist SOS
--  ON SO.ord_no = SOS.ord_no
--  JOIN smsmir.ord_sts_modf_mstr OSM
--  ON SOS.hist_sts = OSM.ord_sts_modf_cd

--  WHERE (SO.svc_desc != 'INSERT FOELY CATHETER'
--        OR SO.svc_desc != 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE'
--        OR SO.svc_desc != 'REMOVE INDWELLING URINARY CATHETER'
--        )
--)
-- KICKS OUT DISCONTINUED ORDERS
AND SO.ord_no NOT IN (
    SELECT SO.ord_no

    FROM smsdss.BMH_PLM_PtAcct_V PV
    JOIN smsmir.sr_ord SO
    ON PV.PtNo_Num = SO.episode_no
    JOIN smsmir.sr_ord_sts_hist SOS
    ON SO.ord_no = SOS.ord_no
    JOIN smsmir.ord_sts_modf_mstr OSM
    ON SOS.hist_sts = OSM.ord_sts_modf_cd

    WHERE OSM.ord_sts = 'DISCONTINUE'
    AND (SO.svc_desc LIKE 'INSERT FOLEY CATHETER'
    OR SO.svc_desc LIKE 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE'
    OR SO.svc_desc LIKE 'REMOVE INDWELLING URINARY CATHETER'
    OR SO.svc_desc LIKE 'URINE%'
    )
)

我尝试使用其余部分的代码是完全注释掉的部分。查询中的注释部分需要相当长的时间才能运行，这意味着 t>5 minutes 没有返回结果。如果没有该部分，查询将返回 t<2 minutes 中的所有结果

提前致谢

Answer 1

一种方法是使用窗口聚合函数(例如 MAX() OVER ...)来确定 VISIT ID 行组是否包含具有特定 的行>ORD DESC 值。

基本上，模式是这样的:

SELECT
  ...
  MAX(CASE WHEN [ORD DESC] = 'URINE TEST' THEN 1 ELSE 0 END)
    OVER (PARTITION BY [VISIT ID]) AS HasUrineTest,
  MAX(CASE WHEN [ORD DESC] IN ('INSERT FOLEY', 'REMOVE FOLEY') THEN 1 ELSE 0 END)
    OVER (PARTITION BY [VISIT ID]) AS HasInsertRemoveFoley
FROM ...

这将为您标记每一行中的列，每次访问具有相同的值。然后，您将使用这些标志来过滤行。不过，您需要将整个选择放入子查询中，并将其用作派生表，以便能够对标志列进行过滤:

WITH flagged AS (
  SELECT
    ...
    MAX(CASE WHEN [ORD DESC] = 'URINE TEST' THEN 1 ELSE 0 END)
      OVER (PARTITION BY [VISIT ID]) AS HasUrineTest,
    MAX(CASE WHEN [ORD DESC] IN ('INSERT FOLEY', 'REMOVE FOLEY') THEN 1 ELSE 0 END)
      OVER (PARTITION BY [VISIT ID]) AS HasInsertRemoveFoley
  FROM  ...  -- all your joins
  WHERE ...  -- and filters
)
SELECT ...  -- repeat all the columns except the Has* flags
FROM flagged
WHERE HasUrineTest = 1
  AND HasInsertRemoveFoley = 1
;

您可能已经注意到我在窗口函数中使用了输出别名而不是基础名称或表达式。在实际实现中，[VISIT ID] 可能需要替换为 PV.PtNo_Num。对于 [ORD DESC]，您可以将 CROSS APPLY 子句引入您的(子)查询并将 [ORD DESC] 计算移到那里而不是现在计算的位置，而是这样的:

WITH flagged AS (
  SELECT
     ...
     x.[ORD DESC],  -- instead of the CASE; the CASE is now in CROSS APPLY
     ...
  FROM ...  -- all your joins
  CROSS APPLY (
    SELECT
      CASE
        WHEN SO.svc_desc = 'INSERT FOLEY CATHETER' THEN 'INSERT FOLEY'
        WHEN SO.svc_desc = 'INSERT INDWELLING URINARY CATHETER TO GRAVITY DRAINAGE' THEN 'INSERT FOLEY'
        WHEN SO.svc_desc = 'REMOVE INDWELLING URINARY CATHETER' THEN 'REMOVE FOLEY'
        ELSE SO.svc_desc
      END AS [ORD DESC]
   ) x
)
SELECT ...
;