如何使用行号（） [英] How to use row number()

问题描述

select 'D05503'+ROW_NUMBER() OVER(ORDER BY C_CODE),* from Tbl_Doc_Stock_Chem_Add_Mst where N_Type=1 and C_Code like 'sa%'

执行此查询时收到错误

如下所示：

While executing this query am getting error
like below:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

数字应该是这样的：

Numbers should come like this:

D05503
D05504
D05505
D05506
D05507
D05508

推荐答案

ROW_N UMBER返回一个数值，SQL正在尝试将文本字符串D05503转换为数字以便添加它。这是行不通的。

相反，试试这个：

ROW_NUMBER returns a numeric value, and SQL is trying to convert the text string "D05503" to a number in order to add it. That won't work.
Instead, try this:

SELECT 'D' + RIGHT('00000' + CONVERT(VARCHAR, 5503 + ROW_NUMBER() OVER (ORDER BY C_CODE)), 5), * 
FROM bl_Doc_Stock_Chem_Add_Mst 
WHERE N_Type=1 AND C_Code LIKE 'sa%'

这是一个非常神秘的错误消息。

但它可能来自f rom以错误的顺序做事。

分析函数是管道中的最后一个。



所以尝试使用这样的CTE：

That's a pretty cryptic error message.
But it probably comes from doing things in the wrong order.
Analytic functions are last in the pipe.

So try using a CTE like this:

WITH CTE AS (
    SELECT  ROW_NUMBER() OVER(ORDER BY C_CODE) rn
    FROM    Tbl_Doc_Stock_Chem_Add_Mst
    WHERE   N_Type=1
        AND C_Code LIKE 'sa%'
    )
SELECT  'D05503' + cast(rn as varchar) Somenumber
FROM    CTE

这篇关于如何使用行号（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！