时间戳

首页 > 解决方案 > SQL 选择查询根据子表中的特定条件仅返回 1 行

sql - SQL 选择查询根据子表中的特定条件仅返回 1 行

问题描述

根据子表中存在的值，我无法确定如何返回特定的父数据行。

我要应用两个过滤器或某些 case 语句，根据子表中的值返回我的registration_idfrom 。table1

  1. 返回regestration_id同时Table3具有和的type值。在这种情况下，它应该返回.13registration_id123
  2. 返回regestration_idwhenTable3仅有type值 of1和typeof3不存在。在这种情况下，它应该返回registration_id.321

这是我用来连接表的 SQL：

select registration_id from table1 t1 
left join table2 t2 on t2.registration_id=t1.registration_id 
left join table3 t3 on t3.x_person_id=t2.x_person_id;

在此处输入图像描述

标签： sqloracle

解决方案

你可以使用EXISTS：

select t1.registration_id
from   table1 t1 
       inner join table2 t2
       on t2.registration_id=t1.registration_id
WHERE  EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type = 1
         AND    t3.x_person_id = t2.x_person_id
       )
AND    EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type = 3
         AND    t3.x_person_id = t2.x_person_id
       );

和NOT EXISTS：

select t1.registration_id
from   table1 t1 
       inner join table2 t2
       on t2.registration_id=t1.registration_id
WHERE  EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type = 1
         AND    t3.x_person_id = t2.x_person_id
       )
AND    NOT EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type = 3
         AND    t3.x_person_id = t2.x_person_id
       );

如果您想一次性完成，EXISTS那么：

select t1.registration_id
from   table1 t1 
       inner join table2 t2
       on t2.registration_id=t1.registration_id
WHERE  EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type IN (1, 3)
         AND    t3.x_person_id = t2.x_person_id
         HAVING COUNT(DISTINCT t3.type) = 2
       )

和：

select t1.registration_id
from   table1 t1 
       inner join table2 t2
       on t2.registration_id=t1.registration_id
WHERE  EXISTS(
         SELECT 1
         FROM   table3 t3
         WHERE  t3.type IN (1, 3)
         AND    t3.x_person_id = t2.x_person_id
         HAVING COUNT(CASE t3.type WHEN 1 THEN 1 END) > 0
         AND    COUNT(CASE t3.type WHEN 3 THEN 1 END) = 0
       )

推荐阅读