Geek asks and answers

MySQL how to repeat one row x times

I have a query that outputs address data:

SELECT ordernumber
  , article_description
  , article_size_description
  , concat(NumberPerBox,' pieces') as contents
  , NumberOrdered
FROM customerorder
WHERE customerorder.id = 1;

I would like the line above to be output NumberOrders(e.g. 50,000) divided by NumberPerBoxe.g. 2000 = 25 times.

Is there an SQL query that can do this, I don't mind using temporary tables to join if necessary.

I checked the previous questions, however the closest one: it
should be possible in mysql to repeat the same result
Only gave answers that give a fixed number of rows, and I need it to be dynamic depending on the value ( NumberOrdered div NumberPerBox).

As a result, I want:

Boxnr   Ordernr        as_description   contents   NumberOrdered
------+--------------+----------------+-----------+---------------
  1   | CORDO1245    | Carrying bags  | 2,000 pcs | 50,000
  2   | CORDO1245    | Carrying bags  | 2,000 pcs | 50,000
....
  25  | CORDO1245    | Carrying bags  | 2,000 pcs | 50,000
+3
source share
1 answer

-, , SQL Server, .
-, , , , .

, . , ( "" ), PK 1 n. , Numbers , , - , , ..

, :

SELECT
   IV.number as Boxnr
  ,ordernumber  
  ,article_description
  ,article_size_description
  ,concat(NumberPerBox,' pieces') as contents
  ,NumberOrdered
FROM
  customerorder
  INNER JOIN (
    SELECT
       Numbers.number
      ,customerorder.ordernumber
      ,customerorder.NumberPerBox
    FROM
      Numbers
      INNER JOIN customerorder
        ON Numbers.number BETWEEN 1 AND customerorder.NumberOrdered / customerorder.NumberPerBox
    WHERE
      customerorder.id = 1
    ) AS IV
    ON customerorder.ordernumber = IV.ordernumber

, SQL Server. http://www.sqlservercentral.com/articles/Advanced+Querying/2547/ ( ). " SQL" .

+1

More articles:


All Articles