代码之家 › 专栏 › 技术社区 › Andrew

如何优化这个联合SQL查询?

sql-server sql

-1

Andrew · 技术社区 · 6 年前

SELECT DISTINCT * FROM ( 
                        SELECT  FundId AS Id,
                                PeriodYearMonth
                        FROM [Fund.Period] F
                        INNER JOIN (
                                    SELECT * FROM (
                                                    SELECT  FundId as Id, 
                                                    MIN(PeriodYearMonth) AS MinPeriodYearMonth
                                                    FROM (
                                                        SELECT  FundId,
                                                                PeriodYearMonth,
                                                                PublishedOn
                                                        FROM    [Fund.Period] FP
                                                        UNION ALL --Changed to UNION ALL as it is more efficient and we wont ever need a UNION as the result set would never match
                                                        SELECT  FundId,
                                                                MAX(PeriodYearMonth) + 1,
                                                                NULL
                                                        FROM      [Fund.Period]
                                                        GROUP BY FundId
                                                        ) FP WHERE PublishedOn IS NULL GROUP BY FundId
                                                    ) MFP
                                    )  FP ON F.FundId = FP.Id AND (F.PeriodYearMonth = FP.MinPeriodYearMonth OR (f.PeriodYearMonth +1) = FP.MinPeriodYearMonth) 
                           ) FP

如果可能的话,我想把工会全部撤掉。有人知道如何优化吗?

1 回复 | 直到 6 年前

Zaynul Abadin Tuhin 6 年前

已全部删除联合

SELECT DISTINCT * FROM ( 
                    SELECT  FundId AS Id,
                            PeriodYearMonth
                    FROM [Fund.Period] F
                    INNER JOIN (
                                SELECT * FROM (
                                                SELECT  FundId as Id, 
                                                MIN(PeriodYearMonth) AS MinPeriodYearMonth,MAX(PeriodYearMonth) + 1 as PeriodYearMonth
                                                FROM (
                                                    SELECT  FundId,
                                                            PeriodYearMonth,
                                                            PublishedOn
                                                    FROM    [Fund.Period] FP

                                                    ) FP WHERE PublishedOn IS NULL 
                                                    GROUP BY FundId
                                                ) MFP
                                )  FP ON F.FundId = FP.Id AND (F.PeriodYearMonth = FP.MinPeriodYearMonth OR (f.PeriodYearMonth +1) = FP.MinPeriodYearMonth) 
                       ) FP