精妙SQL

★说明：复制表(只复制结构,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1 

★说明：拷贝表(拷贝数据,源表名：a 目标表名：b) 

SQL: insert into b(a, b, c) select d,e,f from b; 

★说明：显示文章、提交人和最后回复时间 

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 

★说明：外连接查询(表名1：a 表名2：b) 

SQL: select a.f1, a.f2, a.f3, b.f3, b.f4, b.f5 from a left OUT JOIN b ON a.f1 = b.f3 (左连接)
SQL: select a.f1, b.f2 from a FULL OUT JOIN b ON a.f1 = b.f1 (全连接)

★说明：日程安排提前五分钟提醒 

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 

★说明：两张关联表，删除主表中已经在副表中没有的信息 

SQL: 

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

★说明：四表联查问题： 

select f1, (select min(f)-1 from t where f>f1) as f2 from
(select f 1 as f1 from t where f 1 not in (select f from t) and f <(select max(f) from t)) as cc

★说明：-- 

SQL: 

select A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE 
from TABLE1, 
(select X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE 
from (select NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND 
from TABLE2 
where TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, 
(select NUM, UPD_DATE, STOCK_ONHAND 
from TABLE2 
where TO_CHAR(UPD_DATE,'YYYY/MM') = 
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, 
where X.NUM = Y.NUM （ ） 
and X.INBOUND_QTY   NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B 
where A.NUM = B.NUM 

★说明：-- 

SQL: 

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 

★说明： 

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） 

SQL: 

select a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') as telyear, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) as JAN, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) as FRI, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) as MAR, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) as APR, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) as MAY, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) as JUE, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) as JUL, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) as AGU, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) as SEP, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) as OCT, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) as NOV, 
sum(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) as DEC 
from (select a.userper, a.tel, a.standfee, b.telfeedate, b.factration 
from TELFEESTAND a, TELFEE b 
where a.tel = b.telfax) a 
group by a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 

set IDENTITY_INSERT Table1 ON
declare @i int
set @i = 0
update Table1 set @i = @i   1,Field1 = @i
set IDENTITY_INSERT Table1 off

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.zahui.com/html/8/15480.htm
例,检查数据库中的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.SQL简单分页的存储过程    ◆常用◆◆常用◆◆常用◆◆常用◆◆常用◆
/*
create proc recordpages
@nowpage int,
@per int
as
declare @s nvarchar(255)
if @nowpage<1 set @nowpage=1
if @per<1 set @per=1
set @s=N'declare @k int select top ' convert(varchar(10),(@nowpage-1)*@per) ' @k=id from table1
select top ' convert(varchar(10),@per) ' * from table1 where id>@k'
exec sp_executesql @s
go
*/
exec recordpages 3,10

8：得到表中最小的未使用的id号

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 

9.得到表中自动编号列精心策划号的起始位置

SQL: 
select (case when exists(select * from Handle b where b.HandleID = 1) then MIN(HandleID) + 1 else 1 end) as HandleID 
from Handle
where NOT HandleID in (select a.HandleID - 1 from Handle a)

posted on 2008-01-13 00:08 李亚 阅读(201) 评论(0)  编辑 收藏 引用 所属分类: Sql Server