SQL语句特殊技巧之 SQL Server

KeyLife富翁笔记

作者: HongYuan
标题: SQL语句特殊技巧之 - SQL Server
关键字:
分类: sql server 2000
密级: 私有

(评分: , 回复: 0, 阅读: 2) »»

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

待续...



2003-10-6 13:53:00    
 发表评语&raquo;&raquo;&raquo;    

 2003-12-6 19:36:00    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;
http://www.delphibbs.com/delphibbs/dispq.asp?lid=2290831

 
 2005-10-10 12:56:05    再来一个排名的,没有排名字段查询排名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扫描源表
问题见:http://www.delphibbs.com/delphibbs/dispq.asp?lid=3231593

修改一下以适应于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  

2005-10-24 14:07:57