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

This article mainly explains "how to see MySQL Explain from the title of LeetCode". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to see MySQL Explain from the title of LeetCode".

Title

Topic description: write a SQL query to find all numbers that appear at least three times in a row. And an example is given. According to the example given in the topic, A Fan creates the Logs table locally and inserts the corresponding data, as follows:

We can see that in the above Logs table, 1 is the only number that appears at least three times in a row, so the final output is 1.

Original title: LeetCode 180

When I first saw the topic, Ah Fan didn't react for a moment. I didn't know how to proceed. I thought about whether it could be realized by self-connection. Then the following SQL is written according to the meaning of the topic.

SELECT DISTINCT l1.num FROM `Logs`l1, `Logs`l2, `Logs`l3 WHERE l1.num = l2.num AND l2.num = l3.num AND l1.id = l2.id-1 AND l2.id = l3.id-1

After writing, A Fan submitted it for the first time and prompted the following error. You can see that the return was not renamed at last. After adjusting the SQL, the l1.num was changed to l1.num as ConsecutiveNums and resubmitted, resulting in the second passed picture.

Look at the beginning to see the pass, A Fan is still thinking that this question is nothing, ah, it is still so easy. But suddenly A Fan thought, this topic is about continuous occurrence, but it does not say that ID is continuous, ah, if ID is not continuous, this is not right, and if you need to appear 4 times in a row, what about the number that appears 5 times? You can't connect yourself all the time. If it is written like this, then the whole SQL is too inflexible.

Then A Fan took a look at the official answer and related comments. Sure enough, although the official answer is consistent with that of A Fan, there are many friends in the following comments talking about the problem of ID discontinuity.

Since there is something wrong with this practice, it is natural that some good people will come up with a solution, and sure enough, a boss in the comment area has given the following solution

Just saw this solution, A Fan suddenly did not understand, the code was submitted, and sure enough, it passed normally. And this solution will not be limited by the conditions that appear several times. With the mentality of learning, A Fan is ready to study the contents of this SQL.

SQL disassembly

First of all, there are several places in this SQL that confuse Ah Fan. The first is the @ symbol, and then there is a case when then grammar. I have never encountered this kind of writing in CRUD on weekdays, but I don't know it doesn't matter. Google will be fine. Checked on the Internet, @ prev represents the declaration variable,: = operation is the assignment operation of MySQL, case when then when is followed by judgment condition, and if the condition is established, it will return the result after then. It is important to note that case will only return the first qualified result, and the rest will be ignored.

After a simple understanding of the above knowledge points, we can disassemble the following SQL.

Select distinct Num as ConsecutiveNums from (select Num, case when @ currnet = Num then @ count: = @ count + 1 when (@ currnet: = Num) is not null then @ count: = 1 end as CNT from Logs, (select @ currnet: = null,@count: = 0) as t) as temp where temp.CNT > = 3

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The outermost select distinct Num as ConsecutiveNums from () as temp where temp.CNT > = 3; we can see that the middle parenthesis is derived into a temporary table called temp, and there are two fields in the temp table that are Num,CNT. In fact, Num is the number in the table Logs, CNT is the cumulative number of consecutive occurrences, and the final where temp.CNT > = 3 is queried according to the number of consecutive occurrences required.

The derived statement SELECT Num,CASE WHEN @ currnet=Num THEN @ count:=@count+1 WHEN (@ currnet:=Num) IS NOT NULL THEN @ count:=1 END AS CNT FROM LOGS, (SELECT @ currnet:=NULL,@count:=NULL) AS t contains two parts, one is the case when then in Select and the other is the (select @ currnet:= null,@count: = null) as t in from, where select @ currnet:= null,@count: = null is also a derived table Here, by declaring two variables @ currnet, @ count and assigning the value to null.

The contents of the intermediate derived table temp are as follows, querying the data by generating a temporary table that records the number of occurrences of each number.

Let's take a look at the entire execution of SQL through the explain command:

From select_type, we can see that a total of two tables are derived, which is consistent with our analysis above.

The content of the derived table with ID 3 is select @ current: = null,@count: = 0 to define two variables and assign values, and the larger the id, the first to execute.

The first when in the case statement determines whether the current scanned num value is consistent with the defined variable. If it is consistent, the count is added by one. If it is inconsistent, the next when condition judgment is made, and the count is assigned to 1 to return.

After scanning the whole table, we get the contents of the intermediate table temp above.

I have to say, the above scheme is perfect, there is no question of whether the ID is continuous, and there is no multi-layer self-connection, and the number of consecutive occurrences can be found according to the requirements, which is relatively flexible. When I first saw this SQL, A Fan didn't know the whole execution process, and then he gradually understood the whole execution process through explain, and he also had some understanding of the use of variables in SQL.

Thank you for your reading, the above is the content of "how to look at MySQL Explain from the title of LeetCode". After the study of this article, I believe you have a deeper understanding of how to look at MySQL Explain from the title of LeetCode, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.