html - 将所有选择不同查询合并为一个

Question

这是一个example Fiddle

我在一个页面上有超过 30 个搜索过滤器(我读到它称为分面搜索， similar example 来自 Vitacost)。在每次页面加载时，每个过滤器都会接受一个查询(现在大约有 50 个查询以及其他类型的查询)，以根据用户的搜索结果更新过滤器选项。我有类似的 SELECT DISTINCT 查询，如下所示

SELECT DISTINCT(Category) FROM `Toy` 
    WHERE (Material LIKE '%Steel%')
    AND Material <> ''
    ORDER BY Material

SELECT DISTINCT(Manufacturer) FROM `Toy` 
    WHERE (Material LIKE '%Steel%')
    AND Manufacturer <> ''
    ORDER BY Manufacturer


SELECT DISTINCT(Rating) FROM `Toy` 
    WHERE (Material LIKE '%Steel%')
    AND Rating <> ''
    ORDER BY Rating

我想知道是否有任何提高性能或至少减少查询的空间。我尝试过JOIN，但没有看到太多改进，结果是错误的。有人有什么想法或建议吗？

    SELECT * 
    FROM (

    SELECT DISTINCT(Category),Material FROM `Toy` 
        WHERE (Material LIKE '%Steel%')
        AND Material <> ''
        ORDER BY Material

    )TAB1
    JOIN (

      SELECT DISTINCT(Manufacturer),Material FROM `Toy` 
        WHERE (Material LIKE '%Steel%')
        AND Manufacturer <> ''
        ORDER BY Manufacturer
    )TAB2 ON TAB1.Material = TAB2.Material
    JOIN (
            SELECT DISTINCT(Rating),Material FROM `Toy` 
        WHERE (Material LIKE '%Steel%')
        AND Rating <> ''
        ORDER BY Rating
   )TAB3 ON TAB2.Material = TAB3.Material
GROUP BY TAB2.Material

表:

CREATE TABLE Toy
    (`Toyid` int, `Toy` varchar(20),`Category` varchar(30),`Manufacturer` varchar(3),`Rating` varchar(10),`Material` varchar(20))
;

INSERT INTO Toy
    (`Toyid`,`Toy`,`Category`,`Manufacturer`,`Rating`,`Material`)
VALUES
    (1, 'ToyA','Outdoor','AAA','5','Plastic'),
    (2, 'ToyB','Doll','AAA','5','Gold'),
    (3, 'ToyC','Brainteaser','AAA','4','Metal'),
    (4, 'ToyD','Educational','CCC','5','PVD Steel'),
    (5, 'ToyE','Outdoor','DDD','3','Plastic'),
    (6, 'ToyF','Figure','EEE','3','Steel'),
    (7, 'ToyG','Creative','TTT','2','Stainless Steel'),
    (8, 'ToyH','Doll','DDD','2','Steel'),
    (9, 'ToyI','Outdoor','TTT','5','Stainless Steel'),
    (10, 'ToyJ','Brainteaser','DDD','2','PVD GOLD'),
    (11, 'ToyK','Creative','CCC','4','Rose Gold'),
    (12, 'ToyL','Figure','KKK','2','Gold')
;

我的预期输出:

CATEGORY

Educational
Creative
Outdoor
Figure
Doll

RATING
2
3 
5

MANUFACTURER
CCC
DDD
EEE
TTT

所以我想找到一种解决方案将它们加入到一个输出中。

Answer 1

你可以尝试这样的事情:-

SELECT DISTINCT Category, Material, Manufacturer, Rating
FROM Toy
WHERE Material LIKE '%Steel%'
AND  Material <> ''
AND Manufacturer <> ''
AND Rating <> ''
ORDER BY Material, Manufacturer, Rating;

这可能对你有帮助。