MySQL uses numeric auxiliary tables to implement complex column changes and rows

One day, a netizen in a circle on QQ sent a message asking for help:

PropID name Amount
3 Yuanbao 2
2 Ledou 3

Can I use a SQL to write the output, that is, copy the number of items corresponding to Amount? ：

PropID name
3  Yuanbao
3  Yuanbao
2  Ledou
2  Ledou
2  Ledou

[Note]:

3 Yuanbao 2
Note, the last column is 2, which means there is 2 such records will become
3 Yuanbao
3 Yuanbao
. Remove the column of the last number of records and directly use repeated rows to represent it. So according to this example, 2                                                 3           Yuanbao           will be repeated 3 times. The record is as follows:

2 乐豆
2 乐豆
2 乐豆

After some research, I couldn’t figure it out with ordinary group by sum if case when, so I had to change my mind. After getting hung up, I talked to netizen Mr.W, and he provided the idea of ​​​​a digital auxiliary table.

OK, done, the SQL 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;

The middle , (select 1 as num union select 2 as num union select 3 as num ) t2 part

It is best that you build another num single-column auxiliary table, from Increment from 1 to N, just give it tens of thousands, and then the associated query facilitates some self-connected SQL judgments. Select does not seem to have the function of generating numbers in a loop. As shown in the following table nums

If you see friends here and have better implementation methods, please share them.

The above is the content of MySQL using numeric auxiliary tables to implement complex column changes. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!