如何在SQL中获取UNION的前10个结果？

Question

我目前在一个存储过程中使用以下代码（见下文）。为了返回总共10个结果，我取每个联合的前5个。然而，我想要取UNION的前10个，而不是每个的前5个。有什么想法吗？这可行吗？

BEGIN
SELECT TOP 5
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'

UNION ALL

SELECT TOP 5 
    b_object_ID as [id], 
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'

ORDER BY [name]
END

- WEFX

---

为什么你想在 SQL 中完成它，却打上 Linq-to-SQL 的标签？ - BrokenGlass

抱歉@BrokenGlass，我已经移除了标签。 - WEFX

Answer 1

非常可能。您只需要从联合结果中选择前10个。

SELECT TOP 10 * FROM (
    SELECT
        a_object_ID as [id], 
        a_object_name as [name],
        'A_object' as [Type]
    FROM [database].[dbo].[table_a]
    WHERE a_object_name LIKE @Search + '%'

    UNION ALL

    SELECT
        b_object_ID as [id], 
        b_object_name as [name],
        'B_object' as [Type]
    FROM [database].[dbo].[table_b]
    WHERE b_object_name LIKE @Search + '%'
) u
ORDER BY u.[name]

- dotjoe

---

感谢 @dotjoe，我选择了 @AdaTheDev，因为他的回答比你的早到。 - WEFX

Answer 2

将您的UNION作为子查询处理

SELECT TOP 10 * 
FROM (
  SELECT
      a_object_ID as [id], 
      a_object_name as [name],
      'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'

  UNION ALL

  SELECT
      b_object_ID as [id], 
      b_object_name as [name],
      'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
) AS subquery
ORDER BY subquery.[name]

- Colin Mackay

Answer 3

这个怎么样？

SELECT TOP 10 *
FROM
(
SELECT a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
UNION ALL
SELECT b_object_ID as [id], 
    b_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_b]
WHERE b_object_name LIKE @Search + '%'
) x
ORDER BY [name]

- AdaTheDev

Answer 4

为什么要把查询搞得复杂呢？这样就可以了：

set rowcount 10

  SELECT a_object_ID as [id], 
         a_object_name as [name],
         'A_object' as [Type]
  FROM [database].[dbo].[table_a]
  WHERE a_object_name LIKE @Search + '%'
UNION ALL
  SELECT b_object_ID as [id], 
         b_object_name as [name],
         'B_object' as [Type]
  FROM [database].[dbo].[table_b]
  WHERE b_object_name LIKE @Search + '%'
ORDER BY [name]

set rowcount 0

- Nicholas Carey

Answer 5

上述答案是浪费的，因为子查询会收集然后拒绝可能大量的行。您可以使用子查询改善问题。

declare @Recs int = 10;

with cte1 as (
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'A_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
, cte2 as
(
SELECT TOP (@Recs)
    a_object_ID as [id], 
    a_object_name as [name],
    'B_object' as [Type]
FROM [database].[dbo].[table_a]
WHERE a_object_name LIKE @Search + '%'
)
SELECT TOP (@Recs) [id], [name], [Type]
(
select [id], [name], [Type] from cte1
union all
select [id], [name], [Type] from cte2
) SQ
ORDER BY [Name]

- WaitForPete