如何在tsql中生成连续的行号? [英] How to generate sequential row number in tsql?

问题描述

我在报告中要求在行中显示替代颜色，为此我需要在 SQL Select 语句(参见下面的示例)中生成序列号，以便稍后在显示行时使用.我正在尝试 row_number 和其他一些技术，但它不起作用.这不应该使用脚本来完成，我应该能够在 Select 语句中生成.感谢任何帮助.

I have a requirement in a report to show alternate colors in row and for this I need to generate sequential numbers in a SQL Select statement (see example below) to use later while displaying rows. I am trying row_number and some other techniques its not working. This should not be done using script, I should be able to generate within Select statement. Appreciate any help.

RowNumber - 1, Otherdata - Something1
RowNumber - 2, Otherdata - Something2
RowNumber - 3, Otherdata - Something3
RowNumber - 4, Otherdata - Something4
RowNumber - 5, Otherdata - Something5

推荐答案

如果您的数据库支持分析函数，例如 ROW_NUMBER()

There is no need to avoid Analytic Functions if your database supports them e.g ROW_NUMBER()

    SELECT
        ROW_NUMBER() OVER (ORDER BY [<PRIMARYKEY_COLUMN_NAME>]) AS Number
    FROM
        [<TABLE_NAME>]

语法是Func([arguments])OVER(analytic_clause)你需要重点关注OVER().最后一个括号对您的行进行分区，并在这些分区上一一应用 Func().在上面的代码中，我们只有一组/分区的行.因此生成的序列适用于所有行.

The syntax is Func([ arguments ]) OVER (analytic_clause) you need to focus on OVER (). This last parentheses make partition(s) of your rows and apply the Func() on these partitions one by one. In above code we have only single set/partition of rows. Therefore the generated sequence is for all the rows.

您可以一次制作多组数据并为每组生成序列号.例如，如果您需要为所有具有相同 categoryId 的行集生成序列号.您只需要添加这样的 Partition By 子句 (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).

You can make multiple set of your data and generate sequence number for each one in a single go. For example if you need generate sequence number for all the set of rows those have same categoryId. You just need to add Partition By clause like this (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).

请记住，在 FROM 之后，您还可以使用另一个额外的 ORDER BY 以不同方式对数据进行排序.但是对OVER()没有影响

Remember that after FROM you can also use another extra ORDER BY to sort your data differently. But it has no effect on the OVER ()

这篇关于如何在tsql中生成连续的行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！