SQL query with order by clause

问题:

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

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.

I need to show the table like

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

回答1:

'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;

回答2:

Most RDBMS have a ROW_NUMBER() window function (a notable exception is MySQL prior to version 8.0):

select 
    t.* ,
    row_number() over(order by Product ,Name, TimeStamp) rn
from table t
order by Product, Name, TimeStamp