这个sql语句可否简化?

Question

SELECT     B.StemNumber, B.ButtNumber, B.StemNumber - B.ButtNumber AS HeadNumber, B.OrderNumber, B.StemNumber - B.OrderNumber AS NormalNumber, 
                      ISNULL(A.Number, 0) AS ValueNumber, ISNULL(A.Length, 0) AS Avg_Length, B.StartTime
FROM         (SELECT     COUNT(Length) AS Number, AVG(Length) AS Length, StartTime
                       FROM          (SELECT     MIN(production.OperateInput.EndLgth) AS Length, CONVERT(datetime, CONVERT(varchar(10), production.Stem.StartTime, 120)) 
                                                                      AS StartTime
                                               FROM          production.OperateInput INNER JOIN
                                                                      production.Stem ON production.OperateInput.StemID = production.Stem.StemID
                                               WHERE      (production.OperateInput.AttributeCode = 'p') OR
                                                                      (production.OperateInput.AttributeCode = 'v')
                                               GROUP BY production.OperateInput.StemID, CONVERT(datetime, CONVERT(varchar(10), production.Stem.StartTime, 120))) AS CodePV
                       GROUP BY StartTime) AS A RIGHT OUTER JOIN
                          (SELECT     ISNULL(C.OrderNumber, 0) AS OrderNumber, ISNULL(D.HeadNumber, 0) AS ButtNumber, A_1.StemNumber, A_1.StartTime
                            FROM          (SELECT     COUNT(StemID) AS StemNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                                                    FROM          production.Stem AS Stem_3
                                                    GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS A_1 LEFT OUTER JOIN
                                                       (SELECT     COUNT(StemID) AS HeadNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                                                         FROM          production.Stem AS Stem_1
                                                         WHERE      (ButtStem = '1')
                                                         GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS D ON A_1.StartTime = D.StartTime LEFT OUTER JOIN
                                                       (SELECT     COUNT(StemID) AS OrderNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                                                         FROM          production.Stem AS Stem_2
                                                         WHERE      (StemDetails = '1')
                                                         GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS C ON A_1.StartTime = C.StartTime) AS B ON A.StartTime = B.StartTime

Answer 1

我先帮整理结构,烙个记,有空再看.
SELECT  B.StemNumber, B.ButtNumber, B.StemNumber - B.ButtNumber AS HeadNumber, B.OrderNumber, B.StemNumber - B.OrderNumber AS NormalNumber, 
        ISNULL(A.Number, 0) AS ValueNumber,ISNULL(A.Length, 0) AS Avg_Length, B.StartTime
FROM   (SELECT COUNT(Length) AS Number, AVG(Length) AS Length, StartTime
        FROM  (SELECT MIN(production.OperateInput.EndLgth) AS Length, CONVERT(datetime, CONVERT(varchar(10), production.Stem.StartTime, 120))                                                                     AS StartTime
               FROM  production.OperateInput 
                 INNER JOIN production.Stem ON production.OperateInput.StemID = production.Stem.StemID
               WHERE  production.OperateInput.AttributeCode in( 'p', 'v')
               GROUP BY production.OperateInput.StemID, CONVERT(datetime, CONVERT(varchar(10), production.Stem.StartTime, 120))) AS CodePV
        GROUP BY StartTime) AS A 
  RIGHT OUTER JOIN
        (SELECT  ISNULL(C.OrderNumber, 0) AS OrderNumber, ISNULL(D.HeadNumber, 0) AS ButtNumber, A_1.StemNumber, A_1.StartTime
         FROM  (SELECT COUNT(StemID) AS StemNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                FROM production.Stem AS Stem_3
                GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS A_1
                  LEFT OUTER JOIN
                (SELECT     COUNT(StemID) AS HeadNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                 FROM   production.Stem AS Stem_1
                 WHERE   (ButtStem = '1')
                 GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS D 
              ON A_1.StartTime = D.StartTime 
            LEFT OUTER JOIN
                (SELECT     COUNT(StemID) AS OrderNumber, CONVERT(datetime, CONVERT(varchar(10), StartTime, 120)) AS StartTime
                 FROM          production.Stem AS Stem_2
                 WHERE      (StemDetails = '1')
                 GROUP BY CONVERT(datetime, CONVERT(varchar(10), StartTime, 120))) AS C 
              ON A_1.StartTime = C.StartTime) AS B 
    ON A.StartTime = B.StartTime

Answer 2

格式化一下：
SELECT B.StemNumber
       ,B.ButtNumber
       ,B.StemNumber - B.ButtNumber AS HeadNumber
       ,B.OrderNumber
       ,B.StemNumber - B.OrderNumber AS NormalNumber
       ,ISNULL(A.Number,0) AS ValueNumber
       ,ISNULL(A.Length,0) AS Avg_Length
       ,B.StartTime
FROM   (SELECT   COUNT(Length) AS Number
                 ,AVG(Length) AS Length
                 ,StartTime
        FROM     (SELECT   MIN(production.OperateInput.EndLgth) AS Length
                           ,CONVERT(datetime,CONVERT(varchar(10),production.Stem.StartTime,120)) AS StartTime
                  FROM     production.OperateInput
                           INNER JOIN production.Stem
                             ON production.OperateInput.StemID = production.Stem.StemID
                  WHERE    (production.OperateInput.AttributeCode = 'p')
                   OR (production.OperateInput.AttributeCode = 'v')
                  GROUP BY production.OperateInput.StemID,CONVERT(datetime,CONVERT(varchar(10),production.Stem.StartTime,120))) AS CodePV
        GROUP BY StartTime) AS A
       RIGHT OUTER JOIN (SELECT ISNULL(C.OrderNumber,0) AS OrderNumber
                                ,ISNULL(D.HeadNumber,0) AS ButtNumber
                                ,A_1.StemNumber
                                ,A_1.StartTime
                         FROM   (SELECT   COUNT(StemID) AS StemNumber
                                          ,CONVERT(datetime,CONVERT(varchar(10),StartTime,120)) AS StartTime
                                 FROM     production.Stem AS Stem_3
                                 GROUP BY CONVERT(datetime,CONVERT(varchar(10),StartTime,120))) AS A_1
                                LEFT OUTER JOIN (SELECT   COUNT(StemID) AS HeadNumber
                                                          ,CONVERT(datetime,CONVERT(varchar(10),StartTime,120)) AS StartTime
                                                 FROM     production.Stem AS Stem_1
                                                 WHERE    (ButtStem = '1')
                                                 GROUP BY CONVERT(datetime,CONVERT(varchar(10),StartTime,120))) AS D
                                  ON A_1.StartTime = D.StartTime
                                LEFT OUTER JOIN (SELECT   COUNT(StemID) AS OrderNumber
                                                          ,CONVERT(datetime,CONVERT(varchar(10),StartTime,120)) AS StartTime
                                                 FROM     production.Stem AS Stem_2
                                                 WHERE    (StemDetails = '1')
                                                 GROUP BY CONVERT(datetime,CONVERT(varchar(10),StartTime,120))) AS C
                                  ON A_1.StartTime = C.StartTime) AS B
         ON A.StartTime = B.StartTime好像有几个子查询是相同的，插入到表变量再重复使用，是不是效率会高一点