Блог

У меня есть несколько таблиц следующим образом:

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

Используя определение и набор данных, приведенный ниже метод использует комбинацию перекрестного соединения (для объединения таблиц без связи) и левого соединения;

 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;
  

Создайте инструкции для вашего набора данных:

 create table Product as
select * from (select 'A' ID, 'original_a' Value
union 
select 'B' ID, 'original_b'
union 
select 'C' ID, 'original_c'
union 
select 'D' ID, 'original_d') Product;

create table Override_Set as
select * from (select 'X' ID, 'Set 1' Name
union select 'Y', 'Set 2') Override_Set;

create table Override_Entry as
select * from (select 'X' `Set ID`, 'A' `Product ID`, 'new_a' `Override_Value`
union select 'X' `Set ID`, 'B' `Product ID`, 'new_b' `Override_Value`
union select 'Y' `Set ID`, 'A' `Product ID`, 'custom_a' `Override_Value`) Override_Entry;
  

Это даст вам желаемый результат:

 select p.ID `Product ID`, os.ID `Set ID`,
case when (select 1 from Override_Entry oe where concat(oe.`Set ID`,oe.`Product ID`) = concat(os.`ID`,p.`ID`)) = 1 then
          (select `Override_Value` from Override_Entry oe where concat(oe.`Set ID`,oe.`Product ID`) = concat(os.`ID`,p.`ID`) limit 1)
     else p.`Value` 
end desired_result
from Product p, Override_Set os order by 2, 1;
  

Возможно, вы захотите исправить производительность при работе с огромным набором данных:

  ---- -------------------- ------- ------ --------------- ------ --------- ------ ------ --------------------------------------- 
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                 |
 ---- -------------------- ------- ------ --------------- ------ --------- ------ ------ --------------------------------------- 
|  1 | PRIMARY            | os    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary; Using filesort       |
|  1 | PRIMARY            | p     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using join buffer (Block Nested Loop) |
|  3 | DEPENDENT SUBQUERY | oe    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where                           |
|  2 | DEPENDENT SUBQUERY | oe    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where                           |
 ---- -------------------- ------- ------ --------------- ------ --------- ------ ------ --------------------------------------- 
  

PS — для более быстрого разрешения было бы здорово, если бы вы включили инструкции create для наборов данных.