mysql - MySQL缩短选择查询

嗨，那里的StackOverflow人民！

我需要减少选择语句的子查询，主要是因为要选择的内容太多：

SELECT
    `p`.id
,   (SELECT a.title FROM products_types_attributes a WHERE a.filtername = 'filter_01' AND a.ttv_end IS null AND a.id = p.filter_01) AS filter_01
,   (SELECT a.title FROM products_types_attributes a WHERE a.filtername = 'filter_02' AND a.ttv_end IS null AND a.id = p.filter_02) AS filter_02
,   (SELECT a.title FROM products_types_attributes a WHERE a.filtername = 'filter_03' AND a.ttv_end IS null AND a.id = p.filter_03) AS filter_03
,   (SELECT a.title FROM products_types_attributes a WHERE a.filtername = 'filter_04' AND a.ttv_end IS null AND a.id = p.filter_04) AS filter_04
FROM
    `products` p
LEFT JOIN
    `products_types` t
    ON p.type_id = t.id
    AND t.ttv_end IS null
WHERE
    `p`.`id` = 9754
AND    `p`.`ttv_end` IS null

有50多个过滤器，虽然一一键入是一种方法，但我想知道是否有较短的编码方法。

提前致谢！

最佳答案

SELECT
    `p`.id
,   f1.title AS filter_01
,   f2.title AS filter_02
,   f3.title AS filter_03
,   f4.title AS filter_04
FROM
    `products` p
LEFT JOIN
    `products_types` t
    ON p.type_id = t.id
    AND t.ttv_end IS null
LEFT JOIN products_types_attributes f1 on f1.filtername = 'filter_01' AND a.ttv_end IS null AND a.id = p.filter_01
LEFT JOIN products_types_attributes f2 on f1.filtername = 'filter_02' AND a.ttv_end IS null AND a.id = p.filter_02
LEFT JOIN products_types_attributes f3 on f1.filtername = 'filter_03' AND a.ttv_end IS null AND a.id = p.filter_03
LEFT JOIN products_types_attributes f4 on f1.filtername = 'filter_04' AND a.ttv_end IS null AND a.id = p.filter_04
WHERE
    `p`.`id` = 9754
AND    `p`.`ttv_end` IS null

关于mysql - MySQL缩短选择查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22451734/