当您不知道列名时，SQL Server ROW_NUMBER左联接+ [英] SQL Server ROW_NUMBER Left Join + when you don't know column names

问题描述

我正在写一个页面，该页面将创建一个查询(针对非db用户)，并创建查询并运行查询，并返回查询结果.

I'm writing a page that will create a query (for non-db users) and it create the query and run it returning the results for them.

我正在使用row_number来处理自定义分页.

I am using row_number to handle custom pagination.

当我不知道需要返回的特定列时，如何在子查询中进行左联接和row_number.我尝试使用*，但出现了

How do I do a left join and a row_number in a subquery when I don't know the specific columns I need to return. I tried to use * but I get an error that

"列已多次指定

The column '' was specified multiple times

这是我尝试过的查询:

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Test) AS ROW_NUMBER, *
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 

推荐答案

尝试使用此sql.应该可以.

Try this sql. It should work.

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY a.Test) AS ROW_NUMBER, a.*,b.*
   FROM table1 a
   LEFT JOIN table2 b 
   ON a.ID = b.ID) x
WHERE ROW_NUMBER BETWEEN 1 AND 50 

这篇关于当您不知道列名时，SQL Server ROW_NUMBER左联接+的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！