sql - 如何从表中仅选择顶部和底部行

Question

我正尝试从下表中选择顶行和底行，但收到​​错误消息:

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'UNION'

“UNION”的正确使用方法是什么？

Use DATABASE
Go
declare @Volume VARCHAR(512)
set @Volume = '599722'


SELECT    
TOP (1)
[ARCHIVE_HEADER].archive as FirstArchive,  
[ARCHIVE_HEADER].cr_time,
[ARCHIVE_HEADER].group_name,
[VOLUME].vol_name,
[ARCHIVE_HEADER].display_name
FROM            
ARCHIVE_HEADER INNER JOIN VOLUME
ON ARCHIVE_HEADER.volume_key = VOLUME.volume_key
WHERE        
VOLUME.vol_name = @Volume
ORDER BY
ARCHIVE_HEADER.archive ASC

UNION

SELECT    
TOP (1)
[ARCHIVE_HEADER].archive as FirstArchive,  
[ARCHIVE_HEADER].cr_time,
[ARCHIVE_HEADER].group_name,
[VOLUME].vol_name,
[ARCHIVE_HEADER].display_name
FROM            
ARCHIVE_HEADER INNER JOIN VOLUME
ON ARCHIVE_HEADER.volume_key = VOLUME.volume_key
WHERE        
VOLUME.vol_name = @Volume
ORDER BY
ARCHIVE_HEADER.archive DESC

从性能上来说，这个结果要查询同一张表两次，有没有更好的逻辑只能查询一次相同的结果？

Answer 1

我认为使用单个查询会更简单:

SELECT FirstArchive, cr_time, group_name, vol_name, display_name
FROM (SELECT ah.archive as FirstArchive, ah.cr_time, ah.group_name,
             v.vol_name, ah.display_name,
             ROW_NUMBER() OVER (PARTITION BY v.vol_name ORDER BY ah.archive ASC) as seqnum_asc,
             ROW_NUMBER() OVER (PARTITION BY v.vol_name ORDER BY ah.archive DESC) as seqnum_desc
      FROM ARCHIVE_HEADER ah INNER JOIN
           VOLUME v
           ON ah.volume_key = v.volume_key
      WHERE v.vol_name = @Volume
     ) ah
WHERE 1 IN (seqnum_asc, seqnum_desc)
ORDER BY ah.archive ASC;

不需要 UNION ALL 两个子查询。

注意:这与您的查询有一处不同。如果 volume 只有一行，则返回一行而不是两行。这似乎是一种改进。