在使用 STUFF FOR XML 连接之前对数据进行排序

本文介绍了在使用 STUFF FOR XML 连接之前对数据进行排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下用于 SSRS 报告的查询:

I have the following query that I am using for an SSRS Report:

SELECT  ROW_NUMBER() OVER ( ORDER BY Judge.EventJudgeID ) AS JudgeRow ,
        Judge.EventID ,
        Judge.Judge_PersonID ,
        STUFF(( SELECT DISTINCT
                        ',' + CAST(Fights.FightNumber AS VARCHAR(MAX)) AS [text()]
                FROM    dbo.tblFights Fights ,
                        dbo.tblFightJudge FRJudge
                WHERE   Fights.FightID = FRJudge.fightid
                        AND ( Judge.Judge_PersonID = FRJudge.judge1id
                              OR Judge.Judge_PersonID = FRJudge.judge2id
                              OR Judge.Judge_PersonID = FRJudge.judge3id
                            )
              FOR
                XML PATH('')
              ), 1, 1, '') AS BoutsJudged ,
        Persons.LastName + ' ' + Persons.FirstName AS JudgeName ,
        Events.EventName ,
        Events.EventDate
FROM    dbo.tblEventJudge Judge
        INNER JOIN dbo.tblPersons Persons ON PersonID = Judge_PersonID
        INNER JOIN dbo.tblEvents Events ON Events.EventID = Judge.EventID
WHERE   Judge.EventID = 1278;

问题在于 STUFF 命令返回以下字符串:

The problem is that the STUFF command returns the following string:

1,10,11,12,13,14,15,16,17,18,19,2,3,4,5,6,7,8,9

如何在将数字连接成字符串之前对其进行排序?

How can I make it sort the numbers before concatenating it into a string?

推荐答案

试试这个

SELECT  ROW_NUMBER() OVER ( ORDER BY Judge.EventJudgeID ) AS JudgeRow ,
        Judge.EventID ,
        Judge.Judge_PersonID ,
        STUFF(Select ',' + CAST(Fights.FightNumber AS VARCHAR(MAX)) AS [text()] From ( SELECT DISTINCT Fights.FightNumber
                FROM    dbo.tblFights Fights ,
                        dbo.tblFightJudge FRJudge
                WHERE   Fights.FightID = FRJudge.fightid
                        AND ( Judge.Judge_PersonID = FRJudge.judge1id
                              OR Judge.Judge_PersonID = FRJudge.judge2id
                              OR Judge.Judge_PersonID = FRJudge.judge3id
                            )
                      ) X 
             ORDER BY Fights.FightNumber
             FOR
                XML PATH('')
              ), 1, 1, '') AS BoutsJudged ,
        Persons.LastName + ' ' + Persons.FirstName AS JudgeName ,
        Events.EventName ,
        Events.EventDate
FROM    dbo.tblEventJudge Judge
        INNER JOIN dbo.tblPersons Persons ON PersonID = Judge_PersonID
        INNER JOIN dbo.tblEvents Events ON Events.EventID = Judge.EventID
WHERE   Judge.EventID = 1278;

你可以查看下面的sqls，

You can check below sqls,

之前:

Select *, 
       STUFF((Select Distinct ','+Cast(high as varchar(MAX)) 
              from master..spt_values where type = 'p' and number < 20  
              for xml Path('')),1,1,'')
from  master..spt_values where type = 'p' and number < 20

之后:

Select *, 
           STUFF((Select ','+Cast(high as varchar(MAX)) from (Select distinct     high 
    from master..spt_values where type = 'p' and number < 20) x Order by high  for xml Path('')),1,1,'')
    from  master..spt_values where type = 'p' and number < 20

这篇关于在使用 STUFF FOR XML 连接之前对数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

stuff