SQLite:在计算中使用别名

Question

可以在 SQLite 的计算列中使用别名吗？我有以下疑问:

SELECT
    dpTicker AS Ticker,
    max(dpDate) AS Datum,
    dpClose AS Price,
    (SELECT
        sum(div)
    FROM Dividends
    WHERE
        divTicker = DP1.dpTicker
        AND strftime('%Y', divDate) = strftime('%Y', date('now','-1 Year'))) 
    AS sumDiv,
    (SELECT
        sum(div)
    FROM Dividends
    WHERE
        divTicker = DP1.dpTicker
        AND strftime('%Y', divDate) = strftime('%Y', date('now','-1 Year')))/dpClose*100
     AS divYield    
FROM DailyPrices AS DP1
GROUP BY dpTicker
ORDER BY divYield DESC;

正如您在上面的查询中注意到的，sumDiv 的代码重复了两次。我一直在搜索如何在计算列中使用别名，但是，我的尝试没有结果。

在计算中直接使用别名 (sumDiv) 会导致错误消息。我读过，将别名包装在 SELECT 语句周围应该可以工作:

(SELECT sumDiv)/dpClose AS divYield

此尝试会导致相同的错误:

bash-3.2$ sqlite3 myShares < Queries/test.sql
Error: near line 1: no such column: sumDiv

可以在计算中使用别名，还是必须重复代码两次？

预先感谢您的任何反馈，并帮助爬上 SQLite 学习曲线。

最诚挚的问候，

游戏

Answer 1

SQL 标准规定别名在同一 SELECT 子句的其他部分中不可见。

_{All problems in computer science can be solved by another level of indirection.
— David Wheeler}

只需将计算移至子查询中即可:

SELECT sumDiv,
       sumDiv / dpClose AS divYield
FROM (SELECT (SELECT sum(div)
              FROM Dividends
              WHERE ...
             ) AS sumDiv,
             ...
      FROM DailyPrices
      ...);