bitsCN.com
有个朋友问我一个返话费的问题,大概意思是这样的:只需把表deal中所有手机用户某天充值两次以上且总金额超过50的用户充值记录查询出来,至于怎么进行返话费那不是重点。
先看看group by的语法:
SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";
GROUP BY子句将集中所有的行在一起,它包含了指定列的数据以及允许合计函数来计算一个或者多个列。
假设我们将从员工表employee表中搜索每个部门中工资最高的薪水,可以使用以下的SQL语句:
SELECT max(salary), dept FROM employee GROUP BY dept;
这条语句将在每一个单独的部门中选择工资最高的工资,结果将他们的salary和dept返回。
group by 顾名思义就是按照xxx进行分组,它必须有“聚合函数”来配合才能使用,使用时至少需要一个分组标识字段。
聚合函数有:sum()、count()、avg()等,使用group by目的就是要将数据分组进行汇总操作。
例如对员工表的操作:
select dept_id,count(emp_id),sum(salary) form employee group by dept_id;
这样的运行结果就是以“dept_id”为分类标志统计各单位的职工人数和工资总额。
再看看having的语法:
SELECT column1, SUM(column2) FROM “list-of-tables” GROUP BY “column-list” HAVING “condition”;
这个HAVING子句的作用就是为每一个组指定条件,像where指定条件一样,也就是说,可以根据你指定的条件来选择行。如果你要使用HAVING子句的话,它必须处在GROUP BY子句之后。
例如还是对员工表的操作:
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:充值日期
上边就这些有效字段,假如数据(数据纯属虚构,如有*,纯是巧合)如下:
sell_no name phone amount date
00000000001 李晓红 15822533496 50 2011-10-23 08:09:23
00000000002 李晓红 15822533496 60 2011-10-24 08:15:34
00000000003 李晓红 15822533496 30 2011-10-24 12:20:56
00000000004 杨 轩 18200000000 100 2011-10-24 07:59:43
00000000005 杨 轩 18200000000 200 2011-10-24 10:11:11
00000000006 柳梦璃 18211111111 50 2011-10-24 09:09:46
00000000007 韩菱纱 18222222222 50 2011-10-24 08:09:45
00000000008 云天河 18333333333 50 2011-10-24 08:09:25
把以上数据当天(2011-10-24)交过两次话费,而且总金额大于50的数据取出来,要取的结果如下:
00000000002 李晓红 15822533496 60 2011-10-24 08:15:34
00000000003 李晓红 15822533496 30 2011-10-24 12:20:56
00000000004 杨 轩 18200000000 100 2011-10-24 07:59:43
00000000005 杨 轩 18200000000 200 2011-10-24 10:11:11
因为今天(2011-10-24)李晓红和杨轩交过两次以上话费,而且总金额大于50,所以有他们的数据,而柳梦璃,韩菱纱,云天河只交过一次,所以没他们的数据。
我的处理思路大概是这样的,先把当天日期的记录用group by进行手机号分组即一个手机号为一组,接着用having子句进行过滤,把交过两次话费且话费总金额大于50的手机号查出来,最后用手机号和日期条件组合查询就能完成数据的查询,具体如下。
注意日期处理细节,要查询的某一天(yyyy-MM-dd)的所有记录mysql是这样处理的:
SELECT date_format(date,'%Y-%m-%d') from deal;
查询出符合条件(交过两次以上话费,而且总金额大于50)的手机号:
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;
结合手机号和日期查询出最终记录:
select * from deal where date_format(date,'%Y-%m-%d')="2011-10-24" and phone in
(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) order by phone;
里边嵌套了一个select语句,感觉效率低点了,谁有更高效的方法不?
附数据建库sql代码:
create database if not exists `phone_deal`;
USE `phone_deal`;
DROP TABLE IF EXISTS `deal`;
CREATE TABLE `deal` (
`sell_no` varchar(100) NOT NULL,
`name` varchar(100) default NULL,
`phone` varchar(100) default NULL,
`amount` decimal(10,0) default NULL,
`date` datetime default NULL,
PRIMARY KEY (`sell_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `deal`(`sell_no`,`name`,`phone`,`amount`,`date`) values ('00001','李晓红','15822533496','60','2011-10-23 08:09:23'),('00002','李晓红','15822533496','50','2011-10-24 08:15:34'),('00003','李晓红','15822533496','40','2011-10-24 12:20:56'),('00004','杨轩','18210607179','100','2011-10-24 07:59:43'),('00005','杨轩','18210607179','50','2011-10-24 10:11:11'),('00006','柳梦璃','15822533492','1000','2011-10-24 09:09:46'),('00007','韩菱纱','15822533493','10000','2011-10-24 08:09:45'),('00008','云天河','15822533494','500','2011-10-24 08:09:25');
bitsCN.com
要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

MySQL异步主从复制通过binlog实现数据同步,提升读性能和高可用性。1)主服务器记录变更到binlog;2)从服务器通过I/O线程读取binlog;3)从服务器的SQL线程应用binlog同步数据。

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

MySQL的安装和基本操作包括:1.下载并安装MySQL,设置根用户密码;2.使用SQL命令创建数据库和表,如CREATEDATABASE和CREATETABLE;3.执行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤,你可以从零开始构建和管理MySQL数据库。

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中,减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL适合初学者使用,因为它安装简单、功能强大且易于管理数据。1.安装和配置简单,适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施,确保数据的安全和一致性。

全表扫描在MySQL中可能比使用索引更快,具体情况包括:1)数据量较小时;2)查询返回大量数据时;3)索引列不具备高选择性时;4)复杂查询时。通过分析查询计划、优化索引、避免过度索引和定期维护表,可以在实际应用中做出最优选择。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

Dreamweaver CS6
视觉化网页开发工具

DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。

禅工作室 13.0.1
功能强大的PHP集成开发环境

ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境