Usage of groupby and having syntaxes in the database _ MySQL

Detailed explanation of the usage of groupby and having syntaxes in the database bitsCN.com

A friend asked me a call back question, which probably means: in the deal table, you only need to query the record of top-up of all the mobile phone users who recharge the account more than two times a day and the total amount exceeds 50.

Let's first look at the group by syntax:

SELECT column1, SUM (column2) FROM "list-of-tables" group by "column-list ";

The group by clause combines all rows in the set. it contains the data of the specified column and allows the aggregate function to calculate one or more columns.

Suppose we will search for the highest salary in each department from the employee table, we can use the following SQL statement:

SELECT max (salary), dept FROM employee group by dept;

This statement selects the highest salary in each individual department and returns their salary and dept.

As the name suggests, group by is grouped by xxx. it must be used with an aggregate function. at least one group ID field is required for use.

Aggregate functions include sum (), count (), and avg (). the purpose of using group by IS to group data for aggregation.

For example:

Select dept_id, count (emp_id), sum (salary) form employee group by dept_id;

The result of this operation is to use the "dept_id" as the classification mark to count the number of employees and total wages of each unit.

Let's look at the having syntax:

SELECT column1, SUM (column2) FROM "list-of-tables" group by "column-list" HAVING "condition ";

This HAVING clause is used to specify conditions for each group, just like where, that is, you can select rows based on the conditions you specify. If you want to use the HAVING clause, it must be behind the group by clause.

For example:

SELECT dept_id, avg (sal) FROM employee group by dept_id HAVING avg (salary)> = 4000;

The result of this operation is to calculate the number of employees and the average salary of each unit based on the "dept_id" classification mark, and the average salary is greater than 4000.

The following describes how to implement the call back query function:

The deal fields in the telephone bill table include the following:

Sell_no: Order No.

Name: User name

Phone: user's mobile phone number

Amount: recharge amount

Date: recharge date

The above are valid fields. if data (data is purely fictitious, if there is *, it is a coincidence) is as follows:

Sell_no name phone amount date

00000000001 Li Xiaohong 15822533496 50 08:09:23

00000000002 Li Xiaohong 15822533496 60 08:15:34

00000000003 Li Xiaohong 15822533496 30 12:20:56

00000000004 Yang Xuan 18200000000 100 07:59:43

00000000005 Yang Xuan 18200000000 200 10:11:11

00000000006 Liu Mengying 18211111111 50 09:09:46

00000000007 Han lingsha 18222222222 50 08:09:45

00000000008 yuntianhe 18333333333 50 08:09:25

If you pay the above data twice on the current day () and obtain the data with a total amount greater than 50, the result is as follows:

00000000002 Li Xiaohong 15822533496 60 08:15:34

00000000003 Li Xiaohong 15822533496 30 12:20:56

00000000004 Yang Xuan 18200000000 100 07:59:43

00000000005 Yang Xuan 18200000000 200 10:11:11

Because today () Li Xiaohong and Yang Xuan have paid more than two calls, and the total amount is greater than 50, so there is their data, while Liu Mengyi, Han lingsha, and Yun Tianhe only paid once, so they don't have their data.

My solution is probably like this. First, use group by to group the phone number of the current day, that is, a mobile phone number is a group, and then use the having clause for filtering, after checking the phone number that has been paid twice and the total amount of the phone bill is greater than 50, you can query the data by combining the phone number and the date condition, as shown in the following figure.

Pay attention to the date processing details. mysql processes all records of a certain day (yyyy-MM-dd) to be queried as follows:

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

Find the phone number that meets the condition (more than two calls have been paid, and the total amount is greater than 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;

Query the final records based on the mobile phone number and date:

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;

A select statement is nested in it, and the efficiency is low. who has more efficient methods?

SQL code for creating a database with data:

Create database if not exists 'phone _ deal ';

USE 'phone _ deal ';

Drop table if exists 'dest ';

Create table 'dest '(

'Partition _ 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 ('primary _ no ')

) ENGINE = InnoDB default charset = utf8;

Insert into 'dest' ('Partition _ no', 'name', 'phone', 'amount', 'date') values ('123', 'Li xiaohong ', '123', '60', '1970-10-23 08:09:23 '), ('123', 'Li Xiaohong', '123', '50 ', '2017-10-24 08:15:34 '), ('20170301', 'Li xiaohong', '20170301', '40', '2017-10-24 12:20:56 '), ('1234568', 'Yang Xuan ', '1234568', '1970-10-24 07:59:43'), ('1234568', 'Yang Xuan ', '000000', '50', '2017-10-24 10:11:11 ', '2017-10-24 09:09:46 '), ('123', 'yuntianhe ', '123', '123', '2017-10-24 08:09:25 ');

BitsCN.com