数据库中Having放在分组函数group by后过滤查询组

 

数据库中group by和having语法使用详解

题：把一天中充值记录超过2次且 总金额超过50的用户充值记录查询出来

先看看group by的语法：

    

[java]  view plain  copy

1. SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";  

GROUP BY子句将集中所有的行在一起，它包含了指定列的数据以及允许合计函数来计算一个或者多个列。

假设我们将从员工表employee表中搜索每个部门中工资最高的薪水，可以使用以下的SQL语句：

[sql]  view plain  copy

1. SELECT max(salary), dept FROM employee GROUP BY dept;  

这条语句将在每一个单独的部门中选择工资最高的工资，结果将他们的salary和dept返回。

    group by 顾名思义就是按照xxx进行分组，它必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标识字段。

    聚合函数有：sum()、count()、avg()等，使用group by目的就是要将数据分组进行汇总操作。

    例如对员工表的操作：

    

[sql]  view plain  copy

1. select dept_id,count(emp_id),sum(salary) form employee  group by dept_id;  

    这样的运行结果就是以“ dept_id ”为分类标志统计各单位的职工人数和工资总额。

再看看having的语法：

    

[sql]  view plain  copy

1. SELECT column1, SUM(column2) FROM “list-of-tables” GROUP BY “column-list” HAVING “condition”;  

这个HAVING子句的作用就是为每一个组指定条件，像where指定条件一样，也就是说，可以根据你指定的条件来选择行。如果你要使用HAVING子句的话，它必须处在GROUP BY子句之后。

    例如还是对员工表的操作：

    

[sql]  view plain  copy

1. SELECT dept_id, avg(sal) FROM employee GROUP BY dept_id HAVING avg(salary) >= 4000;  

    这样的运行结果就是以“dept_id”为分类标志统计各单位的职工人数和工资平均数且工资平均数大于4000。

下面开始我们的返话费查询功能的实现：

话费表deal字段有这些：
sell_no:订单编号
name:用户名
phone:用户手机号
amount:充值金额
date:充值日期

上边就这些有效字段，假如数据（数据纯属虚构，如有*，纯是巧合）如下：

[plain]  view plain  copy

1. sell_no             name         phone               amount      date  
2. 00000000001         李晓红       15822533496         50          2011-10-23 08:09:23  
3. 00000000002         李晓红       15822533496         60          2011-10-24 08:15:34  
4. 00000000003         李晓红       15822533496         30          2011-10-24 12:20:56  
5. 00000000004         杨 轩        18200000000         100         2011-10-24 07:59:43  
6. 00000000005         杨 轩        18200000000         200         2011-10-24 10:11:11  
7. 00000000006         柳梦璃       18211111111         50          2011-10-24 09:09:46  
8. 00000000007         韩菱纱       18222222222         50          2011-10-24 08:09:45  
9. 00000000008         云天河       18333333333         50          2011-10-24 08:09:25  

把以上数据当天(2011-10-24)交过两次话费，而且总金额大于50的数据取出来，要取的结果如下：

[plain]  view plain  copy

1. 00000000002         李晓红       15822533496         60          2011-10-24 08:15:34  
2. 00000000003         李晓红       15822533496         30          2011-10-24 12:20:56  
3. 00000000004         杨 轩        18200000000         100         2011-10-24 07:59:43  
4. 00000000005         杨 轩        18200000000         200         2011-10-24 10:11:11  

因为今天(2011-10-24)李晓红和杨轩交过两次以上话费，而且总金额大于50，所以有他们的数据，而柳梦璃，韩菱纱，云天河只交过一次，所以没他们的数据。

我的处理思路大概是这样的，先把当天日期的记录用group by进行手机号分组即一个手机号为一组，接着用having子句进行过滤，把交过两次话费且话费总金额大于50的手机号查出来，最后用手机号和日期条件组合查询就能完成数据的查询，具体如下。

    注意日期处理细节，要查询的某一天(yyyy-MM-dd)的所有记录MySQL是这样处理的：

[sql]  view plain  copy

1. SELECT date_format(date,'%Y-%m-%d') from deal;  

    查询出符合条件（ 交过两次以上话费，而且总金额大于50 ）的手机号：

[sql]  view plain  copy

1. select phone from deal where date_format(date,'%Y-%m-%d')="2011-10-24" group by phone having count(phone)>1 and sum(amount)>50;  

    结合手机号和日期查询出最终记录：

[sql]  view plain  copy

1. select * from deal where date_format(date,'%Y-%m-%d')="2011-10-24" and phone = #{phone}
2.    group by phone having count(phone)>1 and sum(amount)>50) order by phone; 
3. 后记：当然考虑业务的话，可以在充值记录表中加个充值次数字段。。。。