php – MySql |方面的搜索更改

也许你会帮我用我的SQL查询.我的问题是基于另一个问题： Mysql | Faceted search
一切都是一样的,我需要相同的结果,但表格有点不一样.我无法构造我的查询.请看这个 sql fiddle：

我的桌面结构：

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,'New car',(4,'Old car',4);

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

INSERT INTO fields
    (`id`,`field_name`)
VALUES
    (1,'Make'),'Model'),'Color'),'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,'3Series'),3,'White'),4,'Coupe'),5,'Black'),(6,(7,'2Series'),(8,(9,(10,'Grey'),(11,'Honda'),(12,'Civic'),(13,'Red'),(14,'Sedan'),(15,'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),2),4),5),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 |

据我了解你的问题,这应该是你想要的查询：

SELECT field_name,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子句.

请查看我的查询结果.

原文链接：https://www.f2er.com/php/139383.html

php – MySql |方面的搜索更改

猜你在找的PHP相关文章