在派生表中使用ORDER BY

Question

我有7个指标名称，所有这些指标理想情况下应该每月更新一次，但有时并不会发生这种情况。在这种情况下，我需要结转上个月的度量值，红色阈值和黄色阈值。所有数据都来自Excel。

在sql-server上创建了以下查询-

select 
    withnull.[Metric Name],
    ISNULL(withnull.[Metric Value], withnullx.[Metric Value]) MetricValue,
    ISNULL(withnull.[Red Threshold], withnullx.[Red Threshold]) Red,
    ISNULL(withnull.[Yellow Threshold], withnullx.[Yellow Threshold]) Yellow,
    withnull.[Date]
from 

    (
    SELECT [Metric Value], [Red Threshold], [Yellow Threshold], Mon.[Date], Mon.[Metric Name]
    FROM

        (
        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 Effectiveness (SPC)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 MRB Effectiveness (Non-Conf)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['#1 Effectiveness(Problem Solvi)$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Calibration Passing "As Found" $']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Change Control Malfunction Rate$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['MSA passing rate$']

        UNION ALL

        SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
        FROM [QMS Scorecard].[dbo].['Unknown Failures (FMEA & Ctrl)$']
        ) Data

        RIGHT JOIN

        (
        SELECT [Date], [Metric Name]
        FROM [Dates$]
        --ORDER BY [Metric Name], [Date]
        ) Mon

    ON datepart(mm, Data.[Last Updated Date]) = datepart(mm, Mon.[Date])
    AND
    datepart(yyyy, Data.[Last Updated Date]) = datepart(yyyy, Mon.[Date])
    AND
    Data.[Metric Name] = Mon.[Metric Name]

    --ORDER BY [Metric Name], [Date]
    ) withnull

    outer apply

    (SELECT [Metric Value], [Red Threshold], [Yellow Threshold]
    FROM (SELECT * from (SELECT *, row_number() OVER (PARTITION BY [Metric Name] ORDER BY [Date] DESC) rn from
        (
        SELECT [Metric Value], [Red Threshold], [Yellow Threshold], Mon.[Date], Mon.[Metric Name]
        FROM

            (
            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 Effectiveness (SPC)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 MRB Effectiveness (Non-Conf)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['#1 Effectiveness(Problem Solvi)$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Calibration Passing "As Found" $']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Change Control Malfunction Rate$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['MSA passing rate$']

            UNION ALL

            SELECT [Metric Name], [Last Updated Date], [Metric Value], [Red Threshold], [Yellow Threshold]
            FROM [QMS Scorecard].[dbo].['Unknown Failures (FMEA & Ctrl)$']

            ) Data

            RIGHT JOIN

            (
            SELECT [Date], [Metric Name]
            FROM [Dates$]
            ) Mon

        ON datepart(mm, Data.[Last Updated Date]) = datepart(mm, Mon.[Date])
        AND
        datepart(yyyy, Data.[Last Updated Date]) = datepart(yyyy, Mon.[Date])
        AND
        Data.[Metric Name] = Mon.[Metric Name]

        ) b ) d
        WHERE rn = 1) c
    WHERE 
        c.[Date]<withnull.[Date] and 
        withnull.[Metric Value] is null and
        c.[Metric Value] is not null and 
        c.[Red Threshold] is not null and 
        c.[Yellow Threshold] is not null

    ORDER BY [Metric Name], [Date] DESC
    ) withnullx

我已经使用UNION ALL连接了所有指标的工作表，为了获得每个指标名称的所有月份，我做了一个正确的连接。

现在我有几个月的空值(Metric Value、Red Threshold、黄色Threshold)，没有这些特定指标名称的记录。

使用外部应用，我将这些空值替换为前一个月的值。这需要上述代码倒数第二行的ORDER BY。

在上面的代码中还有另一个Order BY。"SELECT * from (SELECT *，row_number() OVER (PARTITION BY Metric Name ORDER BY Date DESC) rn“这不仅可以帮助我获得完整数据的top，还可以帮助我获得每个指标名称的top。

我希望如果我可以使用这段代码，我将能够用每个指标名称的前一个月的值替换空值。但是我得到的错误是“ORDER BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了TOP、OFFSET或FOR XML。”提前谢谢你。

Answer 1

首先，错误会告诉你问题出在哪里，但没有解释原因。您的order by正在尝试对查询排序。但该查询是一个子查询，用于应用于另一个表。在这种情况下，子查询的顺序完全无关紧要。为了进行连接/应用，SQL并不关心查询结果的顺序，而主查询顺序是由它自己的order子句设置的。因此，即使SQL允许您这样做并且没有出错，order子句实际上也不会做任何事情。

我认为您要做的是只应用子查询的第一行？在这种情况下，您需要确保查询只返回要应用的行，或者您可以指定要应用的行。ORDER BY不会这样做。在前面的步骤中，您已经在子查询中设置了rownmuber，然后将rownumber限制为1--我认为这正是您想要的。

这就是您当前应用的子查询所执行的操作

Q1 - Outer query with lots of where stuff (Date < date, Red and yellow not null, etc
   Q2 - Query that only allows most recent row per [Metric Name]
      Q3 - Query that gets data and adds a rownumber by date

Q3获取数据并按每个指标名称的日期对其进行排序。然后，Q2只为每个指标名称选择最新的行，其他所有行都会被抛出。然后，Q1应用了大量的where子句。问题是，如果where子句失败，那么您就没有其他行可以查看了，因为您已经在Q2中抛出了它们。where子句总是失败的，因为最近的行的日期永远不会早于要匹配的行。

您需要做的是以某种方式将所有where子句移到只返回一行的部分之前。这样，您将只获取最近有效行，而不只是最近的行。您需要将where子句移到包含row_number的Q3查询中。null子句很容易移动，但日期必须重新考虑。