MySQL:where 子句中的未知列

Question

SELECT B.*, SC.cate_name, (
        CASE WHEN special_offer_type ='Fixed Value'
        THEN B.price - special_offer
        WHEN special_offer_type =  'Discount %'
        THEN B.price * ( 1 - special_offer / 100.0 ) 
        ELSE B.price
        END
        ) AS final_price,
(IFNULL(xx.avg_rate,'0')) AS avg_rate,
(IFNULL(yy.count_comment, '0')) AS count_comment
FROM book B JOIN setting_category SC ON B.cate_id = SC.cate_id 
LEFT JOIN (SELECT a.isbn, sum(a.rate) / count(a.rate) AS avg_rate 
FROM book_rating a 
GROUP BY a.isbn) AS xx ON b.isbn = xx.isbn 
LEFT JOIN (SELECT c.isbn, count(*) AS count_comment FROM book_comment c 
GROUP BY c.isbn) AS yy ON b.isbn = yy.isbn

使用上面的编码，我在 添加了 4 列 cate_name、final_price、avg_rate 和 count_comment result_table (THX Gordon Linoff，罗纳德·亚历山大·凯洛拉)

用户 AAA 和 BBB 对图书 001 进行了评分，因此图书 001 的 avg_rate 为 (4+5)/2 = 4.5

用户 XXX 和 YYY 对图书 001 添加了评论，因此图书 001 的 count_comment = 2

现在，我想查询最后添加以下代码的final_price的范围。

最终价格在哪里
“60”之间
和“100”
按最终价格排序

但是，我收到错误#1054 - “where 子句”中的未知列“final_price”我该如何修复它？有什么简化代码的建议吗？

book
-----------------------------------------------------------                     
isbn   cate_id   price   special_offer   special_offer_type
001    1         125     5               Fixed Value
002    1         90      30              Discount %
003    2         150     50              Fixed Value

setting_category
--------------------                            
cate_id   cate_name
1         Fiction
2         Dictionary

book_rating                             
------------------------------------------
user   dateadd      timeadd    isbn   rate
AAA    2014/03/20   15:00:00   001     4
BBB    2014/03/21   15:00:00   001     5
CCC    2014/03/22   15:00:00   002     2

book_comment
----------------------------------------------
user    dateadd      timeadd    isbn   comment
XXX     2014/03/20   16:00:00   001    good
YYY     2014/03/21   16:00:00   001    great

result_table
-----------------------------------------------------------------------------------------------------------------
isbn   cate_id   price   special_offer   special_offer_type   cate_name    final_price   avg_rate   count_comment
001    1         125     5               Fixed Value          Fiction      120           4.5        2
002    1         90      30              Discount %           Fiction      63            2          0
003    2         150     50              Fixed Value          Dictionary   100           0          0

Answer 1

嵌入 having 子句，如下所示:

-- include other part of your query here
FROM book B JOIN setting_category SC ON B.cate_id = SC.cate_id 
LEFT JOIN (SELECT a.isbn, sum(a.rate) / count(a.rate) AS avg_rate 
           FROM book_rating a 
           GROUP BY a.isbn) AS xx ON b.isbn = xx.isbn 
LEFT JOIN (SELECT c.isbn, count(*) AS count_comment FROM book_comment c 
           GROUP BY c.isbn) AS yy ON b.isbn = yy.isbn

-- having clause start
HAVING final_price BETWEEN '60' AND '100'
-- having end

ORDER BY final_price