There is a table of Товаров . The table is divided into id , name... , category . Now I have 5 categories. At the same time, there are about 380 products. As one request, get 50 products from each category from the database.

    3 answers 3

    It is necessary to number lines in the context of categories and take those whose numbers are less than 50. In MySQL, this can be done using variables, remembering the category of the previous line when sorting by category. If in the end you need some specific 50 records, and not the first ones, then you need to add the appropriate fields to the sorting.

     select * from ( select *, @num:=if(@grp=category, @num+1, 1) N, -- Текущий номер строки равен -- Номеру из предыдущей +1, если категория совпадает с предыдущей строкой -- Или 1, если категория сменилась @grp:=category -- Запоминаем текущую категорию -- (присвоение происходит после вычисления предыдущего поля, т.е. @num) from table_c, (select @num:=0, @grp:=NULL) x -- Инициализируем переменные order by category ) X where N<=50
    • A good option, but it does not work - Total Pusher
    • one
      @TotalPusher Just checked, it works for me. Actually must work on any version of MySQL. PS corrected one column name because it was tested on a table in which there was no column category - Mike
    • @Mike I don't know what you did but you are cool. I did not know that MySql may not be so clear. Need to pull up - user8978194
    • @ user8978194 Well, in MySQL there are no heaps of useful features, but there are variables that replace them. There is the only feature that decides everything. Variables that with dogs in the beginning remember their state, which they received when processing the previous row of the sample. Thus, the sample is presented as a normal loop that calculates the values ​​of variables line by line - Mike

    Working option:

     (SELECT * FROM goods WHERE `category` = 1 LIMIT 50) UNION ALL (SELECT * FROM goods WHERE `category` = 2 LIMIT 50) UNION ALL (SELECT * FROM goods WHERE `category` = 3 LIMIT 50) UNION ALL (SELECT * FROM goods WHERE `category` = 4 LIMIT 50) UNION ALL (SELECT * FROM goods WHERE `category` = 5 LIMIT 50)

    If a more universal solution is required (fe numbers of categories are not known in advance), then either write a script or create a stored function and access it.

       WITH s AS (SELECT DISTINCT p.category, (SELECT id FROM product WHERE category = p.category ORDER BY id LIMIT 1 OFFSET 50) FROM product p) SELECT * FROM product p JOIN s ON s.category = p.category WHERE p.id < s.id order by p.category;

      PS I’m not sure about the validity of MySql

      • is it mysql? Or something else? - user8978194
      • This request is valid for psql. If there is WITH, AS, LIMIT, OFFSET in MySQL, then everything should be ok - JavaJunior
      • In the 8th version added WITH, you can previously emulate using a stored procedure, see sqlinfo.ru/articles/info/22.html In addition, in MySQL, you can solve the problem using custom variables. - retvizan