收藏的SQLServer技巧集

上次写了个access技巧集,反映良好,这次来个sql语句特殊技巧吧

1.把某个字段重新生气序列(从1到n):
declare @i int
set @i = 0
update table1 set @i = @i + 1,field1 = @i

2.按成绩排名次
update 成绩表
set a.名次 = (
select count(*) + 1
from 成绩表 b
where a.总成绩 < b.总成绩
)
from 成绩表 a

3.查询外部数据库
select a.*
from openrowset('microsoft.jet.oledb.4.0','c:\test.mdb';'admin';'',table1) a

4.查询excel文件
select * 
from opendatasource('microsoft.jet.oledb.4.0','data source="c:\test.xls";user id=admin;password=;extended properties=excel 8.0')...sheet1$

5.在查询中指定排序规则
select * from table1 order by field1 collate chinese_prc_bin
为什么要指定排序规则呢?参见:
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1633985
例,检查数据库中的pub_users表中是否存在指定的用户:
select count(*) from pub_users where [username]='admin' and [password]='aaa' collate chinese_prc_bin
默认比较是不区分大小写的,如果不加collate chinese_prc_bin,那么密码aaa与aaa是等效的,这当然与实际不符.注意的是,每个条件都要指定排序规则,上例中用户名就不区分大小写.


6.order by的一个小技巧
order by可以指定列序而不用指定列名,在下面的例子里说明它的用处(注意,第三列未指定别名)
select a.id,a.name,(select count(*) from tableb b where a.id=b.pid) from tablea a order by 3