Original posted: http://community.9cbs.net/expert/topic/3714/3714430.xml? Temp = .0695917 Can you add a column value within the query result, the value identifies the recorded record Line number? Such as: AA BB CC 21 53 46 34 34 54 67 457 354 Change to line aa bb cc 1 21 53 46 2 34 34 54 3 67 457 354
- Solution 1: If the AA BB CC can uniquely determine a record, then: Create Table Table (AA VARCHAR (20), BB VARCHAR (20), CC VARCHAR (20)) Insert Table Select '21', '53 ',' 46'Union all select '34', '34', '54'Union all Select' 67 ',' 457 ',' 354'GO
SELECT line = (Select Sum (1) from WHERE AA '_' BB '_' CC <= a.aa '_' a.bb '_' a.cc), * from Table A
Drop Table Table - Test Results (the number of rows affected is 3 lines)
LINE AA BB CC --------------------------------------------------------- ----- -------------------- 1 21 53 462 34 34 543 67 457 354
(The number of rows affects 3 lines) - Solution 2, add a temporary table to create a list of column fields (aa varchar (20), bb varchar (20), cc varchar (20)) INSERT table select '21 ',' 53 ',' 46'Union all Select '34', '34', '54'Union All Select' 67 ',' 457 ',' 354'Goselect Identity (Int, 1, 1) As line, * INTO #TFROM table Select * from # T - Results: Line aa bb cc ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- 1 21 53 462 34 34 543 67 457 354 ( The number of rows affects 3 lines) - Solution 3: After adding a field in a physical table, finally remove the Create Table table (Aa Varchar (20), BB VARCHAR (20), cc varchar (20)) Insert table Select '21', '53', '46' EL SELECT' 34 ',' 34 ',' 54'Union All Select '67', '457', '354'GO
ALTER TABLE Table Add Line Int Id Idness (1,1) SELECT * FROM Table ALTER TABLE Table DROP Column Line
Drop Table Table - Result: (The number of rows affects is 3 lines)
(The number of rows affects is 3 lines)
AA BB CC Line ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------- 21 53 46 134 34 54 267 457 354 3
(The number of rows affects is 3 lines)
