将表顺序放入全局临时表时未保留表顺序

Question

所以我有这个数据库邮件查询，这基本上与我经常使用的格式完全相同，它总是按预期工作。 但是，对于此最新查询，按部分的顺序不会传输到全局临时，因此当我收到电子邮件时，订单都是随机的。 代码如下：

SET QUOTED_IDENTIFIER ON
DECLARE @CODE NVARCHAR(MAX),
        @CODE_ROW_COUNT INT,
        @s VARCHAR(MAX)
SET @s = 'Assembly Weekly Pod Thruput - Week of ' + 
CONVERT(VARCHAR(12),DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0), 107)
IF OBJECT_ID('tempdb..##WeeklyPodThruput') IS NOT NULL
    BEGIN
        DROP TABLE ##WeeklyPodThruput
    END;
DECLARE @juangoal INT = 114500
DECLARE @leogoal INT = 68500
DECLARE @silvestregoal INT = 68500
DECLARE @jorgegoal INT = 68500
DECLARE @totalgoal INT = @juangoal+@leogoal+@silvestregoal+@jorgegoal
DECLARE @juanmingoal INT = 8600
DECLARE @leomingoal INT = 5150
DECLARE @silvestremingoal INT = 5150
DECLARE @jorgemingoal INT = 5150
DECLARE @totalmingoal INT = 
@juanmingoal+@leomingoal+@silvestremingoal+@jorgemingoal;

WITH CTE AS(
SELECT DISTINCT
    CASE 
        WHEN t.EmplCode IN ('68','180','100','309','105') THEN '(1) Juan Pod'
        WHEN t.EmplCode IN ('275','297','146') THEN '(2) Leo Pod'
        WHEN t.EmplCode IN ('148','206','259') THEN '(3) Silvestre Pod'
        WHEN t.EmplCode IN ('251','242','142') THEN '(4) Jorge Pod'
        ELSE 'N/A'
        END AS [Pod],
    t.JobNo,
    r.CycleTime*od.QtyOrdered AS [Units Completed],
    od.UnitPrice
FROM TimeTicketDet t 
    JOIN OrderRouting r ON r.JobNo = t.JobNo AND t.WorkCntr = '150' AND r.WorkCntr = 'Assembly'
    JOIN OrderDet od ON r.JobNo = od.JobNo
WHERE r.JobNo NOT IN
        (
        SELECT DISTINCT
            od.JobNo
        FROM OrderDet od 
            JOIN TimeTicketDet t ON od.JobNo = t.JobNo
        WHERE CAST(t.TicketDate AS DATE) < CAST(DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS DATE) --CAST(GETDATE() AS DATE)
            AND t.PiecesFinished >= 1
            AND t.WorkCntr IN ('150')
        )
    AND CAST(t.TicketDate AS DATE) >= CAST(DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS DATE)
    AND CAST(t.TicketDate AS DATE) < CAST(DATEADD(DAY, 8-DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE())) AS DATE) --= CAST(GETDATE() AS DATE)
    AND t.PiecesFinished >= 1
    AND od.QtyOrdered = 1
),
CTE2 AS(
SELECT
    CASE 
        WHEN (GROUPING(CTE.Pod) = 1) THEN 'Total'
        ELSE CTE.Pod
        END AS [Pod],
    SUM(CTE.UnitPrice) AS [$ Completed],
    SUM(CTE.[Units Completed]) AS [Minutes Completed],
    CASE CTE.Pod
        WHEN '(1) Juan Pod' THEN @juangoal
        WHEN '(2) Leo Pod' THEN @leogoal
        WHEN '(3) Silvestre Pod' THEN @silvestregoal
        WHEN '(4) Jorge Pod' THEN @jorgegoal
        END AS [$ Goal],
    FORMAT(SUM(CTE.UnitPrice)/
    CASE CTE.Pod
        WHEN '(1) Juan Pod' THEN @juangoal
        WHEN '(2) Leo Pod' THEN @leogoal
        WHEN '(3) Silvestre Pod' THEN @silvestregoal
        WHEN '(4) Jorge Pod' THEN @jorgegoal
        END, 'P') AS [$ Goal %],
    CASE CTE.Pod
        WHEN '(1) Juan Pod' THEN @juanmingoal
        WHEN '(2) Leo Pod' THEN @leomingoal
        WHEN '(3) Silvestre Pod' THEN @silvestremingoal
        WHEN '(4) Jorge Pod' THEN @jorgemingoal
        END AS [Minutes Goal],
    FORMAT(SUM(CTE.[Units Completed])/
    CASE CTE.Pod
        WHEN '(1) Juan Pod' THEN @juanmingoal
        WHEN '(2) Leo Pod' THEN @leomingoal
        WHEN '(3) Silvestre Pod' THEN @silvestremingoal
        WHEN '(4) Jorge Pod' THEN @jorgemingoal
        END, 'P') AS [Minutes Goal %]
FROM CTE
GROUP BY CTE.Pod WITH ROLLUP
HAVING SUM(CTE.UnitPrice) > 0
)
SELECT
    CTE2.Pod,
    '$ '+FORMAT(CTE2.[$ Completed], 'N0') AS [$ Completed],
    CASE
        WHEN CTE2.Pod = 'Total' THEN '$ '+FORMAT(@totalgoal, 'N0')
        ELSE '$ '+FORMAT(CTE2.[$ Goal], 'N0')
        END AS [$ Goal],
    CASE
        WHEN CTE2.Pod = 'Total' THEN FORMAT(CTE2.[$ Completed]/@totalgoal,'P')
        ELSE CTE2.[$ Goal %]
        END AS [$ Goal %],
    FORMAT(CTE2.[Minutes Completed], 'N0') AS [Minutes Completed],
    CASE
        WHEN CTE2.Pod = 'Total' THEN FORMAT(@totalmingoal, 'N0')
        ELSE FORMAT(CTE2.[Minutes Goal], 'N0')
        END AS [Minutes Goal],
    CASE
        WHEN CTE2.Pod = 'Total' THEN FORMAT(CTE2.[Minutes Completed]/@totalmingoal,'P')
        ELSE CTE2.[Minutes Goal %]
        END AS [Minutes Goal %]
INTO ##WeeklyPodThruput
FROM CTE2
GROUP BY CTE2.Pod, CTE2.[$ Completed], CTE2.[Minutes Completed], CTE2.[$ Goal], CTE2.[$ Goal %], CTE2.[Minutes Goal], CTE2.[Minutes Goal %]
ORDER BY (CASE
            WHEN CTE2.Pod LIKE '%Pod' THEN 2
            WHEN CTE2.Pod = 'N/A' THEN 1
            ELSE 0
            END) DESC, SUM(CTE2.[Minutes Completed])/
                       CASE CTE2.Pod
                           WHEN '(1) Juan Pod' THEN @juanmingoal
                           WHEN '(2) Leo Pod' THEN @leomingoal
                           WHEN '(3) Silvestre Pod' THEN @silvestremingoal
                           WHEN '(4) Jorge Pod' THEN @jorgemingoal
                           END DESC
SELECT @CODE_ROW_COUNT = @@ROWCOUNT
IF(@CODE_ROW_COUNT > 0)
BEGIN
SET @CODE =
N'SELECT
  *
  FROM ##WeeklyPodThruput'
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable 
@html = @html OUTPUT,  
@query = @CODE
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '123abc',
    @recipients = '[e-mail]',
    @subject = @s,
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0,
    @execute_query_database = '123';
END
IF OBJECT_ID('tempdb..##WeeklyPodThruput') IS NOT NULL
    BEGIN
        DROP TABLE ##WeeklyPodThruput
    END

有趣的是，过去几周它一直运行良好，但是本周当我看到电子邮件时，它都乱了，而且很奇怪。 如果我单独运行查询部分，它会按预期运行和显示，但是，如果我将查询放在全局 temp 中并立即查询，它就会乱序。 我有 30 个这样的，按部分排序总是正确传输，不确定现在发生了什么。

Answer 1

您的查询：

SET @CODE =
N'SELECT
  *
  FROM ##WeeklyPodThruput'

没有"ORDER BY"子句，我假设"spQueryToHtmlTable"不强制任何顺序，所以根据定义，你可以得到任何顺序的结果。您需要将"ORDER BY"添加到此查询中。

为此，您可以在临时表中添加 2 列：

CASE
   WHEN CTE2.Pod LIKE '%Pod' THEN 2
   WHEN CTE2.Pod = 'N/A' THEN 1
   ELSE 0
END AS Order1,
SUM(CTE2.[Minutes Completed])/
   CASE CTE2.Pod
   WHEN '(1) Juan Pod' THEN @juanmingoal
   WHEN '(2) Leo Pod' THEN @leomingoal
   WHEN '(3) Silvestre Pod' THEN @silvestremingoal
   WHEN '(4) Jorge Pod' THEN @jorgemingoal
END AS Order2

，然后在从临时表中选择中使用 Order1 和 Order2。