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

MySQL how to customize variables, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Custom variable-self-introduction

Hello everyone, I am mysql custom variable, like other custom variables, you can think of me as a small box, if you want to summon me, just shout

Set @ ver:='x'

You can summon the custom variable volume and put x into the box by the way.

Of course, except that I can install x.

You can also pretend to be b, and you can pretend to be successful by reciting set @ ver:='b' in your heart.

If I'm in a bad mood, I can pretend nothing.

Such as set @ ver:=0

If you summon me, please cherish me, because I am valid in a mysql link.

Don't think that I can only be in bulk and that I can still be installed on the production line.

For example, an indicator produced by a certain sql needs to be saved, and you only need to into the result. The specific operations are as follows:

Select count (1) into @ ver from table

Want to check your eyes with what's in it? select @ ver, tell you what's in the box right now.

I don't usually exist alone, but embedded in SQL statements as a container for storing intermediate variables. If I want to know how to cooperate with my sql statements, I have to know my little brother-sql.

Self-introduction of 2Magi SQL

Hello everyone, I am SQL, is a database operation tool, but also a good brother of custom variables. If you can cooperate with EXCEL to get to know me, you will find that I am not a slow person. Let's not bb if we can do it. I'll give you a chestnut to show you how much we are alike.

The chestnuts are as follows:

Select id from (select id from table1) a join (select id from table2) b on a.id=b.id where a.id > = 100order by id

First of all, I'm going to execute the subquery, that is, to figure out the intermediate tables an and b (the two tables ab can be understood as the two sheet of excle)

Then I execute the join statement and perform the Cartesian product of the two tables according to id to get all the information about the two tables (if the primary key is unique, it can be understood as the vlookup of excel).

Of course, some information is not what we need, how to filter it, use where statement (equivalent to the filtering function of excel)

Finally, the final result is sorted by order by (corresponding to the sorting function of excel)

Here are a few details that can deepen the cooperation between me and custom scalars

When I select the desired column (select id from table) can be seen as reading a column of id, can also be seen as a row reading id column, each record, the cursor down one grid, when all the data after traversing to present us a column of id. In the second way, it is equivalent to python traversing the array, and you can naturally add some variables to store some data.

A little abstract? For example ~

3, name a few chestnuts

A data table records the hourly turnover of two AB stores. The original figures are as follows.

CREATE TABLE `wk_ test` (`date` varchar (20) NOT NULL COMMENT 'date', `shop` varchar (255) NOT NULL COMMENT 'store', `hour` int (11) NOT NULL COMMENT 'hour', `income` int (11) NOT NULL COMMENT 'revenue', PRIMARY KEY (`date`, `shop`, `hour`) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. How to observe the cumulative turnover of the two stores at a certain time every day?

According to the principle mentioned above, if each record read is equivalent to a cursor going down, and we record and judge the intermediate result with variables in the process of traversing the data, then the above requirements can be achieved. The code is as follows:

Initialization variable set @ cosum:=0; set @ dates:=''; set @ shop:='' Select date,shop,hour,income, @ group_income:=-- determine whether it is the same store on the same day, case when @ dates=a.date and @ shop=a.shop-- accumulate then @ cosum:=@cosum+income-- if not, assign the value of the first hour to the cumulative amount else @ cosum:=a.income end as group_income,-- save the variable @ dates:=a.date that is currently used for judgment. @ shop:=a.shop from (select * from wk_test order by date,shop,hour) a

The results are as follows:

two。 Using custom variables to implement row_number () over windowing mysq does not support windowing functions, but the same effect can be achieved by introducing custom variables. The specific implementation code is as follows:

Set @ row_number:=0; set @ dates:=''; set @ shop:=''; select date,shop,hour,income, @ num:=case when @ dates=a.date and @ shop=a.shop then @ row_number:=@row_number+1 else @ row_number:=1 end as group_income, @ dates:=a.date, @ shop:=a.shop from (select * from wk_test order by date,shop,hour) a

The results are as follows:

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.