SQL query that generates rows depending on quantity

Question

SQL query that generates rows depending on quantity

Navigation

#1 by (2 votes)

2

I have a product table in MySQL, which stores the barcode and the stock quantity of a product.

What I need is to generate an SQL query so that the rows can be repeated with certain information of a single product depending on the amount that it has in Stock.

They would be fictitious rows. For example, if I have 35 products in stock, the query should return me 35 times the bar code of the product as a result.

The product table has the following structure:

id_prod    codbar_prod        des_prod     pre_com     stock_act
  1       27478255368371    ASDFADSFADFS    50000         10
  2       27478255368372    ASDESCRIPCION   150000        116

If for example I need to know the barcode of the product 1. I would make a query in the following way:

SELECT codbar_prod FROM productos WHERE id_prod=1;

Return the following:

codbar_prod
--------------
27478255368371

This way it works, but what I would need is that said query result is repeated taking into account the quantity in stock in this case; would be for example a result similar to the following:

codbar_prod
--------------
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371
27478255368371

mysql

asked by Angel 11.01.2017 в 17:40

source

1 answer

Bootstrap table - reload records according to ajax json object How to clean a data to take another one in jquery?

score 2 · Accepted Answer

@cnbandicoot is right, you duplicate it in your application, in case you do not want to do it that way, what you should do is generate views only with numbers, in the following way

-- Generas 16
CREATE VIEW generator_16
AS SELECT 0 n 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   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;
-- Generas 256
CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;
-- Generas 4096
CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;
-- Generas 65536
CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;
-- Generas 1048576
CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Retrieved from the following link link

When making your inquiry

SELECT codbar_prod
FROM productos p
INNER JOIN generator_64k g ON g.n BETWEEN 1 AND p.stock_act
WHERE id_prod=1