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
The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Dreamweaver CS6
Visual web development tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download
The most popular open source editor

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
