精妙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’) 

1.把某个字段重新生起序列(从1到n):  

 

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)