MySQL缩短选择查询

Question

你好，StackOverflow人民！

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

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+过滤器，虽然一个一个地输入它们，我想知道是否有一个更短的方法来编码它。

提前感谢！

Answer 1

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