收集整理—SQL Server技巧

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


7.字符串之Sum

例如，有个表
ID         NAME
------------------
1             T
2             H
3             A
4             N
5             K
要得到
THANK

declare @s varchar(100);
set @s='';
select @s=@s+[Name] from 表 order by id;
select @s;

再来一个排名的,没有排名字段查询排名

create table t1
(khid varchar(10), xsje money)

insert into t1 values ('001',100)
insert into t1 values ('002',105)
insert into t1 values ('003',220)
insert into t1 values ('004',89)
insert into t1 values ('001',150)
insert into t1 values ('002',50)
insert into t1 values ('003',38)

select (
select count(*) + 1 from (
select khid,sum(xsje) as xsje from t1 group by khid
) b where a.xsje < b.xsje
) mc,* from (
select khid,sum(xsje) as xsje from t1 group by khid
) a order by mc

优点:允许并列排名,纯查询
缺点:要做两次sum扫描源表

修改一下以适应于ACCESS
select (select count(*) + 1 from (select khid,sum(xsje) as xsje_sum from t1 group by khid) as b where a.xsje_sum < b.xsje_sum) as mc,*

from (select khid,sum(xsje) as xsje_sum from t1 group by khid) as a order by xsje_sum desc


8:用扩展存储过程判断文件是否存在
DECLARE @result int
EXEC @result = xp_cmdshell 'dir c:\boot.ini'
IF (@result = 0)
select '存在'
ELSE
select '不存在'