连接两个表，合并并转置结果 [英] Join two tables, merge and transpose the result

问题描述

好吧，假设我有两个表products和product_options.

Ok, say i have two tables products and 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 |
   +--------------------------------------+

问题是，我想同时显示两个表查询，并且对仅订购的产品具有特定条件.结果如下:

The question is, I want to display both tables query and has specific criteria for product that I ordered only. And the result be like:

   +------------------------------------------------------------+
   | 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        |
   +------------------------------------------------------------+

但是，我要在stock字段上显示NULL，因为product_options上的数据有:

But, what I want is to display NULL at stock field as from data on product_options has:

   +------------------------------------------------------------+
   | 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，但这不是我要搜索的内容.

The question is, how can I display / merge two tables with case like this? I tried to use GROUP_CONCAT, but that is not what I am searching for.

请参见 SQLFiddle

请帮助.

推荐答案

尝试以下查询:

   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';

这篇关于连接两个表，合并并转置结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！