mysql - 根据列中的整数值重复结果中的行

Question

我有下表。

Sales:
id      quantity    price_charged
------------------------------
101         2           100
102         3           300
103         1           120

我想选择记录，使其根据数量列值重复行 N 次。

所以我需要以下结果

id    quantity    price_charged
--------------------------------
101     1          50
101     1          50
102     1          100
102     1          100
102     1          100
103     1          120

Answer 1

我认为，最好不要使用查询(SQL) 来解析。有一定的生成特征，但性能较差。

你必须改变你的模型(总是存储 1 个数量)，或者在后端处理它(java/c/stb.)

Select id, 
       1 as quantity, 
       price_charged 
from table_name t
JOIN 
(SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5) counter
ON (counter.n<=t.quantity)

连接的子查询重复从 0 到 99999 的数字，它是数量的最大值。连接重复计数器 0 ... quantity-1 值。