MySQL uses a digital auxiliary table to implement complex column-to-row changes

One day, a netizen In the QQ circle sent a help message:

Propid name amount
3 RMB 2
2 ledou 3

Whether an SQL statement can be used as an output is to copy the number of amount records:

Propid name
3. Yuanbao
3. Yuanbao
2 ledou
2 ledou
2 ledou

 

[Note]:

3 RMB 2
Note that the last column is 2, that is, there are two such records to be changed
3. Yuanbao
3. Yuanbao
Remove the column of the number of records and use repeated rows directly. Therefore, in this example, repeat three records, as shown below:

2 ledou
2 ledou
2 ledou

 

 

After studying the problem, I had to change my mind when I used the ordinary group by sum if case when and so on. When I got down to hang, I joined the netizen mr. W mentioned that he provided the idea of a digital auxiliary table.

OK. The SQL statement is as follows:

Select t1.propid, t1.name from TB T1, (select 1 as num Union select 2 as num Union select 3 as num) T2 where t1.amount> = t2.num order by 1;

 

Intermediate, (Select 1 as num Union select 2 as num Union select 3 as num) T2Part

It is recommended that you create another auxiliary table with a single column of num, from 1 to n, just give tens of thousands, and then perform Association queries to facilitate self-join SQL judgment, select does not seem to have the function of generating numbers cyclically. The following table Nums

 

If you see a friend here, there is a better implementation method, please share it.