問題描述

我有一個有 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;

這篇關于帶有 order by 子句的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！