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

MySQL uses a digital auxiliary table to implement complex columns and change rows. One day, a netizen In the QQ circle sent a help message:

PropID name Amount3 ingot 22 ledou 3

 

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

PropID name3 Yuanbao 3 Yuanbao 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) t2

In some cases, it is best to 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