SQL Server Using ROW_NUMBER() in sub-query but want the value in inner query to reflect to outer query [closed]

ddrv8njm 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(96)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 8 days ago.
Improve this question

I have data like this

id	name
1	AA
2	BB
3	CC
4	DD
5	EE

Given that, I have to use a select statement like this.

select *
from (
  select
    row_number() over (partition by id order by id asc) as row
    , id
    , name
) tmp
where tmp.id in (2,3,4) -- Just an example of condition in outer query

I could not temper with outer query [tmp] selection. when I want display data to be as

row	id	name
1	2	BB
2	3	CC
3	4	DD

Is there any way to achieve row_number() as intended without creating temp table and use store procedure to delete and insert every time it queries data with some condition.

sql-server

来源：https://stackoverflow.com/questions/77241944/using-row-number-in-sub-query-but-want-the-value-in-inner-query-to-reflect-to

1条答案

按热度按时间

2ekbmq321#

The best case I could find for now is using a new temporary table for the results wanted it to be and a stored procedure to truncate and insert it as conditions specified

BEGIN TRY   
            TRUNCATE TABLE [TEMP]
            IF EXISTS (SELECT 1 FROM MAIN WHERE your condition)
            SELECT @TableName = 'MAIN'
            , @Condition = 'your condition'
            ELSE
            SELECT @TableName = 'MAIN'
            , @Condition = ' '
           
            SELECT @sqlCommand = 'INSERT INTO TEMP select 
               row_number() over (partition by id order by id asc) as row ,
              /**other columns**/
                from '+@TableName
                +@Condition
            EXEC (@sqlCommand)

            
            SET @o_MSG = 'OK';
        COMMIT;
        END TRY                         
        BEGIN CATCH
            ROLLBACK;   
            SET @o_MSG = 'ERROR';
        END CATCH

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Using ROW_NUMBER() in sub-query but want the value in inner query to reflect to outer query [closed]

1条答案

相关问题

热门标签

最新问答