mysql用另一张表覆盖_mysql – 如何使用源自另一个表的另一行中的值来覆盖行值？...

我有几张表如下：

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视图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');