Returning a list of numbers up to a set value

问题:

I've seen the following used to return a list of numbers

SELECT TOP (SELECT MAX(Quantity) FROM @d) 
       rn = ROW_NUMBER() OVER (ORDER BY object_id)    
FROM   sys.all_columns 
ORDER BY object_id  

if the max quantity is 5 then I assume the above returns:

rn
1
2
3
4
5

Is there a more elegant way of returning this list of numbers?

回答1:

You can do:

SELECT        rn = 1 
  UNION ALL SELECT 2 
  UNION ALL SELECT 3 
  UNION ALL SELECT 4 
  UNION ALL SELECT 5;

This is tolerable when the number is 5, but not 50 or 5000. When you need more you can do things like use a CTE to build up a set of numbers to then cross join to explode the set (you can see a couple of examples here, under Inline 1 / Inline 2).

Or you can build a table of Numbers, let's say you may need 5 or you may need a million:

SET NOCOUNT ON;

DECLARE @UpperLimit INT = 1000000;

WITH n AS
(
    SELECT
        x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM       sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    CROSS JOIN sys.all_objects AS s3
)
SELECT Number = x
  INTO dbo.Numbers
  FROM n
  WHERE x BETWEEN 1 AND @UpperLimit;

GO
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number);
GO

Then when you want some numbers you just say:

SELECT TOP (5) rn = Number 
  FROM dbo.Numbers 
  ORDER BY Number;

Obviously using sys.all_columns or any built-in object with sufficient rows avoids the up-front step of creating a Numbers table (which many people object to, for some reason, anyway).

Now, it would be really nice if there were a more elegant way to do this, wouldn't it? You won't see it in any current version but there's a chance we'll see it in a future version. Please go vote (and more importantly, comment on your use case) here:

http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-table-of-numbers

回答2:

I've used something like this in the past -- though it only works up to 100 or so:

WITH Numbers(number) AS (
SELECT 1 number
UNION ALL
SELECT number+1 FROM Numbers WHERE number < 10)
SELECT * FROM Numbers

回答3:

First create a table of numbers 0 - 9

create table dbo.Digits (digit tinyint not null Primary Key) 
insert into dbo.Digits values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)

Then you can produce the following cross join to count from 1 to 100:

select
D2.digit * 10 + 
D1.digit + 1 as n
from dbo.Digits D1,
dbo.Digits D2
order by n

To count from 1 to 1000, you simply need to add an additional cross join:

select
D3.digit * 100 +
D2.digit * 10 + 
D1.digit + 1 as n
from dbo.Digits D1,
dbo.Digits D2,
dbo.Digits D3
order by n

To count from 1 to x (where x <= 1000)::

select top x
D3.digit * 100 +
D2.digit * 10 + 
D1.digit + 1 as n
from dbo.Digits D1,
dbo.Digits D2,
dbo.Digits D3
order by n

回答4:

Definitely not the most elegant, but probably the fastest way for larger sequences:

WITH
 E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1),                 --10E1  or 10 rows
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E2  or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E3  or 10000 rows
 E8(N) AS (SELECT 1 FROM E4 a, E4 b)  --10E4  or 100000000 rows
SELECT TOP (@DesiredRowCount) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8
;

No temp tables, no table reads, and you can define your maximum yourself.

You can copy-paste the ready-to-use function from here

回答5:

Modify the Top value as required:

SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY [object_id]) 
FROM sys.all_objects

e.g. A dropdown list showing a range of NULL, 1 - 30 for a list of years of education.

<tr>
<td>Education</td>
<td>
<asp:DropDownList ID="EducationDropDownList" runat="server" DataSourceID="sd6"
 DataValueField="Education" DataTextField="Education" 
 SelectedValue='<%# Bind("Education") %>' CssClass="metro" />
<asp:SqlDataSource ID="sd6" runat="server" 
 ConnectionString="<%$ ConnectionStrings:cnYours %>" 
 SelectCommand="SELECT NULL AS Education UNION ALL SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects" 
 SelectCommandType="Text" />
</td>
</tr>

回答6:

If you only wanted your output to contain a running number as an additional column, the following would work as well:

select row_number() over (order by (Select 1)) AS RowNum, * from Table_1 where Column_3 = 372 and Column_6 = 2017;