mysql - SQL调试-条件被忽略的地方

有人可以向我解释一下。我的SQL：

SELECT
    `offers`.`id`,
    `offers`.`max_available`,
    (SELECT COUNT( coupons.id ) FROM coupons WHERE coupons.status = 'Y' AND coupons.offer_id = offers.id) AS coupons_sold
FROM
    `offers`
WHERE
    `offers`.`status` IN ('P', 'S') AND
    `offers`.`published_at` < 1341612000 AND
    `offers`.`end_at` >1341567914 AND
    `coupons_sold` < `offers`.`max_available`
ORDER BY `offers`.`created_at` DESC
LIMIT 4 OFFSET 0

这将返回我这4行：

id      max_available   coupons_sold
195     19              20
194     9999            0
193     9999            0
159     9999            93

如果我在ID 195所在的位置有这种情况，怎么可能包含带有coupons_sold < offers.max_available的行？我无能为力！

最佳答案

该查询将产生错误，因为您不能在WHERE子句中使用该子句，该子句是SELECT列表中的别名。除非表offers也具有coupons_sold列！

请尝试以下查询：

SELECT id, max_available, coupons_sold
FROM
  ( SELECT
        `offers`.`id`,
        `offers`.`max_available`,
        ( SELECT COUNT( coupons.id )
          FROM coupons
          WHERE coupons.status = 'Y'
            AND coupons.offer_id = offers.id
         ) AS coupons_sold
         offers.created_at
    FROM
        `offers`
    WHERE
        `offers`.`status` IN ('P', 'S') AND
        `offers`.`published_at` < 1341612000 AND
        `offers`.`end_at` >1341567914
  ) AS tmp
WHERE coupons_sold < max_available
ORDER BY created_at DESC
  LIMIT 4 OFFSET 0 ;

关于mysql - SQL调试-条件被忽略的地方，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11360070/