Shulou(Shulou.com)05/31 Report--

This article is to share with you about the use of group by and having grammar in the database, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

A friend asked me a question about the return fee, which probably means something like this: you only need to query the recharge records of all mobile phone users in the table deal who recharge more than twice a day and the total amount is more than 50. As for how to charge for the return call, that is not the point.

First take a look at the syntax of group by:

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

The GROUP BY clause sets all the rows together, which contains the data for the specified column and allows the aggregate function to calculate one or more columns.

Assuming that we are going to search for the highest salary in each department from the employee table of the employee table, we can use the following SQL statement:

SELECT max (salary), dept FROM employee GROUP BY dept

This statement selects the highest wage in each individual department and returns their salary and dept as a result.

As the name implies, group by is grouped according to xxx. It must be used with an "aggregate function" and requires at least one grouping identification field.

Aggregate functions are: sum (), count (), avg (), and so on. The purpose of using group by is to aggregate data groups.

For example, the operation on the employee table:

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

The result of such operation is to count the number of employees and total wages of each unit with the classified mark of "dept_id".

Take a look at the syntax of having:

SELECT column1, SUM (column2) FROM "list-of-tables" GROUP BY "column-list" HAVING "condition"

The purpose of this HAVING clause is to specify conditions for each group, just as where specifies conditions, that is, rows can be selected according to the conditions you specify. If you want to use the HAVING clause, it must come after the GROUP BY clause.

For example, it is also the operation on the employee table:

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

The result of such operation is to take "dept_id" as the classification mark to count the number of workers and the average salary of each unit, and the average salary is more than 4000.

Let's start with the implementation of our query function for return charges:

These are available in the deal field of the phone bill table:

Sell_no: order number

Name: user name

Phone: user's mobile phone number

Amount: recharge amount

Date: top-up date

For these valid fields above, if the data (the data is purely fictional, if there is *, it is pure coincidence) are as follows:

Sell_no name phone amount date

00000000001 Li Xiaohong 15822533496 50 2011-10-23 08:09:23

Li Xiaohong 15822533496 60 2011-10-24 08:15:34

000000003 Li Xiaohong 15822533496 30 2011-10-24 12:20:56

000000004 Yang Xuan 18200000000 100 2011-10-24 07:59:43

00000000005 Yang Xuan 18200000000 200 2011-10-24 10:11:11

000000006 Liu Mengli 18211111111 50 2011-10-24 09:09:46

000000007 Han Lingsha 18222222222 50 2011-10-24 08:09:45

000000008 Yuntian River 1833333333 50 2011-10-24 08:09:25

If the above data have been paid twice on the same day (2011-10-24), and the total amount is greater than 50, the results are as follows:

Li Xiaohong 15822533496 60 2011-10-24 08:15:34

000000003 Li Xiaohong 15822533496 30 2011-10-24 12:20:56

000000004 Yang Xuan 18200000000 100 2011-10-24 07:59:43

00000000005 Yang Xuan 18200000000 200 2011-10-24 10:11:11

Because today (2011-10-24) Li Xiaohong and Yang Xuan have paid more than two phone charges, and the total amount is more than 50, so they have their data, while Liu Mengli, Han Linsha and Yuntianhe have only paid once, so they do not have their data.

My processing idea is about this: first, the records of the day's date are grouped into groups of mobile phone numbers with group by, that is, a mobile phone number is grouped into a group, and then filtered with the having clause to find out the mobile phone numbers that have been paid twice and the total amount of the phone charges is greater than 50. Finally, the query of the data can be completed with the combination of mobile phone number and date conditions, as follows.

Pay attention to the details of date processing. All records for the day (yyyy-MM-dd) to be queried are handled as follows:

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

Find out the mobile phone numbers that meet the criteria (the phone bill has been paid more than twice 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 record by combining 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

There is a select statement nested in it, which feels less efficient. Who has a more efficient method?

Sql code for building database is attached:

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 (100.00) default NULL

`date`datetime default NULL

PRIMARY KEY (`sell_ no`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into `deal` ('sell_ no`, `name`, `phone`, `quantit`, `date`) values (' 00001 camera recorder'Li Xiaohong', '15822533496' lead '60th camera' 2011-10-23 0814 values 23'), ('00002' lead'Li Xiaohong', '15822533496' 40 'camera 2011-10-24)), (' 00004 'lead' Yang Xuan', '182607179') ), ('00005 Zhuan' Han Lingsha, '15822533494'), (' 00005Li 'Han Lingsha', '15822533492'), ('00005' Han Lingsha', '15822533493' Han Lingsha', '15822533493'), ('00008') '500 magic hands 2011-10-24 08 purl 09purl 25')

The above is how the group by and having syntax is used in the database, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.