问题描述
我从一排数字中准备了字符串.当我使用 row_number 函数时，order by 子句不起作用
I preapare string from row of nubmers. When I use the row_number function, the order by clause not working
DECLARE @text VARCHAR(MAX)
IF OBJECT_ID('tempdb..#numbers') IS NOT NULL DROP TABLE #numbers
SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number INTO #numbers FROM master..spt_values
SET @text = ''
;WITH
numbers (number)
AS
(
SELECT CAST(ROW_NUMBER() OVER (ORDER BY name) AS INT) AS number FROM master..spt_values
),
a
AS
(
SELECT number FROM numbers WHERE number < 10
),
b
AS
(
SELECT number FROM numbers WHERE number < 10
)
SELECT @text = @text + LTRIM(STR(a.number*b.number))
FROM a
CROSS JOIN b
ORDER BY a.number, b.number DESC
SELECT @text
result "9"
SET @text = ''
;WITH
numbers (number)
AS
(
SELECT number FROM #numbers
),
a
AS
(
SELECT number FROM numbers WHERE number < 10
),
b
AS
(
SELECT number FROM numbers WHERE number < 10
)
SELECT @text = @text + LTRIM(STR(a.number*b.number))
FROM a
CROSS JOIN b
ORDER BY a.number, b.number DESC
SELECT @text
结果 9876543211816141210864227242118151296336322824201612844540353025201510554484236302418126635649423528211477264564840322416881726354453627189"
result "9876543211816141210864227242118151296336322824201612844540353025201510554484236302418126635649423528211477264564840322416881726354453627189"
差异在哪里?
推荐答案
我希望这与 这个问题，总结一下当你使用变量连接时，例如
I expect this is related to this issue, in summary when you use variable concatenation, e.g.
SELECT @Variable = @Variable + someField
FROM Table
ORDER BY AnotherField;
结果取决于物理实现和内部访问路径.我目前正在努力在互联网上寻找基准测试，但我认为 SQL Server 中最快、最可靠的方法是使用 XML 扩展将行连接到列:
The results are dependant on physical implementation and internal access paths. I am currently struggling to find benchmark tests on the internet, but I think the fastest, reliable approach in SQL Server is to use XML extensions to concatenate rows to columns:
WITH Numbers AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) t (Number))
SELECT [Text] = (SELECT LTRIM(STR(a.number*b.number))
FROM Numbers AS A
CROSS JOIN Numbers AS B
ORDER BY A.Number, b.Number DESC
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)');
注意我还删除了对 master..spt_values 的引用并替换为表值构造函数 - 这只是添加了不必要的读取以生成从 1 到 9 的序列.
N.B. I have also removed the reference to master..spt_values and replaced with a table value constructor - this just adds unnecessary reads to generate a sequence from 1 to 9.
如果您的序列需要更多数字，我仍然不会使用系统表，请使用 Iztik Ben-Gan 的堆叠 CTE 方法，如 这篇文章:
If you need more numbers for your sequence I would still not use system tables, use Iztik Ben-Gan's stacked CTE approach, as described in this article:
DECLARE @Numbers INT = 100000;
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT 1 FROM N3 AS N1 CROSS JOIN N3 AS N2),
Numbers (Number) AS (SELECT TOP (@Numbers) ROW_NUMBER() OVER(ORDER BY N) FROM N4)
SELECT Number
FROM Numbers;
这篇关于TSQL USE ROW_NUMBER IN CTE，SELECT ORDER BY NOT WORKING的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！