从多个 SQL Server 表中选择 TOP 4 记录.使用 vb.net

2021-09-16 00:00:00 vb.net sql-server

我有大约 4 个具有完全相同列名的不同表.我想要做的是从所有这些按日期排序的表中选择前 4 条记录(因为日期是它们共享的列之一).

I have about 4 different tables with the exact same column names. What I would like to do is select the top 4 records out of all of these tables combined ordered by date (as date is one of the columns that they all share).

我不断收到错误的陈述，无论是语法问题还是含糊不清的记录等.

I keep getting erroneous statements, whether it be a syntax issue or ambiguous record etc.

基本上我的陈述类似于:

Essentially my statement is similar to:

SELECT TOP 4 date, link, anchor, thumb FROM archive1, archive2, archive3, archive4 ORDER BY date DESC

显而易见的是，我对所有这些东西都不熟悉.在此先感谢您的帮助.

To state the obvious, I'm new to all this stuff. Thanks in advance for any assistance.

推荐答案

您需要制作 union 所有表，然后对它们进行排序以获得最后四个:

You need to produce union of all tables and then order them to get last four:

SELECT TOP 4 date, link, anchor, thumb 
FROM
(
  SELECT date, link, anchor, thumb 
    FROM archive1
   UNION ALL
  SELECT date, link, anchor, thumb 
    FROM archive2
   UNION ALL
  SELECT date, link, anchor, thumb 
    FROM archive3
   UNION ALL
  SELECT date, link, anchor, thumb 
   FROM archive4 
) archive
ORDER BY date DESC

由于您只获取 4 条记录，您可以向每个查询添加 TOP 4 子句以及 ORDER BY 日期 DESC 以加快速度.

As you only take four records you might add TOP 4 clause to each query along with ORDER BY date DESC to speed things up.

相关文章