oracle - 需要检查多个条件以进行 Oracle 查询

Question

我在下面有一个查询。我可以检查两个条件。如果出现“AAA”，它将显示“AAA”及其 id，否则将显示“BBB”。我需要检查更多条件，例如是否出现“AAA”打印“AAA”及其 id 否则出现“BBB”打印“BBB”及其 id 否则“ccc”出现打印“CCC”及其 id 如何做到这一点？

SELECT DISTINCT institution_id,

         CASE
              WHEN count(*) over(PARTITION BY institution_id) >=5 THEN 'AAA'
              WHEN count(*) over(PARTITION BY institution_id) >=5 THEN 'BBB'

         END AS status

FROM table
WHERE data LIKE '% AAA%' 

标签： oracle

Answer 1

WITH
    INSTITUTION_COUNT
    AS
        (  SELECT INSTITUTION_ID, COUNT (*) AS INSTITUTION_COUNT
             FROM INSTITUTION
         GROUP BY INSTITUTION_ID)
SELECT E.INSTITUTION_ID          AS INSTITUTION_ID,
       DC.INSTITUTION_COUNT    AS INSTITUTION_COUNT,
       CASE
           WHEN DC.INSTITUTION_COUNT < 5 THEN 'Low'
           WHEN DC.INSTITUTION_COUNT >= 5 THEN 'High'
       END    AS STATUS
  FROM INSTITUTION E, INSTITUTION_COUNT DC
 WHERE E.INSTITUTION_ID = DC.INSTITUTION_ID;