获取RAG的Oracle SQL查询

Question

RAG    PCT
------ ---
GREEN  100
AMBER   50
ORANGE  20
RED      0

我需要一个oracle查询才能得到类似的结果(建议使用内连接或外部连接)

if the given PCT >100 then Green
if the given PCT >=50 and PCT < 100 then AMBER
if the given PCT >=20 and PCT < 0 then ORANGE
else RED

Answer 1

您可以使用CASE实现逻辑，如：

SELECT pct, CASE 
    WHEN pct >= 100 THEN 'GREEN'
    WHEN pct >= 50 THEN 'AMBER'
    WHEN pct >= 20 then 'ORANGE'
    ELSE 'RED'
END
FROM mytable

CASE在第一个匹配条件下停止(因此不需要编写WHEN pct >= 50 AND pct < 100，因为pct >= 100已经被前一个条件捕获了)。

如果您使用一个单独的表来存储每个间隔的下限(如myranges) (如您的示例中所示)，并且您希望使用一个包含实际数据的表(如mydata)来存储它，那么它就会更加棘手:您需要确保您正在加入相关的范围记录：

SELECT d.*, r.*
FROM mydata d
INNER JOIN myranges r 
    ON d.value >= r.pct 
    AND (
        LEAD (r.pct) OVER (ORDER BY pct) IS NULL
        OR d.value < LEAD (r.pct) OVER (ORDER BY pct)
    )

Answer 2

您所要求的并不完全有意义--您已经说过“使用一个联接”，但是没有提供任何将加入到中的内容，但没关系。以下代码严格地实现了您的规范：

WITH cteData AS (SELECT 'GREEN' AS RAG, 100 AS PCT FROM DUAL UNION ALL
                 SELECT 'AMBER', 50 FROM DUAL UNION ALL
                 SELECT 'ORANGE', 20 FROM DUAL UNION ALL
                 SELECT 'RED', 0 FROM DUAL)
SELECT RAG, PCT, CASE
                   WHEN PCT > 100 THEN 'GREEN'
                   WHEN PCT >= 50 AND PCT < 100 THEN 'AMBER'
                   WHEN PCT >= 20 AND PCT < 0 THEN 'ORANGE'
                   ELSE 'RED'
                 END AS COLOR
  FROM cteData;

执行时，上述内容将产生：

RAG     PCT COLOR
GREEN   100 RED
AMBER   50  AMBER
ORANGE  20  RED
RED     0   RED

愿科德怜悯你的灵魂。

Answer 3

如果我对你的理解正确，我认为这可能是你想要的：

WITH rag_data AS (SELECT 'GREEN' AS rag, 100 AS PCT FROM DUAL UNION ALL
                  SELECT 'AMBER' AS rag, 50 AS PCT FROM DUAL UNION ALL
                  SELECT 'ORANGE' AS rag, 20 AS PCT FROM DUAL UNION ALL
                  SELECT 'RED' AS rag, 0 AS PCT FROM DUAL),
  sample_data AS (SELECT -1 NUM FROM dual UNION ALL
                  SELECT 0 NUM FROM dual UNION ALL
                  SELECT 1 NUM FROM dual UNION ALL
                  SELECT 19 NUM FROM dual UNION ALL
                  SELECT 20 NUM FROM dual UNION ALL
                  SELECT 21 NUM FROM dual UNION ALL
                  SELECT 49 NUM FROM dual UNION ALL
                  SELECT 50 NUM FROM dual UNION ALL
                  SELECT 51 NUM FROM dual UNION ALL
                  SELECT 99 NUM FROM dual UNION ALL
                  SELECT 100 NUM FROM dual UNION ALL
                  SELECT 101 NUM FROM dual)
SELECT NUM,
       rag,
       pct,
       rn
FROM   (SELECT sd.num,
               rd.rag,
               rd.pct,
               row_number() OVER (PARTITION BY sd.num ORDER BY rd.pct DESC) rn
        FROM   sample_data sd
               INNER JOIN rag_data rd ON sd.num >= rd.pct)
WHERE  rn = 1;

       NUM RAG           PCT         RN
---------- ------ ---------- ----------
         0 RED             0          1
         1 RED             0          1
        19 RED             0          1
        20 ORANGE         20          1
        21 ORANGE         20          1
        49 ORANGE         20          1
        50 AMBER          50          1
        51 AMBER          50          1
        99 AMBER          50          1
       100 GREEN         100          1
       101 GREEN         100          1