精妙SQL语句大全
上一篇 / 下一篇 2008-08-09 19:13:33 / 个人分类:数据库相关
SQL: select * into b from a where 1<>151Testing软件测试网#X
V2BlMO!|"O8fc
uRiw!za0 说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
q z_8V4Ohx5I0
p+w,[,|Q%SU0 SQL: insert into b(a, b, c) select d,e,f from a;
lv7^'j2`V'g0 51Testing软件测试网'|]b;j F
说明:显示文章、提交人和最后回复时间
bwdE,S0 51Testing软件测试网gVF ~3kE3ql&P&b|
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate)
/A$c+f^Q0 adddate from table where table.title=a.title) b
\9Y|Mn8eRy0 51Testing软件测试网+v%{ m'\+a2p|-YT:~
说明:外连接查询(表名1:a 表名2:b)51Testing软件测试网A[{ n@h;p~
\O {!C;`l v}o0 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =51Testing软件测试网*^Xk L-s.BM
b.c
J aFI2q-}xV&s0 51Testing软件测试网1K T8M.n oGRmT
说明:日程安排提前五分钟提醒
2rP!eS?8?W|0 51Testing软件测试网k+? U mz
SQL: select * from 日程安排 where
7QvF*S Z2] N0 datediff(’minute’,f开始时间,getdate())>551Testing软件测试网(r!`t$u t?7L!a
51Testing软件测试网j jO]1}
说明:两张关联表,删除主表中已经在副表中没有的信息51Testing软件测试网']C$_*tz#yM2v-D
4v*\Uv cF8^:~-H0 SQL:51Testing软件测试网'^l]9cs"T d
TkQ9biJBTV0 delete from info where not exists ( select * from infobz where51Testing软件测试网 zJ&d,T]yG6p7ZT
info.infid=infobz.infid )51Testing软件测试网#m$bD:MLh\
$~6Zos_)k@dp0 说明:--51Testing软件测试网(`~L ](l"l c(`
51Testing软件测试网"AK4U#V2s
SQL:
-_[/UP~U0 51Testing软件测试网^4m-UrDP[
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
5{4p,g^f$W0 51Testing软件测试网`:`t`J8N2w2j+k
FROM TABLE1,51Testing软件测试网)dY8Ki aE/p b/X)y
51Testing软件测试网4sJ:pTs1NDT`
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE51Testing软件测试网mN*T(] V'Y3XMx0mR
51Testing软件测试网4T4auBJg
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND51Testing软件测试网](`,~(s?+Z5V/kO
51Testing软件测试网\Xc_^F
FROM TABLE251Testing软件测试网 K&v'Q.v&Wp+_D#Z#n
51Testing软件测试网 G,Oy(c4BI
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE,51Testing软件测试网d| ?b2vC
’YYYY/MM’)) X,51Testing软件测试网g8b/q7gw
t x/Ks|%?%w-A0 (SELECT NUM, UPD_DATE, STOCK_ONHAND51Testing软件测试网:TFkyB4g-u
Q@.gYbIXr&D0 FROM TABLE251Testing软件测试网*Y1^j{\&g
51Testing软件测试网f8@(wR8b\*A7Q8Fo
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =51Testing软件测试网U H'|2MW[7uod
5Qz(T*O2H^ N-hC0QN0 TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ’YYYY/MM’) ||51Testing软件测试网 q HYiV(GM/_G
’/01’,’YYYY/MM/DD’) - 1, ’YYYY/MM’) ) Y,
wk:Yu"@_ K4D0
uRiw!za0 说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
q z_8V4Ohx5I0
p+w,[,|Q%SU0 SQL: insert into b(a, b, c) select d,e,f from a;
lv7^'j2`V'g0 51Testing软件测试网'|]b;j F
说明:显示文章、提交人和最后回复时间
bwdE,S0 51Testing软件测试网gVF ~3kE3ql&P&b|
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate)
/A$c+f^Q0 adddate from table where table.title=a.title) b
\9Y|Mn8eRy0 51Testing软件测试网+v%{ m'\+a2p|-YT:~
说明:外连接查询(表名1:a 表名2:b)51Testing软件测试网A[{ n@h;p~
\O {!C;`l v}o0 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =51Testing软件测试网*^Xk L-s.BM
b.c
J aFI2q-}xV&s0 51Testing软件测试网1K T8M.n oGRmT
说明:日程安排提前五分钟提醒
2rP!eS?8?W|0 51Testing软件测试网k+? U mz
SQL: select * from 日程安排 where
7QvF*S Z2] N0 datediff(’minute’,f开始时间,getdate())>551Testing软件测试网(r!`t$u t?7L!a
51Testing软件测试网j jO]1}
说明:两张关联表,删除主表中已经在副表中没有的信息51Testing软件测试网']C$_*tz#yM2v-D
4v*\Uv cF8^:~-H0 SQL:51Testing软件测试网'^l]9cs"T d
TkQ9biJBTV0 delete from info where not exists ( select * from infobz where51Testing软件测试网 zJ&d,T]yG6p7ZT
info.infid=infobz.infid )51Testing软件测试网#m$bD:MLh\
$~6Zos_)k@dp0 说明:--51Testing软件测试网(`~L ](l"l c(`
51Testing软件测试网"AK4U#V2s
SQL:
-_[/UP~U0 51Testing软件测试网^4m-UrDP[
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
5{4p,g^f$W0 51Testing软件测试网`:`t`J8N2w2j+k
FROM TABLE1,51Testing软件测试网)dY8Ki aE/p b/X)y
51Testing软件测试网4sJ:pTs1NDT`
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE51Testing软件测试网mN*T(] V'Y3XMx0mR
51Testing软件测试网4T4auBJg
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND51Testing软件测试网](`,~(s?+Z5V/kO
51Testing软件测试网\Xc_^F
FROM TABLE251Testing软件测试网 K&v'Q.v&Wp+_D#Z#n
51Testing软件测试网 G,Oy(c4BI
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE,51Testing软件测试网d| ?b2vC
’YYYY/MM’)) X,51Testing软件测试网g8b/q7gw
t x/Ks|%?%w-A0 (SELECT NUM, UPD_DATE, STOCK_ONHAND51Testing软件测试网:TFkyB4g-u
Q@.gYbIXr&D0 FROM TABLE251Testing软件测试网*Y1^j{\&g
51Testing软件测试网f8@(wR8b\*A7Q8Fo
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =51Testing软件测试网U H'|2MW[7uod
5Qz(T*O2H^ N-hC0QN0 TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ’YYYY/MM’) ||51Testing软件测试网 q HYiV(GM/_G
’/01’,’YYYY/MM/DD’) - 1, ’YYYY/MM’) ) Y,
wk:Yu"@_ K4D0