問題描述

我從一排數(shù)字中準(zhǔn)備了字符串.當(dāng)我使用 row_number 函數(shù)時(shí)，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

結(jié)果 9876543211816141210864227242118151296336322824201612844540353025201510554484236302418126635649423528211477264564840322416881726354453627189"

result "9876543211816141210864227242118151296336322824201612844540353025201510554484236302418126635649423528211477264564840322416881726354453627189"

差異在哪里?

推薦答案

我希望這與 這個(gè)問題，總結(jié)一下當(dāng)你使用變量連接時(shí)，例如

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;

結(jié)果取決于物理實(shí)現(xiàn)和內(nèi)部訪問路徑.我目前正在努力在互聯(lián)網(wǎng)上尋找基準(zhǔn)測(cè)試，但我認(rèn)為 SQL Server 中最快、最可靠的方法是使用 XML 擴(kuò)展將行連接到列:

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)');

注意我還刪除了對(duì) master..spt_values 的引用并替換為表值構(gòu)造函數(shù) - 這只是添加了不必要的讀取以生成從 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.

如果您的序列需要更多數(shù)字，我仍然不會(huì)使用系統(tǒng)表，請(qǐng)使用 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;

這篇關(guān)于TSQL USE ROW_NUMBER IN CTE，SELECT ORDER BY NOT WORKING的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！