当您不知道列名时，SQL Server ROW_NUMBER左连接+

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.

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

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

当我不知道需要返回的特定列时，如何在子查询中执行左连接和row_number。我试着用*但是我有一个错误。

The column '' was specified multiple times

该列“指定了多次”。

Here is the query I tried:

以下是我尝试的查询:

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

2 个解决方案

#1

Your query is going to fail in SQL Server regardless of the row_number() call. The * returns all columns, including a.id and b.id. These both have the same name. This is fine for a query, but for a subquery, all columns need distinct names.

无论调用什么row_number()，在SQL Server中查询都将失败。返回所有列，包括a。id和b.id。它们都有相同的名字。这对于查询是可以的，但是对于子查询，所有列都需要不同的名称。

You can use row_number() for an arbitrary ordering by using a "subquery with constant" in the order by clause:

通过在order by子句中使用“带有常量的子查询”，可以对任意排序使用row_number():

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

This removes the dependency on the underlying column name (assuming none are named ROW_NUMBER).

这样就消除了对底层列名的依赖(假设没有一个被命名为ROW_NUMBER)。

#2

Try this sql. It should work.

试试这个sql。它应该工作。

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

#1