by和union的组合

wooyq4lh  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(365)

如何在一个结果集中获得表的第一条记录和最后一条记录?
此查询失败

SELECT TOP 1 Id,Name FROM Locations ORDER BY Id
UNION ALL
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC

有什么帮助吗?

vdgimpew

vdgimpew1#

把你的 order by 以及 top 语句到子查询:

select first.Id, first.Name 
from (
    select top 1 * 
    from Locations 
    order by Id) first
union all
select last.Id, last.Name 
from (
    select top 1 * 
    from Locations 
    order by Id desc) last
jtoj6r0c

jtoj6r0c2#

select * from (
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id) X
UNION ALL
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC
ctrmrzij

ctrmrzij3#

如果您使用的是sql server 2005或更高版本:

; WITH NumberedRows as (
    SELECT Id,Name,
       ROW_NUMBER() OVER (ORDER BY Id) as rnAsc,
       ROW_NUMBER() OVER (ORDER BY Id desc) as rnDesc
    FROM
        Locations
)
select * from NumberedRows where rnAsc = 1 or rnDesc = 1

唯一不同于原始查询的地方是如果表中只有一行(在这种情况下,我的答案返回一行,而你的答案将返回同一行两次)

zc0qhyus

zc0qhyus4#

SELECT TOP 1 Id as sameColumn,Name FROM Locations 
UNION ALL
SELECT TOP 1 Id as sameColumn,Name FROM Locations ORDER BY sameColumn DESC

相关问题