子查询中的排序与字符串接

[勉励前行]

CREATE TABLE #TEST (F1 int)
INSERT INTO #TEST(F1) VALUES(8)
INSERT INTO #TEST(F1) VALUES(10)
INSERT INTO #TEST(F1) VALUES(7)
INSERT INTO #TEST(F1) VALUES(9)
INSERT INTO #TEST(F1) VALUES(1)


DECLARE @SheetStatus varchar(8000)
SET @SheetStatus = ''
SELECT @SheetStatus = @SheetStatus + ','+ CAST ( F1 as varchar(10))
FROM
(
SELECT distinct F1
FROM #TEST
--ORDER BY F1 DESC --想要倒序，可是子查询中不给排
) A
DROP TABLE #TEST

SELECT SUBSTRING(@SheetStatus,2,8000)

实际结果为：1,7,8,9,10　
想要结果为：10,9,8,7,1

[ai_li7758521]

 DECLARE @SheetStatus varchar(8000)
 SET @SheetStatus = ''
 SELECT  @SheetStatus = @SheetStatus + ','+ CAST (  F1 as varchar(10))
 FROM 
 (
  SELECT  distinct F1
  FROM #TEST
) A
ORDER BY F1 DESC
 DROP TABLE #TEST

 SELECT  SUBSTRING(@SheetStatus,2,8000)

[Andy__Huang]

CREATE TABLE #TEST (F1 int)
INSERT INTO #TEST(F1) VALUES(8)
INSERT INTO #TEST(F1) VALUES(10)
INSERT INTO #TEST(F1) VALUES(7)
INSERT INTO #TEST(F1) VALUES(9)
INSERT INTO #TEST(F1) VALUES(1)

select * from #TEST
order by F1 desc

declare @s varchar(8000) 
set @s=''
select @s=@s+cast(F1 as varchar)+','
from #TEST
order by F1 desc
print left(@s,len(@s)-1)


/*
10,9,8,7,1
*/

[發糞塗牆]

CREATE TABLE #TEST (F1 int)
INSERT INTO #TEST(F1) VALUES(8)
INSERT INTO #TEST(F1) VALUES(10)
INSERT INTO #TEST(F1) VALUES(7)
INSERT INTO #TEST(F1) VALUES(9)
INSERT INTO #TEST(F1) VALUES(1)


DECLARE @SheetStatus varchar(8000)
SET @SheetStatus = ''
SELECT  @SheetStatus = @SheetStatus + ','+ CAST (  F1 as varchar(10))
FROM 
(
 SELECT DISTINCT TOP 99.99 PERCENT  F1
 FROM #TEST
 ORDER BY F1 DESC --想要倒序，可是子查询中不给排
) A
DROP TABLE #TEST

SELECT  SUBSTRING(@SheetStatus,2,8000)

/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10,9,8,7,1

*/

[發糞塗牆]

在你原来基础上加个top 99.99 percent即可用order by