Соедините две таблицы, объедините и транспонируйте результат

Хорошо, скажем, у меня есть две таблицы products и product_options.

структура таблицы products выглядит так:

   +--------------------------------------+
   | p_id | product_name | product_status |
   +--------------------------------------+
   |    1 | Keyboard ABC | PENDING        |
   |    2 | Mouse ABC    | ORDERED        |
   |    3 | Monitor ABC  | ORDERED        |
   +--------------------------------------+

И таблица product_options будет такой:

   +--------------------------------------+
   | po_id | p_id | opt_name | opt_value  +
   +--------------------------------------+
   |    1  |    1 | part_no  | KB-ABC-001 |
   |    2  |    1 | stock    |          0 |
   |    3  |    2 | part_no  | MS-ABC-001 |
   |    4  |    2 | stock    |         14 |
   |    5  |    3 | part_no  | MO-ABC-001 |
   |    6  |    3 | stock    |         10 |
   +--------------------------------------+

Вопрос в том, что я хочу отобразить запрос обеих таблиц и указать конкретные критерии только для продукта, который я заказал. И результат будет примерно таким:

   +------------------------------------------------------------+
   | p_id | product_name | part_no    | stock  | product_status |
   +------------------------------------------------------------+
   |    2 | Mouse ABC    | MS-ABC-001 |     14 | ORDERED        |
   |    3 | Monitor ABC  | MO-ABC-001 |     10 | ORDERED        |
   +------------------------------------------------------------+

Мой SQL-запрос:

   SELECT 
          t.p_id, 
          t.product_name,
          (CASE WHEN t.key_name = 'part_no' THEN t.key_value ELSE NULL END) AS part_no,
          (CASE WHEN t.key_name = 'stock' THEN t.key_value ELSE NULL END) AS stock,
          t.product_status
   FROM
          (
               SELECT 
                      products.p_id as p_id,
                      products.product_name as product_name,
                      products.product_status as product_status,
                      product_options.opt_name as key_name, 
                      product_options.opt_value as key_value
               FROM 
                      product_options Inner Join 
                      products On product_options.p_id = products.p_id
               WHERE
                      (products.product_status = 'ORDERED') AND 
                      (product_options.opt_name = 'part_no' OR product_options.opt_name = 'stock')
              ) as t
   GROUP BY t.p_id

Что у меня есть:

   +------------------------------------------------------------+
   | p_id | product_name | part_no    | stock  | product_status |
   +------------------------------------------------------------+
   |    2 | Mouse ABC    | MS-ABC-001 |   NULL | ORDERED        |
   |    3 | Monitor ABC  | MO-ABC-001 |   NULL | ORDERED        |
   +------------------------------------------------------------+

Но я хочу отображать NULL в поле stock, поскольку данные на product_options имеют:

   +------------------------------------------------------------+
   | p_id | product_name | part_no    | stock  | product_status |
   +------------------------------------------------------------+
   |    2 | Mouse ABC    | MS-ABC-001 |     14 | ORDERED        |
   |    3 | Monitor ABC  | MO-ABC-001 |     10 | ORDERED        |
   +------------------------------------------------------------+

Вопрос в том, как я могу отобразить / объединить две таблицы с таким регистром? Я пытался использовать GROUP_CONCAT, но это не то, что я ищу.

См. SQLFiddle.

Пожалуйста помоги.


mysql transpose inner-join
person agungandika    schedule 22.03.2015    source источник
comment
это может помочь подумать о «запасах», хранящихся в отдельной таблице. Затем вы можете присоединиться к нему. т.е. создать его как подзапрос и присоединиться к нему. Это не самый эффективный способ, но он сделает то, что вы хотите.   -  person Ryan Vincent    schedule 22.03.2015
comment
@RyanVincent да, подзапрос работает как шарм. в следующий раз сделаю это. Спасибо   -  person agungandika    schedule 22.03.2015

Ответы (4)


arrow_upward
2
arrow_downward

Попробуйте этот запрос:

   select p.p_id p_id , p.product_name product_name ,
   (select i.opt_value from product_options i where i.p_id=p.p_id and i.opt_name='part_no') part_no,

   (select i.opt_value from product_options i where i.p_id=p.p_id and i.opt_name='stock') stock,
   p.product_status product_status

   from products p
   where p.product_status='ORDERED';
person Sagar Joon    schedule 22.03.2015

arrow_upward
1
arrow_downward

Вы можете попробовать это:

SELECT 
  p.p_id p_id , 
  p.product_name product_name , 
  po.opt_value_1 AS part_no,
  po.opt_value_2 AS stock, 
  p.product_status product_status
FROM
products p LEFT JOIN (SELECT po1.p_id,
                        po1.opt_name AS opt_name_1,
                        po1.opt_value AS opt_value_1,
                        po2.opt_name AS opt_name_2,
                        po2.opt_value AS opt_value_2
                     FROM product_options po1 
                       LEFT JOIN product_options po2
                       ON po1.p_id = po2.p_id 
                       AND po1.opt_name != po2.opt_name  
                     GROUP BY po1.p_id) AS po
  ON p.p_id = po.p_id
  WHERE p.product_status='ORDERED';

Вот sqlfiddle.

person Beginner    schedule 22.03.2015

arrow_upward
1
arrow_downward

Чтобы получить таблицу результатов, вы можете JOIN вместе два запроса:

SELECT t1.p_id, t1.product_name, t1.part_no, t2.stock, t1.product_status
FROM
(
    SELECT products.p_id, products.product_name, products.product_status, po1.opt_value AS 'part_no'
    FROM products INNER JOIN product_options AS po1
    ON products.p_id = po1.p_id
    WHERE po1.opt_name = 'part_no'
) AS t1
INNER JOIN
(
    SELECT products.p_id, po2.opt_value AS 'stock'
    FROM products INNER JOIN product_options AS po2
    ON products.p_id = po2.p_id
    WHERE po2.opt_name = 'stock'
) AS t2
ON t1.p_id = t2.p_id;

Первый SELECT (t1) получает номер детали для каждого продукта, ограничивая продукты в product_options только теми, у которых opt_name равно 'part_no'. И второй SELECT (t2) получает акции, используя аналогичную стратегию.

person Tim Biegeleisen    schedule 22.03.2015

arrow_upward
1
arrow_downward 

SELECT p.p_id
     , p.product_name
     , MAX(CASE WHEN o.opt_name = 'part_no' THEN o.opt_value END) part_no
     , MAX(CASE WHEN o.opt_name = 'stock' THEN o.opt_value END) stock
     , p.product_status
  FROM products p
  JOIN product_options o
    ON o.p_id = p.p_id
 WHERE p.product_status = 'ordered'
 GROUP
    BY p.p_id;

или, быстрее, но набирать больше ...

SELECT p.p_id
     , p.product_name
     , o1.opt_value part_no
     , o2.opt_value stock
     , p.product_status
  FROM products p
  LEFT
  JOIN product_options o1
    ON o1.p_id = p.p_id
   AND o1.opt_name = 'part_no'
  LEFT
  JOIN product_options o2
    ON o2.p_id = p.p_id
   AND o2.opt_name = 'stock'
 WHERE product_status = 'ordered'
person Strawberry    schedule 22.03.2015