mysql - 如何使用来自另一个表的另一行的值来覆盖行值？

Question

我有几个表如下:

product_sets

ID | Value
---------------
A  | original_a
B  | original_b
C  | original_c
D  | original_d

override_sets

ID | Name
----------
X  | Set 1
Y  | Set 2

override_entries

ID | Set ID | Product ID | Override Value
-----------------------------------------
1  | X      | A          | new_a
2  | X      | B          | new_b
3  | Y      | A          | custom_a

我已经尝试过使用多个连接，但我会得到如下结果:

Product ID | Set ID | Final Value
---------------------------------
A          | X      | new_a
B          | X      | new_b
A          | Y      | custom_a

我想要一个 SQL View final_products 产生如下结果:

Product ID | Set ID | Final Value
---------------------------------
A          | X      | new_a
B          | X      | new_b
C          | X      | original_c
D          | X      | original_d
A          | Y      | custom_a
B          | Y      | original_b
C          | Y      | original_c
D          | Y      | original_d

SQL 语句:

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `override_sets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `override_entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `set_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `override_entries_set_id_index` (`set_id`),
  KEY `override_entries_product_id_index` (`product_id`),
  CONSTRAINT `override_entries_set_id_foreign` FOREIGN KEY (`set_id`) REFERENCES `override_sets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `override_entries_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
);

CREATE VIEW `final_products` AS 
    SELECT 
        p.id,
        e.set_id,
        CASE 
            WHEN e.value IS NULL THEN p.value
            ELSE e.value
        END AS final_value
    FROM products AS p
    LEFT OUTER JOIN override_entries AS e ON p.id = e.product_id;

数据集:

INSERT INTO `products` (`id`, `value`)
VALUES
    (1,'original_a'),
    (2,'original_b'),
    (3,'original_c'),
    (4,'original_d');

INSERT INTO `override_sets` (`id`, `name`)
VALUES
    (1,'Set 1'),
    (2,'Set 2');

INSERT INTO `override_entries` (`id`, `set_id`, `product_id`, `value`)
VALUES
    (1,1,1,'new_a'),
    (2,1,2,'new_b'),
    (3,2,1,'custom_a');

Answer 1

使用给定的定义和数据集，下面的方法使用交叉连接(用于连接没有关系的表)和左连接的组合；

select a.id as `Product ID`, b.id as `Set Id` ,
case when c.value is null then a.value else c.value end as `Final Value`
from products a 
cross join override_sets b
left join override_entries c on a.id = c.product_id and b.id = c.set_id
order by b.id, a.id;