Oracle SQL 相关子查询(在 WHERE 语句中)没有结果

Question

您好，感谢您的阅读，

我正在尝试使用相关的子查询运行以下SQL查询，但它没有返回任何结果。 我使用子查询仅返回日期范围最低的结果。 单独查询和单独子查询都可以正常工作。

有什么想法吗？ 我是否错误地链接了相关的子查询？

注意：相关子查询位于查询的最后一行

谢谢威尔兹06jw

SELECT aaa."effective_time", 
       aaa."event_type_c", 
       "clarity_dep"."department_name", 
       "patient"."birth_date", 
       "patient"."pat_mrn_id", 
       "zc_ped_delivr_meth"."name", 
       "zc_ped_delivr_meth"."ped_delivr_meth_c", 
       aaa."department_id", 
       "clarity_dep"."department_id", 
       aaa."alt_event_type_c", 
       aaa."in_event_type_c" 
FROM   (("CLARITY"."clarity_adt" aaa 
         inner join "CLARITY"."clarity_dep" "CLARITY_DEP" 
                 ON aaa."department_id" = "clarity_dep"."department_id") 
        inner join "CLARITY"."patient" "PATIENT" 
                ON aaa."pat_id" = "patient"."pat_id") 
       inner join "CLARITY"."zc_ped_delivr_meth" "ZC_PED_DELIVR_METH" 
               ON "patient"."ped_delivr_meth_c" = 
                  "zc_ped_delivr_meth"."ped_delivr_meth_c" 
WHERE  ( "patient"."birth_date" >= To_date ('01-12-2012 00:00:00', 
                                            'DD-MM-YYYY HH24:MI:SS') 
         AND "patient"."birth_date" < To_date ('06-12-2012 00:00:00', 
                                      'DD-MM-YYYY HH24:MI:SS' 
                                      ) ) 
       AND ( aaa."department_id" = 236601 
              OR aaa."department_id" = 236703 
              OR aaa."department_id" = 236801 
              OR aaa."department_id" = 236901 
              OR aaa."department_id" = 237101 
              OR aaa."department_id" = 237201 ) 
       AND aaa."event_type_c" = 3 
       AND aaa."effective_time" = (SELECT Min(bbb."effective_time") 
                                   FROM   "clarity_adt" bbb 
                                   WHERE  aaa."pat_id" = bbb.pat_id) 

Answer 1

在不对整个语句进行梳妆的情况下，最简单的解决方案是使用 ROW_NUMBER 函数将语句包装在 select 中以获取最低日期。

SQL 语句 1

SELECT  *
FROM    (
          SELECT ROW_NUMBER() OVER (PARTITION BY pat_id ORDER BY effective_time) AS rn,
                 aaa."effective_time", 
                 aaa."event_type_c", 
                 "clarity_dep"."department_name", 
                 "patient"."birth_date", 
                 "patient"."pat_mrn_id", 
                 "zc_ped_delivr_meth"."name", 
                 "zc_ped_delivr_meth"."ped_delivr_meth_c", 
                 aaa."department_id", 
                 "clarity_dep"."department_id", 
                 aaa."alt_event_type_c", 
                 aaa."in_event_type_c" 
          FROM   "CLARITY"."clarity_adt" aaa
                 inner join "CLARITY"."clarity_dep" "CLARITY_DEP" ON aaa."department_id" = "clarity_dep"."department_id"
                 inner join "CLARITY"."patient" "PATIENT" ON aaa."pat_id" = "patient"."pat_id"
                 inner join "CLARITY"."zc_ped_delivr_meth" "ZC_PED_DELIVR_METH" ON "patient"."ped_delivr_meth_c" = "zc_ped_delivr_meth"."ped_delivr_meth_c"
          WHERE  ( "patient"."birth_date" >= To_date ('01-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                 AND "patient"."birth_date" < To_date ('06-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) ) 
                 AND ( aaa."department_id" = 236601 OR aaa."department_id" = 236703 OR aaa."department_id" = 236801 OR aaa."department_id" = 236901 OR aaa."department_id" = 237101 OR aaa."department_id" = 237201 ) 
                 AND aaa."event_type_c" = 3 
        ) q
WHERE   q.rn = 1

您的陈述可以通过使用 IN OR语句来简化（在阅读时）。

SQL 语句 2

SELECT  *
FROM    (
          SELECT ROW_NUMBER() OVER (PARTITION BY pat_id ORDER BY effective_time) AS rn,
                 aaa."effective_time", 
                 aaa."event_type_c", 
                 "clarity_dep"."department_name", 
                 "patient"."birth_date", 
                 "patient"."pat_mrn_id", 
                 "zc_ped_delivr_meth"."name", 
                 "zc_ped_delivr_meth"."ped_delivr_meth_c", 
                 aaa."department_id", 
                 "clarity_dep"."department_id", 
                 aaa."alt_event_type_c", 
                 aaa."in_event_type_c" 
          FROM   "CLARITY"."clarity_adt" aaa
                 inner join "CLARITY"."clarity_dep" "CLARITY_DEP" ON aaa."department_id" = "clarity_dep"."department_id"
                 inner join "CLARITY"."patient" "PATIENT" ON aaa."pat_id" = "patient"."pat_id"
                 inner join "CLARITY"."zc_ped_delivr_meth" "ZC_PED_DELIVR_METH" ON "patient"."ped_delivr_meth_c" = "zc_ped_delivr_meth"."ped_delivr_meth_c"
          WHERE  ( "patient"."birth_date" >= To_date ('01-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                 AND "patient"."birth_date" < To_date ('06-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) ) 
                 AND ( aaa."department_id" IN (236601,236703,236801,236901,237101,237201) )
                 AND aaa."event_type_c" = 3 
        ) q
WHERE   q.rn = 1

最后，我什至会通过为所有表添加别名并使用 WITH 语句来进一步简化它。最终结果将如下所示

SQL 语句 3

;WITH q AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY pat_id ORDER BY effective_time) AS rn,
         aaa."effective_time", 
         aaa."event_type_c", 
         cd."department_name", 
         p."birth_date", 
         p."pat_mrn_id", 
         pdm."name", 
         pdm."ped_delivr_meth_c", 
         aaa."department_id", 
         cd."department_id", 
         aaa."alt_event_type_c", 
         aaa."in_event_type_c" 
  FROM   "CLARITY"."clarity_adt" aaa
         inner join "CLARITY"."clarity_dep" cd ON aaa."department_id" = cd."department_id"
         inner join "CLARITY"."patient" p ON aaa."pat_id" = p."pat_id"
         inner join "CLARITY"."zc_ped_delivr_meth" pdm ON p."ped_delivr_meth_c" = pdm."ped_delivr_meth_c"
  WHERE  p."birth_date" >= To_date ('01-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS'
         AND p."birth_date" < To_date ('06-12-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS' ) ) 
         AND aaa."department_id" IN (236601,236703,236801,236901,237101,237201)
         AND aaa."event_type_c" = 3 
)
SELECT  *
FROM    q
WHERE   rn = 1

Answer 2

只有当日期最早的行也与clarity_adt上的其他条件匹配时，您才会得到行。如果你想要该子集的最早记录，那么你必须将这些条件推送到子查询中。