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