如何在 mysql 中重用子查询？

Question

我有以下查询，并且使用相同的子查询 4 ​​次，这影响了我的性能。有没有办法通过仅运行一次并在我需要的其他地方引用它来重用子查询？

下面是代码， 谢谢

SELECT adb_product_type.strproduct_type AS strproduct_type, adb_product_cat.strproduct_cat AS strproduct_cat, adb_product.strproduct AS strproduct, 

CASE 
(SELECT COUNT(DISTINCT adb_campaign_1.campaign_id) 
FROM adb_camp_media AS adb_camp_media_1 
INNER JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id 
WHERE adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND ((adb_campaign_1.start_date >= '2010-01-01' AND adb_campaign_1.start_date <= '2010-01-31') 
OR (adb_campaign_1.end_date >= '2010-01-01' AND adb_campaign_1.end_date <= '2010-01-31')) )
WHEN 0 THEN 'No' ELSE 'Yes'
END AS 'YesNo', 

CASE 
(SELECT COUNT(DISTINCT adb_campaign_1.campaign_id) 
FROM adb_camp_media AS adb_camp_media_1 
INNER JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id 
WHERE adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND ((adb_campaign_1.start_date >= '2010-01-01' AND adb_campaign_1.start_date <= '2010-01-31') 
OR (adb_campaign_1.end_date >= '2010-01-01' AND adb_campaign_1.end_date <= '2010-01-31')) )
WHEN 0 THEN '' ELSE 

(SELECT COUNT(DISTINCT adb_campaign_1.campaign_id) 
FROM adb_camp_media AS adb_camp_media_1 
INNER JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id 
WHERE adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND ((adb_campaign_1.start_date >= '2010-01-01' AND adb_campaign_1.start_date <= '2010-01-31') 
OR (adb_campaign_1.end_date >= '2010-01-01' AND adb_campaign_1.end_date <= '2010-01-31')) )
END AS 'CampaignCount', 

CASE
(SELECT COUNT(DISTINCT adb_campaign_1.campaign_id) 
FROM adb_camp_media AS adb_camp_media_1 
INNER JOIN adb_campaign AS adb_campaign_1 ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id 
WHERE adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
AND ((adb_campaign_1.start_date >= '2010-01-01' AND adb_campaign_1.start_date <= '2010-01-31') 
OR (adb_campaign_1.end_date >= '2010-01-01' AND adb_campaign_1.end_date <= '2010-01-31')) )
WHEN 0 THEN '' ELSE 
 adb_media.name 
END AS mediaName

FROM adb_product_type 
LEFT JOIN adb_product_cat ON adb_product_cat.lngproduct_type = adb_product_type.lngproduct_type 
LEFT JOIN adb_product ON adb_product.lngproduct_cat = adb_product_cat.lngproduct_cat 
LEFT JOIN adb_camp_media ON adb_camp_media.lngproduct = adb_product.lngproduct 
LEFT JOIN adb_media ON adb_media.media_id = adb_camp_media.media_id 
LEFT JOIN adb_camp_media_prod ON adb_camp_media_prod.media_num = adb_camp_media.media_num 
LEFT JOIN adb_campaign ON adb_campaign.campaign_id = adb_camp_media.campaign_id 
WHERE 1=1 AND (adb_campaign.start_date >= '2010-01-01' AND adb_campaign.start_date <= '2010-01-31') 
OR (adb_campaign.end_date >= '2010-01-01' AND adb_campaign.end_date <= '2010-01-31')
OR ( adb_camp_media.campaign_id IS NULL) OR (adb_camp_media_prod.lngproduct IS NULL)

GROUP BY strproduct, mediaName ORDER BY strproduct_type, strproduct_cat, strproduct

Answer 1

我发现它......在 mysql 中你可以使用用户变量，例如。

@campaignCount:=(SELECT 
                  COUNT(DISTINCT adb_campaign_1.campaign_id) 
                  FROM adb_camp_media AS adb_camp_media_1 
                   INNER JOIN adb_campaign AS adb_campaign_1 
                    ON adb_campaign_1.campaign_id = adb_camp_media_1.campaign_id 
                   WHERE adb_camp_media_1.lngproduct = adb_camp_media.lngproduct 
                    AND ((adb_campaign_1.start_date >= '2010-01-01' 
                    AND adb_campaign_1.start_date <= '2010-01-31') 
                    OR (adb_campaign_1.end_date >= '2010-01-01' 
                    AND adb_campaign_1.end_date <= '2010-01-31')) )

Answer 2

您可以提取子查询的结果并将其放入临时表中。然后更新您的查询以使用临时表的内容。