确保加入时来自一个表的记录始终覆盖其他表

RedGiant 发表于 Dev

红巨人

我想将五个EAV表组合成一个存储所有产品属性的大表。假设有两部手机，A并且B来自同一系列，称为Nokia 1150。这两款手机共享一些相同的信息，例如价格，品牌，内存。该系列使用绿色塑料盒。但是B手机是特别版。它的外壳是金色的，是黑色的。我想透视表并使用IFNULL或coalesce确保该表model_attr始终被覆盖series_attr

我希望输出是这样的：

MODEL_NAME  SERIES_NAME     PRICE   RAM BRAND   MATERIAL    COLOR
A           Nokia Series    5000    512 Nokia   Plastic     Green
B           Nokia Series    5000    512 Nokia   Gold        Black

我的代码：

Select m.model_name,s.series_name,s.price,s.ram,s.brand,
    GROUP_CONCAT(
     IF(a.attr_name = 'material',a.attr_value, NULL)) AS material,
    GROUP_CONCAT(
     IF(a.attr_name = 'color',a.attr_value, NULL)) AS color
FROM model m INNER JOIN series s 
LEFT JOIN series_attr sa ON sa.series_id = s.series_id
LEFT JOIN model_attr ma ON ma.model_id = m.model_id
LEFT JOIN attr a ON a.attr_id = ma.attr_id
GROUP BY m.model_name

我得到的是：

MODEL_NAME  SERIES_NAME     PRICE   RAM BRAND   MATERIAL    COLOR
A           Nokia Series    5000    512 Nokia   (null)  (null)
B           Nokia Series    5000    512 Nokia   Gold,Gold,Gold,Gold Black,Black,Black,Black

表架构

 CREATE TABLE series
        (`series_id` int, `series_name` varchar(20),`price` int,`ram`int,`brand`varchar(20))
    ;

INSERT INTO series
    (`series_id`,`series_name`,`price`,`ram`,`brand`)
VALUES
    (1,'Nokia Series',5000,512,'Nokia'),
    (2,'Sony Series',2500,1024,'Sony')

;

CREATE TABLE model
    (`model_id` int, `model_name` varchar(20),`series_id` int)
;

INSERT INTO model
    (`model_id`,`model_name`,`series_id`)
VALUES
    (1,'A',1),
    (2, 'B',1),
    (3, 'C',2)
;

CREATE TABLE attr
    (`attr_id` int, `attr_name` varchar(20),`attr_value` varchar(20))
;

INSERT INTO attr
    (`attr_id`,`attr_name`,`attr_value`)
VALUES
    (1, 'material','Gold'),
    (2, 'material','Plastic'),
    (3, 'color','Grey'),
    (4, 'color','Black'),
    (5, 'color','Green')


;

CREATE TABLE series_attr
    (`series_id` int, `attr_id` int )
;
INSERT INTO series_attr
    (`series_id`,`attr_id`)
VALUES
    (1,2),
    (1,5),
    (2,2),
    (2,3)
;



CREATE TABLE model_attr
    (`model_id` int, `attr_id` int,`series_group`int )
;
INSERT INTO model_attr
    (`model_id`,`attr_id`,`series_group`)
VALUES
    (2,1,1),
    (2,4,1)
;

我在table中创建了一个名为series_group的字段model_attr。我希望这会使加入变得容易。任何帮助将不胜感激

戈登·利诺夫

您实际上完成了大部分工作。但是，关键的更改是两次加入属性表，一次用于系列，一次用于模型。然后，您可以分别查看每个属性，然后选择模型（如果存在）或系列：

Select m.model_name, s.series_name, s.price, s.ram, s.brand,
       (case when sum(a.attr_name = 'material') > 0
             then GROUP_CONCAT(DISTINCT IF(a.attr_name = 'material', a.attr_value, NULL))
             else GROUP_CONCAT(DISTINCT IF(saa.attr_name = 'material', saa.attr_value, NULL))
        end) AS material,
       (case when sum(a.attr_name = 'color') > 0
             then GROUP_CONCAT(DISTINCT IF(a.attr_name = 'color', a.attr_value, NULL))
             else GROUP_CONCAT(DISTINCT IF(saa.attr_name = 'color', saa.attr_value, NULL))
        end) AS color
FROM model m INNER JOIN
     series s
     on m.series_id = s.series_id LEFT JOIN
     series_attr sa
     ON sa.series_id = s.series_id LEFT JOIN
     attr saa
     on saa.attr_id = sa.attr_id LEFT JOIN
     model_attr ma
     ON ma.model_id = m.model_id LEFT JOIN
     attr a
     ON a.attr_id = ma.attr_id
GROUP BY m.model_name;