时间戳

首页 > 解决方案 > 如何连接两个表但不重复mysql中的行

mysql - 如何连接两个表但不重复mysql中的行

问题描述

我面临一个获取数据的问题。我是Mysql的新手。首先我将展示我的表结构

order_products
id  user_id product_id  product_name
1    1        10           Jacket1
2    1        10           Jacket2

order_products_sizes
id    order_product_id  size    qty
1          1             S      56
2          1             M      36
3          1             XL     36
4          1             2XL    56
5          2             S      32
6          2             M      28
7          2             XL     28
8          2             2XL    32
9          2             3XL    69

 My expected Output:-
product_name     S    M   XL  2XL  3XL
  JACKET1       56   36  36   56 
  JACKET2       32   28  28   32    69 

 for first row 3xl would be empty beacuse there is no size available in order_product_sizes

实际上我正在使用连接，但是当我使用连接时，行会重复，因为连接两个表是连接的实际行为。到目前为止我已经尝试过：-

SELECT order_products.product_name,
CASE 
WHEN order_product_sizes.order_product_id = order_products.id AND 
order_product_sizes.size = 'L' THEN  order_product_sizes.qty    
END AS L
from order_products
join 
order_product_sizes
on order_products_sizes.order_product_id = order_products.id;

标签： mysqlsqljoin

解决方案

您可以在下面尝试 - 使用条件聚合

SELECT order_products.product_name,
max(CASE 
WHEN 
order_product_sizes.size = 'S' THEN  order_product_sizes.qty    
END) AS S,
max(CASE 
WHEN 
order_product_sizes.size = 'M' THEN  order_product_sizes.qty    
END) AS M,
max(CASE 
WHEN 
order_product_sizes.size = 'XL' THEN  order_product_sizes.qty    
END) AS XL,
max(CASE 
WHEN 
order_product_sizes.size = '2XL' THEN  order_product_sizes.qty    
END) AS '2XL',
max(CASE 
WHEN 
order_product_sizes.size = '3XL' THEN  order_product_sizes.qty    
END) AS '3XL'
from order_products join order_products_sizes
on order_products_sizes.order_product_id = order_products.id
group by order_products_sizes.order_product_id

推荐阅读