Как перевести вертикальную таблицу в горизонтальную?

#mysql #sql

Что имеется?
Имеется таблица такого вида

+----+--------------+-------+--------------+
| id | product_name | count | warehouse_id |
+----+--------------+-------+--------------+
|  1 | Puperproduct |    10 |            1 |
|  2 | Puperproduct |    15 |            2 |
|  3 | Puperproduct |    12 |            3 |
+----+--------------+-------+--------------+


Что нужно получить?
Нужно получить макс и мин количество товаров с ихними складами.

+-----+---------------+-----------+------------------+-----------+------------------+
| id  | product_name  | min_count | min_warehouse_id | max_count | max_warehouse_id |
+-----+---------------+-----------+------------------+-----------+------------------+
|  1  | Puperproduct  |        10 |                1 |        15 |                2 |
+-----+---------------+-----------+------------------+-----------+------------------+


Что у меня получилось?
Получилось:

select 
    id
    , product_name
    , min(count) as min_count
    , max(count) as max_count
from table
group by product_name

+----+--------------+-----------+-----------+
| id | product_name | min_count | max_count |
+----+--------------+-----------+-----------+
|  1 | Puperproduct |        10 |        15 |
+----+--------------+-----------+-----------+


Как взять ид складов? Количество записей около 800К. Mysql 5.5
    

Ответы

Ответ 1

Собираем в одну строку count дополненный, скажем, до 10 символов и соответствующий
ему warehouse_id, берем от этой строки min/max и потом из этого вырезаем склад обратно
и преобразуем в числовой вид:

select id, product_name,
       max(count) as max_count, min(count) as min_count,
       substr(min(concat(lpad(count,10,'0'),warehouse_id)),11)+0 min_warehouse_id,
       substr(max(concat(lpad(count,10,'0'),warehouse_id)),11)+0 max_warehouse_id
  from table
 group by product_name


Ответ 2

Можно поступить следующим образом

SELECT
  gr.id AS id
  , gr.product_name AS product_name
  , gr.min_count AS min_count
  , min.warehouse_id AS min_warehouse_id
  , gr.max_count AS max_count
  , max.warehouse_id AS max_warehouse_id
FROM
  (SELECT
      id
      , product_name
      , min(count) AS min_count
      , max(count) AS max_count
  FROM
    table AS main
  GROUP BY
    product_name) AS gr
LEFT JOIN
  table AS min
ON
  gr.product_name = min.product_name AND
  gr.min_count = min.count
LEFT JOIN
  table AS max
ON
  gr.product_name = max.product_name AND
  gr.max_count = max.count;


Однако лучше посмотреть анализатором на продуктовой базе, не будет ли несколько запросов
дешевле.


Ответ 3

А если просто select * from table order by count desc использовать? Тогда "сверху"
будет макс кол-во, а "снизу" - мин.


Ответ 4

Может быть по-идиотски и очень не оптимально (Впрочем, этого в условии не было),
но вроде бы работает:

SELECT tmp.product_name,
 tmp.min_count,
 t2.warehouse_id as min_warehouse_id,
        tmp.max_count,
         t3.warehouse_id as max_warehouse_id 
FROM (
SELECT 
     t1.id
    , t1.product_name
    , MIN(COUNT) AS min_count
    , MAX(COUNT) AS max_count
FROM test1 t1
GROUP BY t1.product_name) tmp
JOIN test1 t2 ON tmp.product_name = t2.product_name AND tmp.min_count = t2.count
JOIN test1 t3 ON tmp.product_name = t3.product_name AND tmp.max_count = t3.count


Ответ 5

Почему никто не вспоминает про переменные в MySQL?
Вариант от Mike будет работать вероятно быстрее, но всё же предложу альтернативу:

SELECT id, product_name, count, warehouse_id, 
  CASE WHEN count = @I THEN 'MIN' ELSE 'MAX' END row_type
FROM(
  SELECT *
    ,@I := CASE WHEN @I=0 THEN @I ELSE count END min_count 
    ,@J := CASE WHEN @J>count AND @J>=0 THEN @J ELSE count END max_count 
  FROM thetable, 
    (SELECT @I := -1, @J:= -1)T
)T
WHERE count IN (@I, @J)


Тут выведутся все склады с максимальным и минимальным значением поля count.
На результат можно посмотреть здесь:

ссылка на http://sqlfiddle.com/