子查询中的排序与字符串接
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
[解决办法]
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)
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
*/
