If the data table itself does not contain fields with auto-increment numbers, how can we enable the SELECT query result to add auto-increment numbers? We provide the following five methods for your reference:
1 USE
2 GO
3
4/* method 1 */
5
6 SELECT number = (select count (customer number) FROM customer AS LiMing
7 WHERE LiMing. Customer No. <= Chang. Customer No ),
8. Customer ID, company name
9 FROM customer AS Chang order by 1;
10 GO
11
12/* Method 2: Use the RANK () OVER () syntax unique to SQL Server 2005 */
13 select rank () OVER (order by customer No. DESC) AS No,
14 customer ID, company name
15 FROM customer;
16 GO
17
18/* method 3 */
19 SELECT No. = COUNT (*), LiMing. Customer No., LiMing. Company Name
20 FROM customer AS LiMing, customer AS Chang
21 WHERE LiMing. Customer No.> = Chang. Customer No.
22 group by LiMing. Customer ID, LiMing. Company Name
23 order by no;
24 GO
25
26/* method 4
27. Create an automatic number field and add the data to a regional temporary data table,
28. Then, select the data from the saved data table in the region, and delete the temporary data table in the region.
29 */
30 SELECT No. = IDENTITY (INT,), pipeline, programming language, Lecturer, qualifications
31 INTO # LiMing
32 FROM coupon survey 1;
33 GO
34 SELECT * FROM # LiMing;
35 GO
36 drop table # LiMing;
37 GO
38
39 /*
40 Method 5
41 use the ROW_NUMBER () OVER () syntax exclusive to SQL Server 2005
42. Use CTE (a common data table expression, that is, the WITH syntax) to select the sequence number 2 ~ 4 Data
43 */
44 WITH sorted books
45 (SELECT ROW_NUMBER () OVER (order by customer No. DESC) AS No,
46 customer ID, company name
47 FROM customer)
48 SELECT * FROM sorted books
49 WHERE no. BETWEEN 2 AND 4;
50 go
51
52
53
