根据列中的整数值重复结果中的行 [英] Repeat rows in the result based on an integer value in column

问题描述

我有下表.

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

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

I want to select the records such that it repeat Rows N time according to quantity column value.

所以我需要以下结果

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

推荐答案

在参考了有关如何在mysql中生成序列的答案之后，我能够为我的问题提出解决方案.这是链接.

I was able to come up with a solution for my problem after referring an answer for how to generate series in mysql. Here is the link.

SELECT
  sal.id,
  1 as quantity, sal.quantity as originalQty,
  sal.price_charged/sal.quantity
FROM
  (SELECT
     @num := @num + 1 AS count
   FROM
     sales, -- this can be any table but it should have row count 
            -- more than what we expect the max value of Sales.quantity column
     (SELECT @num := 0) num
   LIMIT
     100) ctr
  JOIN sales sal
    ON sal.quantity >= ctr.count
    order by id;

这篇关于根据列中的整数值重复结果中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！