Mysql Where使用列别名

参考  http://blog.csdn.net/github_26672553/article/details/51889847

SELECT customer_id,group_id,email,SUM(grand_total) AS total_amount,COUNT(*) AS orders_num   
FROM sales_flat_order_grid      
LEFT JOIN customer_entity  ON customer_entity.entity_id = sales_flat_order_grid.customer_id     
WHERE `status`='complete' AND customer_id IS NOT NULL  AND customer_entity.group_id = 1   
GROUP BY customer_id      
ORDER BY orders_num DESC 

需求：

我只需要orders_num这列值 >= 5的数据。

方法：在这个select外面 在套一个 select

SELECT SS.* FROM (  
    #...上面那个SQL    
)SS  
where SS.orders_num >=5   

最终如下：

SELECT SS.* FROM (  
    SELECT customer_id,group_id,email,SUM(grand_total) AS total_amount,COUNT(*) AS orders_num   
    FROM sales_flat_order_grid      
    LEFT JOIN customer_entity  ON customer_entity.entity_id = sales_flat_order_grid.customer_id     
    WHERE `status`='complete' AND customer_id IS NOT NULL  AND customer_entity.group_id = 1   
    GROUP BY customer_id      
    ORDER BY orders_num DESC      
)SS  
where SS.orders_num >=5  

优化前：

select  (select t.gc_name from shop_goods_class as t where t.gc_id=(select two_gc.gc_parent_id from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) ) as oneName,

(select two_gc.gc_name from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) as twoGcName,
 (select two_gc.gc_id from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) as id,
 (select two_gc.gc_parent_id from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) as pid,
gc.gc_name as threeGcName from shop_goods_class as gc where   (select two_gc.gc_name from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) is not null



优化后的sql：
select * from (select  (select t.gc_name from shop_goods_class as t where t.gc_id=(select two_gc.gc_parent_id from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) ) as oneName,
(select two_gc.gc_name from shop_goods_class as two_gc where two_gc.gc_id=gc.gc_parent_id) as twoGcName,
gc.gc_name as threeGcName from shop_goods_class as gc )tt
where tt.twoGcName is not null  and tt.oneName is not null