SQL查询中无法引用SELECT生成的列进行条件过滤求助

这个问题我太熟了——SQL的执行顺序在这儿给你挖了个小坑！你在SELECT里定义的aTotal、dTotal这些别名，在WHERE子句里是没法直接引用的，因为数据库的执行逻辑是先处理FROM/JOIN，再执行WHERE过滤，最后才会处理SELECT里的列和别名。也就是说，当数据库执行WHERE条件的时候，这些别名对应的计算结果还没生成呢，它自然不知道你说的aTotal是什么。

下面给你几种可行的解决方案：

方法1：用CTE（公共表表达式）实现清晰过滤 #

CTE可以先帮你把包含所有计算列的结果集生成出来，然后你再在外层查询里用别名做过滤，逻辑非常清晰，也容易维护：

WITH summary AS (
    SELECT 
        a.securityID, 
        username, 
        a.dateOn, 
        (SELECT SUM(pricePaid*qty) as total FROM auctions_cart c INNER JOIN auctions_orders o ON o.orderID=c.orderID WHERE o.securityID=a.securityID AND c.status='closed' AND o.dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND o.dateOn>='7/2/2013 9:16:15 AM') as aTotal, 
        (SELECT SUM(price*qty) as total FROM donations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM' AND rDenied<>'True') as dTotal, 
        (SELECT SUM(price*qty) as total FROM events_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as eTotal, 
        (SELECT SUM(price*qty) as total FROM registrations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as rTotal 
    FROM authorizeNet a 
    INNER JOIN security s ON s.securityID=a.securityID 
    WHERE a.dateOn IS NOT NULL
)
SELECT * 
FROM summary
WHERE aTotal > 0 OR dTotal > 0 OR eTotal > 0 OR rTotal > 0;

方法2：用子查询包裹原查询 #

如果你的数据库版本不支持CTE（比如一些老版本的MySQL），可以用子查询来实现同样的效果，原理和CTE一样，先执行内层查询生成带别名的结果，再在外层过滤：

SELECT *
FROM (
    SELECT 
        a.securityID, 
        username, 
        a.dateOn, 
        (SELECT SUM(pricePaid*qty) as total FROM auctions_cart c INNER JOIN auctions_orders o ON o.orderID=c.orderID WHERE o.securityID=a.securityID AND c.status='closed' AND o.dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND o.dateOn>='7/2/2013 9:16:15 AM') as aTotal, 
        (SELECT SUM(price*qty) as total FROM donations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM' AND rDenied<>'True') as dTotal, 
        (SELECT SUM(price*qty) as total FROM events_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as eTotal, 
        (SELECT SUM(price*qty) as total FROM registrations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as rTotal 
    FROM authorizeNet a 
    INNER JOIN security s ON s.securityID=a.securityID 
    WHERE a.dateOn IS NOT NULL
) AS subquery
WHERE aTotal > 0 OR dTotal > 0 OR eTotal > 0 OR rTotal > 0;

方法3：直接在WHERE中重复子查询（不推荐） #

如果你不想用嵌套查询，也可以把每个子查询直接写到WHERE条件里，但这种方法会导致代码大量重复，维护起来很麻烦，而且数据库可能会重复执行这些子查询（取决于优化器），性能不如前两种：

SELECT 
    a.securityID, 
    username, 
    a.dateOn, 
    (SELECT SUM(pricePaid*qty) as total FROM auctions_cart c INNER JOIN auctions_orders o ON o.orderID=c.orderID WHERE o.securityID=a.securityID AND c.status='closed' AND o.dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND o.dateOn>='7/2/2013 9:16:15 AM') as aTotal, 
    (SELECT SUM(price*qty) as total FROM donations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM' AND rDenied<>'True') as dTotal, 
    (SELECT SUM(price*qty) as total FROM events_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as eTotal, 
    (SELECT SUM(price*qty) as total FROM registrations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') as rTotal 
FROM authorizeNet a 
INNER JOIN security s ON s.securityID=a.securityID 
WHERE 
    a.dateOn IS NOT NULL
    AND (
        (SELECT SUM(pricePaid*qty) FROM auctions_cart c INNER JOIN auctions_orders o ON o.orderID=c.orderID WHERE o.securityID=a.securityID AND c.status='closed' AND o.dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND o.dateOn>='7/2/2013 9:16:15 AM') > 0
        OR (SELECT SUM(price*qty) FROM donations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM' AND rDenied<>'True') > 0
        OR (SELECT SUM(price*qty) FROM events_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') > 0
        OR (SELECT SUM(price*qty) FROM registrations_cart WHERE securityID=a.securityID AND dateOn between '11/1/2019 00:01:00.00' AND '11/30/2019 23:59:59.999' AND dateOn>='7/2/2013 9:16:15 AM') > 0
    );

总结 #

优先推荐CTE或者子查询的方式，它们不仅代码结构清晰，而且性能也更优，后续维护起来也方便。

内容的提问来源于stack exchange，提问作者Damien