SQL Oracle - 单独 ID 的案例

ID  Service 
1   A
1   A1
2   A
2   B
3   A
3   A1

SELECT a.ID, a.service,
CASE 
    WHEN a.service IN ('A','A1') THEN 'Yes'
    ELSE 'No'
END      
FROM t1 a

ID  Service  RELATION
1   A        YES
1   A1       YES
2   A        NO
2   B        NO
3   A        YES
3   A1       YES

首先，您应该根据服务按字母顺序对表进行排序。 然后创建一个数组并用所有服务填充它：

DECLARE 
   CURSOR c_t1 is 
   SELECT  service FROM t1; 
   type c_list is varray (6) of t1.service%type; 
   service_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_t1 LOOP 
      counter := counter + 1; 
      service_list.extend; 
      service_list(counter)  := n.service; 
      dbms_output.put_line('Service('||counter ||'):'||service_list(counter)); 
   END LOOP; 
END; 
/ 

然后循环访问表值：

DECLARE
        v_count number DEFAULT 0;
BEGIN
    FOR i IN (SELECT t1.ID, t1.service FROM t1)
    LOOP
        FOR j IN (service_list.COUNT)
        LOOP
            CASE 
                WHEN t1.service IN (service_list[i], service_list[i+1]) THEN 'Yes'
                ELSE 'No'
            END      
            v_COUNT := v_COUNT + 1;
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_COUNT);
END;
/

以下查询有效，尽管我不确定它的性能如何。

WITH a1
     AS (SELECT id, 1 AS here
         FROM   services
         WHERE  service = 'A1')
   , a
     AS (SELECT id, 1 AS here
         FROM   services
         WHERE  service = 'A')
SELECT driver.id
     , driver.service
     , CASE
          WHEN NVL(a1.here, 0) + NVL(a.here, 0) > 1 THEN 'YES'
          ELSE 'NO'
       END
FROM   services driver
       LEFT OUTER JOIN a1 ON a1.id = driver.id
       LEFT OUTER JOIN a ON a.id = driver.id

但是我在检查每个ID的关系时遇到问题 独立地

这就是为什么建议您创建一个新表来存储服务之间的所有唯一关系。这为您提供了更好的处理来比较关系并为您提供所需的输出。

您应该首先创建存储此类值的关系表。

|rid| S1 | S2 |
--- |----|----|
|1  |  A | A1 |

现在，要生成所需的输出，您应该首先使用自连接来获取具有公共ID的关系对。

SELECT a.id, 
       a.service,
       b.service
FROM   t1 a 
       join t1 b 
         ON ( a.service != b.service 
              AND a.id = b.id ) ;

获得此类组合后，您所需要的只是使用带有关系表OUTER JOIN检查该关系组合是否存在，这将帮助您根据存在将输出为"是"或"否"。

SELECT a.id, 
       b.service, 
       CASE 
         WHEN r.id IS NULL THEN 'NO' 
         ELSE 'YES' 
       END AS relation 
FROM   t1 a 
       join t1 b 
         ON ( a.service != b.service 
              AND a.id = b.id ) 
       left join rel r 
              ON ( ( r.s1 = a.service 
                     AND r.s2 = b.service ) 
                    OR ( r.s2 = a.service 
                         AND r.s1 = b.service ) ); 

演示

我会使用窗口函数：

select t1.*, (case when sum(case when service not in ('A', 'A1') then 1 else 0 end) over (partition by id) = 0
                   then 'yes' else 'no'
              end) as Relation
from t1; 

这将起作用，你必须为它使用解码功能：

create table ex2(
no1 number,
no2 varchar(20));
insert into ex2 values(1,    'A');
insert into ex2 values(1   , 'A1');
insert into ex2 values(2   , 'A');
insert into ex2 values(2   , 'B');
insert into ex2 values(3   , 'A');
insert into ex2 values(3   , 'A1');
select * from ex2;
SELECT no1, no2 ,
decode(no2,'A','yes','A1','yes','no')      
FROM ex2;

输出：

1   A   yes
2   A   yes
2   B   no
3   A   yes
3   A1  yes
1   A1  yes

对于您的表查询是：

SELECT ID, service ,
decode(service,'A','yes','A1','yes','no')
FROM ex2;