Mysql Where使用列别名
16lz
2021-01-22
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
更多相关文章
- hibernate（*.hbm.xml）中新添加的字段被标记为红色（找不到）的解决方法
- Linux安装MySQL的两种方法 先卸载之前版本
- linux安装apache/mysql/php的最新完整方法(cents和ubuntu都实用)
- 获取项目列表的更好方法：缓存序列化数据与数据库查询或其他？
- cpanel导入大数据库(mysql)的方法
- mysql的count方法详解
- 通用的增删改查方法（反射）附带MySQL数据库连接
- php将图片以二进制形式保存到mysql数据库的解决方法
- linux CentOS 7.4下 mysql5.7.20 密码改简单的方法