代码之家 › 专栏 › 技术社区 › 3ashmawy

将重复的子查询转换为联接

query-optimization mysql

3ashmawy · 技术社区 · 14 年前

我有下面的查询,它使用不同的列选择重复一个select子查询,我希望我可以用一种更优雅的方式编写它。请注意,第一个select查询在代码中重复了两次,但使用了不同的列选择。

SET @date1  = '2010-01-06';
SET @date2  = '2010-07-01';
SET @qdate  = '2010-07-01';
SET @period = 'WEEK';
      (SELECT S.date1, 
              S.date2,
              S.period,
              Q.market,
              Q.ticker,
              Q.close * EXP(S.ratio) AS scenario

        FROM portfolio.scenario S , portfolio.quote Q

        WHERE  
              S.date1 >= @date1 AND
              S.date2 <= @date2 AND
              Q.date  = @qdate AND
              S.series = @series AND
              Q.market = S.market AND 
              Q.ticker = S.ticker)

UNION

      (SELECT T2.date1, 
              T2.date2, 
              T2.period, 
              T1.market, 
              T1.ticker, 
              0 AS scenario
      FROM
                  (SELECT DISTINCT
                          Q.market,
                          Q.ticker

                  FROM portfolio.scenario S , portfolio.quote Q

                  WHERE  
                        S.date1 >= @date1 AND
                        S.date2 <= @date2 AND
                        Q.date  = @qdate AND
                        S.series = @series AND
                        Q.market = S.market AND 
                        Q.ticker = S.ticker) AS T1

            JOIN 

                  (SELECT DISTINCT S.date1, S.date2, S.period
                   FROM portfolio.scenario S
                   WHERE S.series = @series AND
                         S.date1 >= @date1 AND
                         S.date2 <= @date2) AS T2

            WHERE     (T2.date1, 
                       T2.date2, 
                       T2.period, 
                       T1.market, 
                       T1.ticker) 

                    NOT IN 

                        (SELECT S.date1, 
                        S.date2,
                        S.period,
                        Q.market,
                        Q.ticker

                        FROM portfolio.scenario S , portfolio.quote Q

                        WHERE  
                              S.date1 >= @date1 AND
                              S.date2 <= @date2 AND
                              Q.date  = @qdate AND
                              S.series = @series AND
                              Q.market = S.market AND 
                              Q.ticker = S.ticker))

1 回复 | 直到 14 年前

Heiko Hatzfeld 14 年前

我懒得在这里重复整个查询,但是有一种简单的方法来替换结果中的字段,方法是使用Case语句。

这将允许您根据变量输出不同的字段。

所以

CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END