通过语句递增命令

Question

我有一个类似这样的查询：

SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps
FROM Table
WHERE Doctor = 'Dr. ABC'
AND numberOfOps > 5
GROUP BY ElapsedTime
ORDER BY CASE
  WHEN ElapsedTime = 'Preoperative' THEN 1
  WHEN ElapsedTime = '2 Weeks' THEN 2
  WHEN ElapsedTime = '6 Weeks' THEN 3
  WHEN ElapsedTime = '3 Months' THEN 4
  WHEN ElapsedTime = '6 Months' THEN 5
  WHEN ElapsedTime = '1 Year' THEN 6
  WHEN ElapsedTime = '2 Years' THEN 7
  WHEN ElapsedTime = '3 Years' THEN 8
  WHEN ElapsedTime = '4 Years' THEN 9
  WHEN ElapsedTime = '5 Years' THEN 10
  WHEN ElapsedTime = '6 Years' THEN 11
  WHEN ElapsedTime = '7 Years' THEN 12
  WHEN ElapsedTime = '8 Years' THEN 13
  WHEN ElapsedTime = '9 Years' THEN 14
  WHEN ElapsedTime = '10 Years' THEN 15
  ELSE ElapsedTime END ASC

如果ABC博士没有任何结果w/ 'DaysElapsed‘==’术前‘，其余的顺序就会被忽略--如下所示：

'5 Years,6 Years,7 Years,8 Years,10 Years,2 Weeks,6 Weeks,3 Months,6 Months,1 Year,2 Years,3 Years,4 Years,Unknown'

如何使这一条款的订单更加有力？

是否有任何方法在语句中包含递增变量，例如

SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps
FROM Table
WHERE Doctor = 'Dr. ABC'
GROUP BY Time
var i = 0;
ORDER BY CASE
  WHEN ElapsedTime = 'Preoperative' THEN ++i
  WHEN ElapsedTime = '2 Weeks' THEN ++i
  WHEN ElapsedTime = '6 Weeks' THEN ++i
  WHEN ElapsedTime = '3 Months' THEN ++i
  .          .          .          .
  .          .          .          .
  .          .          .          .
  .          .          .          .   
  WHEN ElapsedTime = '10 Years' THEN ++1
  ELSE ElapsedTime END ASC

我理解SQL不允许这样的事情，但这是我正在寻找的功能类型。

谢谢

Answer 1

创建一个引用表，类似于：

CREATE TABLE ElapsedTimeRef (
    varchar timeString,
    int orderValue
);

INSERT INTO ElapsedTimeRef (timeString, orderValue) VALUES
    ('Preoperative', 1),
    ('2 Weeks', 2),
    . . . . .
    ('9 Years', 14),
    ('10 Years', 15);

然后在您的选择中加入它，比如：

SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps
FROM foo, ElapsedTimeRef etr
WHERE foo.Doctor = 'Dr. ABC'
AND foo.numberOfOps > 5
AND foo.ElapsedTime = etr.timeString
GROUP BY foo.ElapsedTime
ORDER BY etr.orderValue ASC

(P.S.我假设您的表名实际上不是" table“，因为TABLE是一个SQL关键字。)

Answer 2

您可能会编写一个将DaysElapsed变量转换为数字的PLSQL函数，然后像这样使用它：

SELECT Time
  FROM Table
 WHERE Doctor = 'Dr. ABC'
 GROUP BY Time
 ORDER DaysElapsedToOrderBy(DaysElapsed);

函数本身可以如下所示。Note表示，这是非常基本的，可能会导致错误--只将其作为一个示例实现，以便进一步改进。

CREATE FUNCTION DaysElapsedToOrderBy(psDaysElapsed VARCHAR) RETURN NUMBER IS

    nCount        NUMBER;
    sIntervalType VARCHAR(100);

BEGIN

    -- Split DaysElapsed to number of intervals and intervalType
    nCount := TO_NUMBER(SUBSTR(psDaysElapsed, 1, 1));
    sIntervalType := SUBSTR(psDaysElapsed, 3);

    -- calculate order by value
    CASE sIntervalType

        WHEN 'days'   THEN RETURN nCount
        WHEN 'months' THEN RETURN nCount * 10
        WHEN 'years'  THEN RETURN nCount * 100
        ...

    END

END;

Answer 3

您可以使用一个游标(这将获得I++逻辑)来填充CTE/ Table/Table变量，然后根据表加入该变量，并且根据查询的不同，您可以按未选择的列进行排序。当我回到家的时候，长篇的回答。