Блог

нужна помощь с sql. Посмотрите ниже на мой фрагмент кода sql.

 create table product
(
    id int auto_increment primary key,
    title text null,
    stock int default 0 not null,
    price float(9,2) default 0.00 not null
);

create table product_property_value
(
    id int auto_increment primary key,
    product_id int not null,
    property_id int not null,
    value varchar(255) null
);

create table property
(
    id int auto_increment primary key,
    code varchar(20) null,
    title varchar(50) null
);
 

У меня есть инструкция для выбора всех строк из этих трех таблиц с красивым выводом.

 SELECT
    title,
    stock,
    price,
    (
        SELECT GROUP_CONCAT(p.title, ': ', pv.value SEPARATOR ', ')
        FROM product_property_value pv
        INNER JOIN property p on pv.property_id = p.id WHERE pv.product_id = product.id
    ) property_values
FROM product;

 

Моя таблица свойств заполнена этими данными:

 insert into property (id, code, title) values (1, 'color', 'Color');
insert into property (id, code, title) values (2, 'width', 'Width');
insert into property (id, code, title) values (3, 'height', 'Height');
 

Моя таблица product_property_value заполнена этими данными (есть один пример для одного продукта, в таблице их много):

 insert into product_property_value (id, product_id, property_id, value) values (4, 2, 1, 'Green');
insert into product_property_value (id, product_id, property_id, value) values (5, 2, 2, 4);
insert into product_property_value (id, product_id, property_id, value) values (6, 2, 3, 4);
 

Итак, главный вопрос заключается в том, как выбирать продукты только с указанными значениями:
Выберите продукты с color = «Red», width = 4 или width = 5 и height = 5. Мне нужно изменить оператор или изменить его, но в любом случае мне нужно сохранить обновленный вывод. Не стесняйтесь отвечать, спасибо.

Если ваша версия MySQL поддерживает боковые производные таблицы, вы можете попробовать использовать это.

 SELECT title, stock, price, property_values
FROM product prod
, LATERAL (
   SELECT ppv.product_id
   , MAX(CASE WHEN prop.code = 'color' THEN ppv.value END) AS color
   , MAX(CASE WHEN prop.code = 'width' THEN ppv.value END) AS width
   , MAX(CASE WHEN prop.code = 'height' THEN ppv.value END) AS height
   , GROUP_CONCAT(prop.title, ': ', ppv.value SEPARATOR ', ') AS property_values
   FROM product_property_value ppv
   LEFT JOIN property prop ON prop.id = ppv.property_id
   WHERE ppv.product_id = prod.id
   GROUP BY ppv.product_id
   HAVING color = 'Red'
      AND width IN (4, 5) 
      AND height = 5
) propies
ORDER BY title;
 

Или просто объедините их все.

 SELECT prod.title, stock, price
, GROUP_CONCAT(prop.title, ': ', ppv.value SEPARATOR ', ') AS property_values
FROM product prod
JOIN product_property_value ppv
  ON ppv.product_id = prod.id
JOIN property prop 
  ON prop.id = ppv.property_id
 AND prop.code IN ('color', 'width', 'height') 
GROUP BY prod.id, prod.title, stock, price
HAVING COUNT(CASE 
             WHEN prop.code = 'color' AND ppv.value = 'Red'
             THEN ppv.id END) > 0
   AND COUNT(CASE 
             WHEN prop.code = 'width' AND ppv.value IN (4, 5) 
             THEN ppv.id END) > 0
   AND COUNT(CASE 
             WHEN prop.code = 'height' AND ppv.value = 5
             THEN ppv.id END) > 0
ORDER BY prod.title;
 

Демонстрация в db <>скрипка здесь

Я думаю, что в вашем sql-операторе должно быть предложение where. Один из способов сделать это:

 SELECT
    title,
    stock,
    price,
    (
        SELECT GROUP_CONCAT(p.title, ': ', pv.value SEPARATOR ', ')
        FROM product_property_value pv
        INNER JOIN property p on pv.property_id = p.id WHERE pv.product_id = product.id
    ) property_values
FROM product WHERE property_values='Color: Red, Width: 4, Height: 5' OR property_values='Color: Red, Width: 5, Height: 5';