带有 order by 子句的 SQL 查询

2021-09-10 00:00:00 sql tsql sql-server

我有一个有 3 列的表格.产品、名称、时间戳.目前，我没有任何 rownumber 列.如果我从表中获取记录，我将使用

I have a table that has 3 columns. Product,Name,TimeStamp. At present, I don't have any rownumber column. If I fetch the record from the table, I will be using 

select * 
from table 
order by Product,Name,TimeStamp.

我会得到一些数据的顺序.按照这个顺序，我需要另一列应该显示行号.简而言之，我需要一个列，该列应该根据上述查询顺序告诉我行号.

I will get some order of data. In that order I need another column that should show the row number. Simply put, I need a column that should tell me the row number based on the above order by query.

是否可以根据某种顺序插入值?在创建这样的表时?

Is it possible to insert values based on some order? while creating table like that? 

OPERATOR    PRODUCT USER NAME   TIME STAMP
1           INS1    1YHS        2018-08-15 09:02:33.000
1           INS1    1YHS        2018-08-15 10:46:17.000
2           INS1    1YHS        2018-08-15 11:01:28.000
2           INS1    1YHS        2018-08-15 17:07:47.000

这里如果操作员为 1，则获取产品 INS1 的许可证，如果操作员为 2，则返回同一产品的许可证.同一个人可以拿更多的执照.第 1 行获取了许可证的详细信息，并返回了相同的许可证，该信息存储在第 3 行中.对于第 2 行，许可证返回信息存储在第 4 行.

Here if the operator is 1, license for product INS1 is taken and if the operator is 2 then the license for the same product is been returned. Same person can take more licenses. 1st row has the details of license been taken and the same license been returned and that information is stored in the 3rd row. for the 2nd row, the license returned information is stored in the 4th row.

我需要像

OPERATOR    PRODUCT USER NAME   TIME STAMP
1           INS1    1YHS        2018-08-15 09:02:33.000
2           INS1    1YHS        2018-08-15 11:01:28.000
1           INS1    1YHS        2018-08-15 10:46:17.000
2           INS1    1YHS        2018-08-15 17:07:47.000

推荐答案

'Transaction' 是一对 take + return.它的身份是根据源数据计算出来的，因此 OPERATOR 可以按照您需要的方式进行分组.查询可能会在具有未配对的 OPERATOR s 的数据上失败.

'Transaction' is a pair of take + return. It's identity is computed from source data so OPERATORs could be grouped the way you need. The query may fail on data with unpaired OPERATORs.

declare @tbl table (
OPERATOR int,   
PRODUCT varchar(50), 
[USER NAME] varchar(100),    
[TIME STAMP] datetime);

insert into @tbl(OPERATOR, PRODUCT, [USER NAME], [TIME STAMP]) values
 (1, 'INS1', '1YHS', '2018-08-15 09:02:33.000')
,(1, 'INS1', '1YHS', '2018-08-15 10:46:17.000')
,(2, 'INS1', '1YHS', '2018-08-15 11:01:28.000')
,(2, 'INS1', '1YHS', '2018-08-15 17:07:47.000');

select OPERATOR, PRODUCT, [USER NAME], [TIME STAMP]
from (
    select OPERATOR, PRODUCT, [USER NAME], [TIME STAMP]
        , row_number() over(partition by PRODUCT, [USER NAME], OPERATOR order by [TIME STAMP]) transId 
    from @tbl) t
order by PRODUCT, [USER NAME], transId, OPERATOR;

相关文章