SQL Server skills

1. Repeat a field from 1 to n ):
Declare @ I int
Set @ I = 0
Update Table1 set @ I = @ I + 1, field1 = @ I

2. ranking by score
Update Tables
Set a. Ranking = (
Select count (*) + 1
From orders Table B
Where a. Total score <B. Total score
)
From orders Table

3. query external databases
Select .*
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/test. mdb'; 'admin'; '', Table1)

4. query Excel files
Select *
From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C:/test.xls"; user id = admin; Password =; extended properties = Excel 8.0 ')... sheet1 $

5. Specify the sorting rule in the query.
Select * From Table1 order by field1 collate chinese_prc_bin
Why do we need to specify sorting rules? See:
Http://www.delphibbs.com/delphibbs/dispq.asp? Lid = 1633985
For example, check whether the pub_users table in the database has a specified user:
Select count (*) from pub_users where [username] = 'admin' and [Password] = 'aaa' collate chinese_prc_bin
The default comparison is case-insensitive. If collate chinese_prc_bin is not added, the password AAA is equivalent to AAA, which is of course not consistent with the actual situation. note that the sorting rules must be specified for each condition. The user names in the preceding example are case insensitive.

6. A tips for order
Order by can specify the column order without specifying the column name. In the following example, it is useful (note that the third column does not specify an alias)
Select a. ID, A. Name, (select count (*) from tableb B where a. ID = B. PID) from tablea a order by 3