当查询没有返回任何行时，返回至少X行（空行直到行数为X）

is there a way to get at least X rows? Even when the query returns 0 rows. Example, if a table has 3 rows total, how can i do a SELECT TOP 10 from that table and return 3 rows that has data in it and 7 rows that are all nulls?

有没有办法获得至少X行？即使查询返回0行。例如，如果一个表总共有3行，那么如何从该表中执行SELECT TOP 10并返回包含数据的3行和7行全部为空？

I looked around quite a bit but couldn't find the exact answer I was looking for. I'm using SQL Server 2012 Thanks!

我环顾四周但找不到我想要的确切答案。我正在使用SQL Server 2012谢谢！

3 个解决方案

#1

One method just appends 10 empty rows and then chooses 10 rows from the entire result set:

一种方法只是附加10个空行，然后从整个结果集中选择10行：

select top 10 t.*
from ((select top 10 t.*, 0 as which from t)
      union all
      (select t.*, v.n
       from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(n) left join
            t
            on 0 = 1
      )
     ) t
order by which;

#2

I believe that you will need to use a numbers table as a starting point. Then you can left join to the numbers table in order to achieve this result.

我相信您需要使用数字表作为起点。然后你可以将join连接到数字表以获得这个结果。

WITH CTE_numbers AS (--using a cte as an example of a numbers table
SELECT 1 as rn
UNION ALL
SELECT 2
UNION ALL
SELECT 3)
,
cte_Top as(--Query to select top X number of records
SELECT TOP 3 'testdata' r,ROW_NUMBER() OVER(ORDER BY column) as rn
FROM Table)

SELECT *
FROM CTE_numbers cb
LEFT JOIN cte_Top ct
ON cb.rn = ct.rn

This should give you the correct number of rows even if the query that uses 'TOP' does not have the equal amount of rows in the result. I used top 3 instead of top 10, but you can change the query to fit your needs.

即使使用“TOP”的查询在结果中没有等量的行，这也应该为您提供正确的行数。我使用前三名而不是前十名，但您可以更改查询以满足您的需求。

#3

I ended up using a row over function first, then joined on the table later. Worked like a charm. Thank you for the help!

我最后首先在函数上使用了一行，然后在表中加入。工作就像一个魅力。感谢您的帮助！

#1