Mysql Where使用列别名

最新推荐文章于 2023-04-12 20:19:27 发布

原创最新推荐文章于 2023-04-12 20:19:27 发布 · 4.1k 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

71 篇文章

订阅专栏

本文介绍了一种通过SQL查询筛选特定客户的方法。具体来说，通过对已完成订单的客户进行分组统计，仅选择下单数量大于等于5次的客户及其相关信息。

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

上面SQL查询结果如下：

需求：

我只需要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