MySql | Граничный поиск изменен - программирование

MySql | Граничный поиск изменен

Возможно, вы поможете мне с моим sql-запросом. Мой вопрос основан на другом вопросе: Mysql | Графический поиск Все одно и то же, и мне нужен тот же результат, но таблицы немного разные. Я не могу построить свой запрос. Пожалуйста, взгляните на это sql скрипта:

Моя структура таблицы:

CREATE TABLE products
    (`id` int, `description` varchar(9), `user_id` int);

INSERT INTO products
    (`id`, `description`, `user_id`)
VALUES
    (1, 'my car', 3),
    (2, 'dream car', 3),
    (3, 'New car', 3),
    (4, 'Old car', 4);

CREATE TABLE fields
    (`id` int, `field_name` varchar(14)); /*meta_name*/

INSERT INTO fields
    (`id`, `field_name`)
VALUES
    (1, 'Make'),
    (2, 'Model'),
    (3, 'Color'),
    (4, 'Car Type'),
    (5, 'Interior Color');

CREATE TABLE fields_values
    (`id` int, `field_id` int, `field_value` varchar(7)); /*meta_value*/

INSERT INTO fields_values
    (`id`, `field_id`, `field_value`)
VALUES
    (1, 1, 'BMW'),
    (2, 2, '3Series'),
    (3, 3, 'White'),
    (4, 4, 'Coupe'),
    (5, 5, 'Black'),
    (6, 1, 'BMW'),
    (7, 2, '2Series'),
    (8, 3, 'Black'),
    (9, 4, 'Coupe'),
    (10, 5, 'Grey'),
    (11, 1, 'Honda'),
    (12, 2, 'Civic'),
    (13, 3, 'Red'),
    (14, 4, 'Sedan'),
    (15, 5, 'Black');

CREATE TABLE products2fields_values
    (`id` int, `product_id` int, `field_value_id` int);
INSERT INTO products2fields_values
    (`id`, `product_id`, `field_value_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4),
    (5, 1, 5),
    (6, 2, 1),
    (7, 2, 2),
    (8, 2, 3),
    (9, 2, 4),
    (10, 2, 5),
    (11, 3, 1),
    (12, 3, 2),
    (13, 3, 3),
    (14, 3, 4),
    (15, 3, 5);

И мой неправильный запрос:

SELECT field_name, field_value, COUNT(DISTINCT pid) count
FROM fields ft 
JOIN fields_values fvt
    ON fvt.field_id = ft.id

JOIN products2fields_values p2fv
    ON p2fv.field_value_id = fvt.id

LEFT JOIN (
    SELECT p.id pid
    FROM products p   
    JOIN products2fields_values p2fv
        ON p2fv.product_id = p.id
    JOIN fields_values fvt
        ON fvt.id = p2fv.field_value_id
    JOIN fields ft
        ON ft.id = fvt.field_id
    GROUP BY p.id
    HAVING MAX(ft.id = 1 AND p2fv.field_value_id = 1) = 1
       AND MAX(ft.id = 4 AND p2fv.field_value_id = 4) = 1
)

LJ ON p2fv.product_id = LJ.pid
GROUP BY field_name, field_value;

Я пытаюсь получить результат:

|      field_name| field_value| count |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     0 |
|          Color |      Black |     1 |
|          Color |        Red |     0 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     0 |
|          Model |    2Series |     0 |
|          Model |    3Series |     1 |
|          Model |      Civic |     0 |
4b9b3361

ОТВЕТЫ

Ответ 1

Насколько я понимаю ваш вопрос, это должен быть запрос, который вы хотите:

SELECT field_name, field_value, COUNT(val.id) as count
FROM fields ft 
JOIN fields_values fvt
ON fvt.field_id = ft.id
LEFT JOIN products2fields_values val
ON val.field_value_id = fvt.id 
GROUP BY  field_name, field_value;

Я не знаю, почему вы присоединяетесь к своей таблице продуктов, потому что это не кажется необходимым для ваших результатов. И я не понимаю, почему вы внедрили свое странное предложение HAVING.

Пожалуйста, посмотрите результаты моего запроса.

Ответ 2

Вам нужно скорректировать данные для столбца field_value_id в таблице products2fields_values.

С текущими данными отношения в таблице нарушены, и мы не можем получить ожидаемые результаты с теми значениями, которые там сидят.

Вставка должна быть следующей:

INSERT INTO products2fields_values
    (id, product_id, field_value_id)
VALUES
    (1,1, 1),
    (2,1, 2),
    (3,1, 3),
    (4,1, 4),
    (5,1, 5),
    (6,2, 6),
    (7,2, 7),
    (8,2, 8),
    (9,2, 9),
    (10,2, 10),
    (11,3, 11),
    (12,3, 12),
    (13,3, 13),
    (14,3, 14),
    (15,3, 15);

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

SELECT 
    field_name, field_value, COUNT(val.id) as count
FROM 
    fields ft 
INNER JOIN 
    fields_values fvt ON fvt.field_id = ft.id
LEFT JOIN 
    products2fields_values val ON val.field_value_id = fvt.id
GROUP BY field_name, field_value;

Здесь вы можете увидеть результаты

Ответ 3

Мне удалось найти решение для этого в моем экземпляре SQL Server 2014 Развернуть таблицы в новую таблицу и использовать это с помощью некоторых Unions, как показано ниже:

Declare @Make varchar(7)
Declare @CarType varchar(7)

Create table #CarDetails (CarId int, Make varchar(7), Model varchar(7), Colour varchar(7), CarType varchar(7), InteriorColour varchar(7))
Insert into #CarDetails
Select CarId, [1] as Make, [2] Model, [3] Colour, [4] CarType, [5] InteriorColour
from
(Select ROW_NUMBER() over (partition by field_id order by id asc) CarId, field_id, field_value
from fields_values) a
PIVOT
(Max(field_value) for field_id in ([1],[2],[3],[4],[5])) as pvt
order by pvt.CarId

Set @Make = 'BMW'
Set @CarType = 'Coupe'

Select 'Car Type' field_name, CarType field_value, Count(CarType) [count]
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by CarType
UNION
Select 'Colour', Colour, Count(Colour)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Colour
UNION
Select 'Interior Colour', InteriorColour, Count(InteriorColour)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by InteriorColour
UNION
Select 'Make', Make, Count(Make)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Make
UNION
Select 'Model', Model, Count(Model)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Model

Однако это для MySQL, а MySQL не имеет функции RowNumber или функции Pivot, поэтому я попытался заставить ее работать но, к сожалению, еще не удалось проверить его, поскольку SQLFiddle испытывает проблемы. Надеюсь, это сработает:

Set @Make = 'BMW'
Set @CarType = 'Coupe'

Create temporary table CarDetails (CarId int, Make varchar(7), Model varchar(7), Colour varchar(7), CarType varchar(7), InteriorColour varchar(7));
Insert into CarDetails (CarId, Make)
Select CarId, field_value
from
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 1) a;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 2) a on a.CarId = c.CarId
Set c.Model = a.field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 3) a on a.CarId = c.CarId
Set Colour = a.field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 4) a on a.CarId = c.CarId
Set CarType = field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 5) a on a.CarId = c.CarId
Set InteriorColour = field_value;

Select 'Car Type' field_name, CarType field_value, Count(CarType) count
from CarDetails
Where Make = @Make and CarType = @CarType
Group by CarType
UNION
Select 'Colour', Colour, Count(Colour)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Colour
UNION
Select 'Interior Colour', InteriorColour, Count(InteriorColour)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by InteriorColour
UNION
Select 'Make', Make, Count(Make)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Make
UNION
Select 'Model', Model, Count(Model)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Model

** Обновление ** SQLFiddle возвращается и здесь он работает: http://www.sqlfiddle.com/#!9/aa769/4/2