Shulou(Shulou.com)06/01 Report--

What the editor shares with you today is the thinking and solving process of sharing mysql query interview questions a few days ago. When I encountered this question in the written test, I read it several times and was confused. "for at least N days in a period of time, the total score of every day in these N days is greater than M", easy to circle, and finally did not write correctly. When I think of this question today, I write the answer and verify the sql sentence. Let's take a look.

problem

A game uses a mysql database. The data table scores records the history of user scores. Uid represents the user ID, score represents the score, and date represents the date. Each user generates multiple records every day.

The data structure and rows are as follows:

Now you need a list of users who have a total score of more than 40 points for at least 16 of the 31 days in March 2017. Represented by a sql statement.

Train of thought

Reorganize the requirements and draw the key points.

Now you need a list of users who have a total score of more than 40 points for at least 16 of the 31 days in March 2017. Represented by a sql statement.

User list

Represents a non-repeating uid list, which can be implemented using DISTINCT uid or GROUP BY uid.

In 31 days in March 2017,

Use the where statement to limit the time range.

At least 16 days.

You need to aggregate the day date and use the aggregate function COUNT (*) > 15 to determine.

(per person) the total score per day is more than 40

The daily score needs to be aggregated, and the aggregate function is used to judge SUM (score) > 40.

There are two aggregate functions here, but for different dimensions (days and daily scores), so you need to use a subquery to place the two aggregations on the inner and outer sql statements.

According to the principle of "from the inside out", we first aggregate the scores of each day, that is, we aggregate the days.

-- in 31 days in March 2017, select * from scores where `date` > = '2017-03-01' and `date` = '2017-03-01' and `date`40 group by uid having count-at least 16 days-- based on the above results, aggregate the days using having count () filter results select uid from (select uid,date from scores where `date` > = '2017-03-01' and `date`40) group by uid having count (*) > 15

Answer

SELECT uid FROM (SELECT uid,date FROM WHERE `date` > = '2017-03-01' AND `date`40) WHERE GROUP BY uid HAVING count (*) > 15

Verification

-- structure CREATE TABLE `scores` (`id` int (11) NOT NULL AUTO_INCREMENT, `uid` int (11) DEFAULT NULL, `score` int (11) DEFAULT NULL, `date`date DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- data INSERT INTO `scores`VALUES ('1x,'1x,'1m, '2018-04-03'); INSERT INTO `scores` VALUES (' 2th, '1th,' 2th, '2018-04-03') INSERT INTO `scores` VALUES ('3,'1,'1,'1, '2018-04-04'); INSERT INTO `scores` VALUES ('11,'1,'4,'4, '2018-04-04'); INSERT INTO `scores` VALUES ('12,'1,'3, '2018-04-06'); INSERT INTO `scores` VALUES ('4, 1, 3, 3, 2018-04-07') INSERT INTO `scores`VALUES ('5,'2,'2,'4, '2018-04-04'); INSERT INTO `scores` VALUES ('6,'2,'4,'4, '2018-04-04'); INSERT INTO `scores` VALUES ('7,'2,'1, '2018-04-03'); INSERT INTO `scores` VALUES ('8, 3, 3, 3, 2018-04-06') INSERT INTO `scores` VALUES ('9,'3,'1, '2018-04-05'); INSERT INTO, `scores` VALUES (' 10,'3,'2, '2018-04-04');-- because the data entry is limited, we change the result to:-- get a list of users with a time range of 4 to 6 for at least 2 days, with a total score of more than 2 per day. -- query-- not the most concise statement, including debugging statements, can be run in segments to see the effect of each part of the statement. SELECT uidFROM (SELECT uid, `date`, sum (score) AS total_score FROM scores WHERE `date` > '2018-04-03' AND `date`

2 ORDER BY uid, date) AS aGROUP BY uidHAVING count (*) > 1 shareholder-the answer is: uid: 1 the above is a brief introduction to the thinking and solving process of sharing mysql query questions. Of course, the differences in the detailed use of the above have to be understood by everyone. If you want to know more, welcome to 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.