时间戳

首页 > 解决方案 > sql oracle - 使用 case when 过滤查询 sql

sql - sql oracle - 使用 case when 过滤查询 sql

问题描述

我有以下查询

select
    ens.ID_ENS,
    ens.NOM_ENS,
    
   -- for the first SURVEILLANT
    disp.SALLE_EXAM,
    disp.NB_HEURES_ENS,

    -- for the second SURVEILLANT
    disp.SALLE_EXAM2,
    disp.NB_HEURES_ENS2

from ESP_ENSEIGNANT ens, ESP_MODULE_PANIER_CLASSE_SAISO disp
WHERE ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2) AND ens.NOM_ENS != 'A AFFECTER';

我想只为 SURVEILLANT 更新显示 SALLE_EXAM、NB_HEURES_ENS 的查询

select
    ens.ID_ENS,
    ens.NOM_ENS,

    CASE
        WHEN ens.ID_ENS = disp.SURVEILLANT THEN (disp.SALLE_EXAM AS "EXAM", disp.NB_HEURES_ENS AS "HOURDISP")
        WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN (disp.SALLE_EXAM2 AS "EXAM", disp.NB_HEURES_ENS2 AS "HOURDISP")
    END

from ESP_ENSEIGNANT ens, ESP_MODULE_PANIER_CLASSE_SAISO disp
WHERE ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2) AND ens.NOM_ENS != 'A AFFECTER';

标签： sqloracle

解决方案

JOIN您可以使用显式语法将查询重写为

SELECT ens.ID_ENS,
       ens.NOM_ENS,      
       CASE
         WHEN ens.ID_ENS = disp.SURVEILLANT THEN
           disp.SALLE_EXAM 
         WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN
           disp.SALLE_EXAM2
       END AS "EXAM",
       CASE
         WHEN ens.ID_ENS = disp.SURVEILLANT THEN
          disp.NB_HEURES_ENS 
         WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN
          disp.NB_HEURES_ENS2
       END AS "HOURDISP"
  FROM ESP_ENSEIGNANT ens
  JOIN ESP_MODULE_PANIER_CLASSE_SAISO disp
    ON ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2)
   AND ens.NOM_ENS != 'A AFFECTER';

附注：如果disp.SURVEILLANT和disp.SURVEILLANT2具有相等的值，则disp.SALLE_EXAM和disp.NB_HEURES_ENS将分别被选中。例如，第一个组件具有优先权。

推荐阅读