代码之家 › 专栏 › 技术社区 › Abhi

如何在sqldeveloper中使用IF-else条件检索最新实例

oracle10g oracle sql

Abhi · 技术社区 · 6 年前

我有一张学生表,如下所示

ROLL  |  STUDENT | SUBJECT | VERSION_ID |

  1        A         M         1
  2        B         M         2 
  3        C         M         3
  4        B         S         1
  5        D         S         2
  6        E         E         1 
  7        F         G         1

如果主题=“M”至少有一条记录,则根据版本id检索主题“M”的最新记录。
ELSIF如果主题=“S”至少有一条记录,则根据版本id检索主题“S”的最新记录。
ELSIF如果主题=“E”至少有一条记录,则根据版本id检索主题“E”的最新记录。
否则不获取任何记录。

基于上述条件,我试图编写一个查询或视图,它可以满足上述所有条件,并给出以下输出。请为我提供可能的解决方案。

输出

ROLL  |  STUDENT | SUBJECT | VERSION_ID | 
  3        C         M         3

我试图使用下面的查询获得所需的输出,但遇到了错误。请帮助我以更好的性能解决此查询。

SELECT * from ( IF EXISTS(select subject from STUDENT where SUBJECT='M') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='M'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='M'))) S5
ON
S1.ROLL=S5.ROLL
ELSIF EXISTS(select subject from STUDENT where SUBJECT='S') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='S'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='S'))) S5
           ON
          S1.ROLL=S5.ROLL
ELSIF EXISTS(select subject from STUDENT where SUBJECT='E') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='E'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='E'))) S5
           ON
          S1.ROLL=S5.ROLL
ELSE 
  SELECT * FROM STUDENT WHERE 1=2);

表格创建:

Create table student (ROLL number,STUDENT varchar2(20),SUBJECT varchar2(20),VERSION_ID number );

插入数据:

insert into STUDENT values(1,'A','M',1);
insert into STUDENT values(2,'B','M',2);
insert into STUDENT values(3,'C','M',3);
insert into STUDENT values(4,'B','S',1);
insert into STUDENT values(5,'D','S',2);
insert into STUDENT values(6,'E','E',1);
insert into STUDENT values(7,'F','G',1);

非常感谢。

2 回复 | 直到 6 年前

Tim Biegeleisen 6 年前

我们可以申请 ROW_NUMBER 使用您在问题中给出的排序逻辑,对整个表进行排序。学科 M , S 和 E 按此顺序优先考虑 VERSION_ID 打破僵局。主题与这三个主题不匹配的行被分配最低优先级。如果没有匹配主题的记录,我们会过滤掉 WHERE 条款

WITH cte AS (
    SELECT ROLL, STUDENT, SUBJECT, VERSION_ID,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN SUBJECT = 'M' THEN 1
                                         WHEN SUBJECT = 'S' THEN 2
                                         WHEN SUBJECT = 'E' THEN 3
                                         ELSE 4 END, VERSION_ID DESC END) rn
)

SELECT ROLL, STUDENT, SUBJECT, VERSION_ID
FROM cte
WHERE rn = 1 AND SUBJECT IN ('M', 'S', 'E')

DeadCat 6 年前

你想要正确的答案对吗?尝试以下操作:

SELECT TOP 1 #student.*
FROM   student
JOIN   (

SELECT  MAX(VERSION_ID) VERSION_ID,SUBJECT
FROM   student
WHERE  SUBJECT = 'M'
GROUP BY SUBJECT

UNION ALL 

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'S'
GROUP BY SUBJECT

UNION ALL

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'E'
GROUP BY SUBJECT

UNION ALL 

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'G'
GROUP BY SUBJECT

UNION ALL

SELECT NULL,NULL

)       AS Dtls
ON     (student.SUBJECT     =      Dtls.SUBJECT
AND student.VERSION_ID     =      Dtls.VERSION_ID)