使用 ORDER BY 子句在 varchar 变量中累积数据集

Question

我正在编写一个 QA 过程，该过程记录数据库中违反某些业务规则的记录，并向用户发送电子邮件，通知他们有一些清理工作要做。我们的业务需求包括不向用户"发送垃圾邮件"，因此在发送之前，我必须将所有违规行为累积到一封电子邮件中。

此特定 QA 可识别重复的 WPGC。

-- variables already declared
DECLARE @th VARCHAR(MAX) SET @th = '';
DECLARE @tb VARCHAR(MAX) SET @tb = '';
DECLARE @tr VARCHAR(MAX) SET @tr = '';
DECLARE @tf VARCHAR(MAX) SET @tf = '';
-- yes the URL is munged. org_name is probably not a valid CRM organization.
DECLARE @url VARCHAR(MAX) SET @url = 'https://org_name.crm.dynamics.com/main.aspx?etc=@etc&id={@guid}&pagetype=entityrecord';
DECLARE @rc INT SET @rc = 0 ;
--------------------------------------------------------
SELECT @th='<tr><th>Capyr</th><th>WPGC Name</th><th>Account Name</th></tr>'
, @tr='<tr><td>@3</td><td>@4</td><td><a href="@url">@5</a></td></tr>'
, @tb='', @tf='', @rc=0;
WITH d AS ( 
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@tr
, '@url' , @url)
, '@etc' , '10021')
, '@guid', ISNULL(CAST(account      AS VARCHAR(50)),''))
, '@3', isnull(CAST(campaignyear AS VARCHAR(50)),''))
, '@4', isnull(CAST(name         AS VARCHAR(50)),''))
, '@5', isnull(CAST(accountname  AS VARCHAR(50)),'')) d
,  1 n
, campaignyear f1d, accountname f2a
FROM QA.dupeWPGC 
)
SELECT @tb += d, @rc += n 
FROM d
--ORDER BY f1d DESC, f2a ASC

如果我按写入方式运行此查询，则 170 行将按预期显示。

问题 1：数据没有特定的顺序。(2010, 2011, 2012, 2009, 2009, 2016, 2011...)
解决方案 1：添加(取消注释(ORDER BY子句

问题 2：如果我在ORDER BY子句处于活动状态(未注释(的情况下运行此查询，则仅包含一条记录。(它似乎是正确ORDER的 ed 数据集的最后一条记录。
解决方案 2：???

如果我将ORDER BY子句添加到最外层的查询中，则只返回一行。
如果我在其他任何地方(例如在视图QA.dupeWPGC、CTEd或另一个 CTE 中(例如，, d2 AS ( SELECT * FROM d ORDER BY f1d DESC, f2a ASC)(中添加ORDER BY子句，则最终结果仍然未排序(即，看不到差异(。

如何使此数据集呈现 a( 完全和 b( 正确排序？

最后，这将在以下位置运行：

SELECT @@VERSION;
--------------------------------------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) 
Sep 23 2016 18:13:56 
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Answer 1

对于解决方案 1：

更新：解决方案 1 不起作用

您可以尝试在ORDER BY末尾添加 OPTION(MAXDOP 1

(：

ORDER BY f1d DESC, f2a ASC
OPTION( MAXDOP 1 )

我不确定，但我的猜测是ORDER BY会导致并行计划，这可能会导致问题。

我建议解决方案3：

它使用内置的 FOR XML 功能的 SQL Server

CREATE TABLE #Data( ID INT, campaignyear VARCHAR( 20 ), name VARCHAR( 20 ), account VARCHAR( 20 ), accountname VARCHAR( 20 ))
INSERT INTO #Data
SELECT 4, '2017', 'John', '123456', 'AccountName1' UNION ALL
SELECT 7, '2017', 'Paul', '654321', 'AccountName2' UNION ALL
SELECT 3, '2016', 'Jack', '123', 'AccountName3'
DECLARE @tb XML
SELECT @tb = (
SELECT 
( SELECT ISNULL( CONVERT( NVARCHAR( 50 ), campaignyear ), '' ) FOR XML PATH( 'td' ), TYPE ),
( SELECT ISNULL( CONVERT( NVARCHAR( 50 ), name ), '' ) FOR XML PATH( 'td' ), TYPE ),
( SELECT
( SELECT CONVERT( NVARCHAR( 255 ),
N'https://org_name.crm.dynamics.com/main.aspx?etc=10021&id={' + ISNULL( CONVERT( NVARCHAR( 50 ), account ), '' ) + '}&pagetype=entityrecord' ) AS [@href],
ISNULL( CONVERT( NVARCHAR( 50 ), accountname ), '' )
FOR XML PATH( 'a' ), TYPE )
FOR XML PATH( 'td' ), TYPE )
FROM #Data
ORDER BY ID -- !!!!!!!!!!! Note ORDER BY here
FOR XML PATH( 'tr' )
)
SELECT @tb

输出：

<tr>
<td>2016</td>
<td>Jack</td>
<td>
<a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={123}&amp;pagetype=entityrecord">AccountName3</a>
</td>
</tr>
<tr>
<td>2017</td>
<td>John</td>
<td>
<a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={123456}&amp;pagetype=entityrecord">AccountName1</a>
</td>
</tr>
<tr>
<td>2017</td>
<td>Paul</td>
<td>
<a href="https://org_name.crm.dynamics.com/main.aspx?etc=10021&amp;id={654321}&amp;pagetype=entityrecord">AccountName2</a>
</td>
</tr>