主页 / dba / 问题 / 172675
Accepted
McNets
Asked: 2017-05-04 13:16:11 +0800 CST

对使用 STUFF 生成的逗号分隔字符串的结果进行排序

  • 772

给定这样的模式：

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');

我需要生成一个逗号分隔的字符串，按以下顺序排列Position：

'M' + Id + ' AS [' + Title + ']'

期望的结果：

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]

我试过了：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

但它构建了按以下顺序排序的结果Id：

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

如果我添加ORDER BY Id到STUFF表达式：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

产生下一个错误：

如果指定了 SELECT DISTINCT，则消息 145 级别 15 状态 1 第 2 行
ORDER BY 项必须出现在选择列表中。+

我可以使用按以下顺序排序的子查询Position：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM (SELECT TOP 100 PERCENT Id, Position, Title FROM Foo ORDER BY Position) X
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

但我想知道是否有另一种方法可以在不使用子查询的情况下对结果进行排序。没有重复的标题。

dbfiddle在这里

sql-server order-by

1 个回答

  1. Best Answer

    正如Aaron Bertrand在评论中指出的那样，产生错误是因为我使用的是SELECT DISTINCT; 在这个例子中，没有必要。此外，ORDER BY如果删除了不同的位置，则位置是完全有效的。

    如果DISTINCT被删除，则查询返回所需的值：

    DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;
GO

    | （无列名） |
| :------------------------------------------------ ------------------------------------------- |
| M3 AS [Title1]、M5 AS [Title2]、M1 AS [Title3]、M2 AS [Title10]、M4 AS [Title12] |

    dbfiddle在这里

    我建议看一下sp_BlitzErik推荐的这篇文章：

    分组串联：排序和删除重复项

    • 3

相关问题