当前位置：文江博客话题详情

具有增量整数列的 MSSQL Select 语句...不是来自表

发布于 2024-07-13 23:45:56 字数 189 浏览 14 评论 0原文

如果可能的话，我需要一个 t-sql 查询，该查询从任意表返回值，还返回一个增量整数列，第一行值为 1，第二行值为 2，依此类推。

该列实际上并不驻留在任何表中，并且必须是严格增量的，因为 ORDER BY 子句可以对表的行进行排序，并且我希望增量行始终保持完美的形状。

该解决方案必须在 SQL Server 2000 上运行

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苄①跕圉湢 2024-07-20 23:45:56

对于 SQL 2005 及更高

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

版本的 2000，您需要执行类似的操作，

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

另请参阅此处行号

For SQL 2005 and up

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

for 2000 you need to do something like this

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

see also here Row Number

回复收藏 0 原文

弄潮 2024-07-20 23:45:56

您可以从自定义号码开始，然后从那里递增，例如，您想要为您可以执行的每笔付款添加支票号码：

select @StartChequeNumber = 3446;
SELECT 
((ROW_NUMBER() OVER(ORDER BY AnyColumn)) + @StartChequeNumber ) AS 'ChequeNumber'
,* FROM YourTable

将为每行提供正确的支票号码。

You can start with a custom number and increment from there, for example you want to add a cheque number for each payment you can do:

select @StartChequeNumber = 3446;
SELECT 
((ROW_NUMBER() OVER(ORDER BY AnyColumn)) + @StartChequeNumber ) AS 'ChequeNumber'
,* FROM YourTable

will give the correct cheque number for each row.

回复收藏 0 原文

静谧幽蓝 2024-07-20 23:45:56

尝试 ROW_NUMBER()

http://msdn.microsoft.com/en-us/library /ms186734.aspx

示例：

SELECT
  col1,
  col2,
  ROW_NUMBER() OVER (ORDER BY col1) AS rownum
FROM tbl

Try ROW_NUMBER()

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Example:

SELECT
  col1,
  col2,
  ROW_NUMBER() OVER (ORDER BY col1) AS rownum
FROM tbl

回复收藏 0 原文

沒落の蓅哖 2024-07-20 23:45:56

它很丑陋并且性能很差，但从技术上讲，这适用于任何具有至少一个唯一字段的表，并且适用于 SQL 2000。

SELECT (SELECT COUNT(*) FROM myTable T1 WHERE T1.UniqueField<=T2.UniqueField) as RowNum, T2.OtherField
FROM myTable T2
ORDER By T2.UniqueField

注意：如果您使用此方法并向外部 SELECT 添加 WHERE 子句，则必须将其添加到如果您希望数字连续，也可以使用内部 SELECT。

It is ugly and performs badly, but technically this works on any table with at least one unique field AND works in SQL 2000.

SELECT (SELECT COUNT(*) FROM myTable T1 WHERE T1.UniqueField<=T2.UniqueField) as RowNum, T2.OtherField
FROM myTable T2
ORDER By T2.UniqueField

Note: If you use this approach and add a WHERE clause to the outer SELECT, you have to added it to the inner SELECT also if you want the numbers to be continuous.

回复收藏 0 原文

~没有更多了~