tsql - 与正常的行集相比，FOR XML AUTO 子句能否给出意想不到的结果？

Question

当使用 FOR XML AUTO 返回时，我试图了解我的 SQL 结果中的一些异常情况。奇怪的是，我在 XML 中得到了不同的结果。简而言之， 的计数机构应该总是 603，但 XML 数据给了我 3 个不同的总数，如这组代码片段所示:


下面的 ORDER BY 只是通过选择对结果进行排序。为了调试它，我手动运行了存储过程(注释掉 FOR XML 子句并创建了 3 个以传递的 SORT 参数为后缀的“调试”表(当没有使用 FOR XML AUTO 时，每个结果集包含 603 行)。



希望问题现在已经清楚地显示出来了。这是存储过程(我认为@Sort 值确定 ORDER BY 的底部逻辑是最相关的):

    ALTER Procedure [dbo].[FLAS2_List_Awards_V4]

-- EXECUTE FLAS2_List_Awards_V4 2,0
(
    @Sort   int = 1 
                    -- 1 = descending order on TotalAmount
                    -- 2 = descending order on TotalAwards
                    -- 3 = ascending order on Institution
    ,@Range int = 0
                    -- 0 = no filtering 
                    -- 1 = < $1 million
                    -- 2 = < $5 million
                    -- 3 = < $10 million
                    -- 4 = < $15 million
                    -- 5 = < $20 million
                    -- 6 = > $20 million
)
As

CREATE TABLE #TempMarkers
(
    ID          nchar(6) NOT NULL
,   Institution nvarchar(255) NOT NULL
,   Street      nvarchar(255) NULL
,   City        nvarchar(255) NULL
,   State       nvarchar(255) NULL
,   Zip         nvarchar(255) NULL
,   Latitude    decimal (28, 18) NULL
,   Longitude   decimal (28, 18) NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)
INSERT INTO #TempMarkers
(
    ID          
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   TotalAwards 
,   TotalAmount 
)
SELECT DISTINCT
        C.ID 
      , C.InstitutionName
      , NULL AS street
      , NULL AS city
      , NULL AS state
      , NULL AS zip
      , NULL As Latitude
      , NULL As Longitude
      , NULL As TotalAwards
      , NULL As TotalAmount
  FROM dbo.FLAS2_Schools2 C
-- where c.ID in (135717,434584)
UPDATE #TempMarkers 
    SET      Street     = x.street
            ,City       = x.city
            ,State      = x.state
            ,Zip        = x.zip          
    FROM dbo.FLAS2_Schools2 X  
    WHERE X.ID = #TempMarkers.ID
    AND
    X.InstitutionName = #TempMarkers.Institution
UPDATE #TempMarkers 
    SET     Latitude    = Z.lat
            ,Longitude  = Z.Long
    FROM dbo.ZipCodesPreferred Z                         
    WHERE Z.ZipCode = #TempMarkers.Zip
CREATE TABLE #TempGrants
(
    ID          nchar(6) NOT NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)

EXECUTE dbo.FLAS2_List_Awards_V3_PrepAwards @Range

CREATE TABLE #FinalMarkers
(
    ID          nchar(6) NOT NULL
,   Institution nvarchar(255) NOT NULL
,   Street      nvarchar(255) NULL
,   City        nvarchar(255) NULL
,   State       nvarchar(255) NULL
,   Zip         nvarchar(255) NULL
,   Latitude    decimal (28, 18) NULL
,   Longitude   decimal (28, 18) NULL
,   TotalAwards decimal (16, 0) NULL
,   TotalAmount decimal (16, 0) NULL
)
INSERT INTO #FinalMarkers
(
    ID          
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   TotalAwards 
,   TotalAmount 
)
select 
    t1.ID           
,   Institution 
,   Street      
,   City 
,   State       
,   Zip  
,   Latitude
,   Longitude       
,   t2.TotalAwards 
,   t2.TotalAmount 
FROM #TempMarkers t1
join #TempGrants t2 
on t1.id = t2.id

--SELECT * FROM #FinalMarkers

IF @Sort = 1 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.TotalAmount DESC, Marker.Institution, GrantAmount DESC
for xml auto, root('root')
END
IF @Sort = 2 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.TotalAwards DESC, Marker.Institution ,GrantAmount DESC
for xml auto, root('root')
END
IF @Sort = 3 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
order by Marker.Institution ,Marker.Street, GrantAmount DESC
for xml auto, root('root')
END

顺便说一句，无论在创建过程中使用 INTO 子句如何排序，每个“DEBUG”表都正好有 1117 行。

最后，这是网络应用程序循环的片段(计算“标记”):

Answer 1

好吧，这对我来说是一个难题(以及为什么我要悬赏)，但我得到的帮助却无济于事。

我自己解决了这个问题。问题的要点是需要添加 GROUP BY 并通过添加 Marker.Street 来加强 ORDER BY 子句(参见下面的代码段)。顺便说一句，FOR XML AUTO 非常适合在 Marker 元素中创建 Award 元素。

    IF @Sort = 2 
BEGIN
SELECT   Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude       
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber as GrantNumber
        ,Award.TotalObligatedAmount as GrantAmount
FROM #FinalMarkers Marker     
     LEFT JOIN dbo.FLAS2_Grants Award
       ON Marker.ID = Award.ID
GROUP BY Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,Marker.City
        ,Marker.State
        ,Marker.Zip
        ,Marker.Latitude
        ,Marker.Longitude
        ,Marker.TotalAmount
        ,Marker.TotalAwards
        ,Award.GrantNumber 
        ,Award.TotalObligatedAmount        
order BY Marker.TotalAwards DESC
        ,Marker.ID
        ,Marker.Institution
        ,Marker.Street
        ,GrantAmount DESC
for xml auto, root('root')
END

GROUP BY 已根据 @Sort 值添加到每个代码块中，但除此之外，OP 中的代码没有任何变化。