SQL语句返回错误的值(TOP n ... ORDER BY) [英] SQL statement returns wrong values (TOP n ... ORDER BY)

问题描述

我尝试使用此查询获取表的最后一行和下一行:

I tried to get the the last and next row of my table by using this query:

SELECT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
UNION 
SELECT TOP 2 Nummer FROM Empfänger WHERE Nummer >= 4748 ORDER BY Nummer ASC;

主键"Nummer"是唯一且自动递增的，因此有

The primary key "Nummer" is unique and auto-incremental , so there is

4747, 4748, 4749

它应该恕我直言返回上述数字，但它返回:

It should imho return the above stated numbers, but instead it returns:

4747, 4798, 4799

当我使用Access打开数据库并将过滤器设置为之间"时，值将正确显示.

When I open the Database with Access and set the filter "between" the values are shown correctly.

我将声明更改为:

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
UNION 
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4748 
UNION 
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4748 ORDER BY Nummer ASC;

这几乎可以正常工作.

4747, 4748, 4750

当我在单个查询中编写每条语句时，它会返回正确的值...

When I write every statement in a single query it returns the right values...

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4748 ORDER BY Nummer DESC
= 4747
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4748 
= 4748
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4748 ORDER BY Nummer ASC;
= 4749

\

SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer < 4750 ORDER BY Nummer DESC
UNION 
SELECT DISTINCT Nummer FROM Empfänger WHERE Nummer = 4750 
UNION 
SELECT DISTINCT TOP 1 Nummer FROM Empfänger WHERE Nummer > 4750 ORDER BY Nummer ASC;

我再次检查了一下，上面显示的查询突然起作用了. 谢谢您的帮助.

I checked it again, suddenly the above shown query worked. Thank you for your help.

推荐答案

Access使用JET引擎. 似乎 ORDER BY应用于 AFTER TOP子句，您将需要使用以下形式的子查询

Access uses the JET engine. It seems that ORDER BY is applied AFTER the TOP clause and you will need to use subqueries of the form

select top n ...
from (
  select ...
  from table
  where ...
  order by ...
)

使其正常工作.

这篇关于SQL语句返回错误的值(TOP n ... ORDER BY)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！