[转载]精妙SQL语句介绍----(5)

查询第xxx行数据 T*stQB  
假设id是主键： :x}!q Ko  
select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) Dz^;;i o  
如果使用游标也是可以的 oQ&FpuwJ|  
fetch absolute [number] from [cursor_name] W-.Ynk~  
行数为绝对行数 OKy L 58  
SQL Server日期计算 ]z?3~S_  
a. 一个月的第一天 M$c0[1$^  
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) 9,XUrH  
b. 本周的星期一 #Qmm)M  
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) CtO{IJG  
c. 一年的第一天 :& K3B  
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) "#]6O$j\  
d. 季度的第一天 .yc&Y 5F  
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) SN(&Xrqo![  
e. 上个月的最后一天 EHP3B!}Z  
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) =b 9Y&Aa  
f. 去年的最后一天 LrP)o]  
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) Mb`p]>8v  
g. 本月的最后一天 G%dE@F]Xa  
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) ejn\*6dgX  
h. 本月的第一个星期一 VHI> gK3  
select DATEADD(wk, DATEDIFF(wk,0, h*sUhs}px  
dateadd(dd,6-datepart(day,getdate()),getdate()) Z>D}Y9@`  
), 0) rZlmg7rQ\  
i. 本年的最后一天 f8ZO8O 69$  
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 ZfCVJH1
 
获取表结构[把 'sysobjects' 替换 成 'tablename' 即可] R+U1 jP  
SELECT CASE IsNull(I.name, '') c
$h=gQ  
When '' Then '' QhkNnB @6  
Else '*' Q U(hE%D&  
End as IsPK, #DX.i00}U  
Object_Name(A.id) as t_name, Q`+l[BcP}  
A.name as c_name, ZW #_<  
IsNull(SubString(M.text, 1, 254), '') as pbc_init, 6"z`_PJO  
T.name as F_DataType, jJ1?[mBn?  
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') tFrV e S  
WHEN '' Then Cast(A.prec as varchar) | r *R"N  
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar) V>H8yVI  
END as F_Scale, E 'dhnF  
A.isnullable as F_isNullAble j28R\Z( `2  
FROM Syscolumns as A Ob"v`)w   
JOIN Systypes as T Pa]+ D  
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') ) . MOY7&7  
LEFT JOIN ( SysIndexes as I Bl(5*^)Bk  
JOIN Syscolumns as A1 :9LY^}Sn3  
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) ) m:O%<@  
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) ) C8
6A9X{"  
LEFT JOIN SysComments as M vA2asO~:&@  
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 ) rm=[+Hm  
ORDER BY A.Colid ASC %u`R' C  
提取数据库内所有表的字段详细说明的SQL语句 yX= 3  
SELECT %wjd>] r  
(case when a.colorder=1 then d.name else '' end) N'表名', R87#=e=  
a.colorder N'字段序号', K8h2u8A
C  
a.name N'字段名', skZak"m  
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' zTj85pS#6E  
end) N'标识', tReM{(sXZ  
(case when (SELECT count(*) A
,
]*%^;  
FROM sysobjects x0ND   
WHERE (name in 7'EHB*)@  
(SELECT name Rz-dG==  
FROM sysindexes  
WHERE (id = a.id) AND (indid in et],RoQ.^  
(SELECT indid &[[5=W   
FROM sysindexkeys 97?.bi/O  
WHERE (id = a.id) AND (colid in %$Lk$2[1r  
(SELECT colid %(U?3&a~.  
FROM syscolumns GK L~QhS5  
WHERE (id = a.id) AND (name = a.name))))))) AND #G&Jy/BSg  
(xtype = 'PK'))>0 then '√' else '' end) N'主键', Y*d`-f1  
b.name N'类型', C$2 )qQh  
a.length N'占用字节数', sDFg^Ka   
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', ]-)!nW  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', +5, l{U  
(case when a.isnullable=1 then '√'else '' end) N'允许空', AsBtn-X>^  
isnull(e.text,'') N'默认值', 9lWpDCWPr  
isnull(g.[value],'') AS N'字段说明' }F-g#~#q0i  
FROM syscolumns a (@U
Jh $a  
left join systypes b I`C n E/V  
on a.xtype=b.xusertype 4i
le(D`  
inner join sysobjects d "d&fPACI  
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'  
left join syscomments e "=~jBww  
on a.cdefault=e.id JB1$
/  
left join sysproperties g vS(z'nV  
on a.id=g.id AND a.colid = g.smallid $](t   
order by object_name(a.id),a.colorder Oak:OIbF{  
快速获取表test的记录总数[对大容量表非常有效] lBq.c  
快速获取表test的记录总数: D4J(c3KrQ  
select rows from sysindexes where id = object_id('test') and indid in (0,1) @2qn+t+y]  
update 2 set KHXH=(ID+1)\2 2行递增编号 hu#%].8  
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增 `,8 Fkd\  
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增 B 6Hx7 N  
delete from [1] where (id%2)=1 奇数 }BAhkub  
替换表名字段 6 %e)G{   
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%' |lAJJTm~  
截位 gPQq$8Ym5  
SELECT LEFT(表名, 5) B  
r^_o9g\;I  
经过对SQLServer2000系统表的分析，写出了以下两个SQL语句。可以把这两个语句分别建为两个“视图”，方便查看用户数据表和字段的信息。 &
XjK1sW  
1、列出所有的用户数据表： 0zo]W4 J  
SELECT TOP 100 PERCENT o.name AS 表名 M=\#~ab"  
FROM dbo.syscolumns c INNER JOIN D l83  
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND F :a."F  
      o.name <> 'dtproperties' LEFT OUTER JOIN )Y]3[
@_  
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder SY2+*h&"W  
WHERE (c.colid = 1) >z~j=t2  
ORDER BY o.name, c.colid Yq[)Y;1Ko  
2、列出所有的用户数据表及其字段信息： c!Q\(+  
SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名, "+tY3u3Q|  
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空, zk6bJ~ZZ{  
      CAST(m.[value] AS Varchar(100)) AS 说明 Euf#:!lwb  
FROM dbo.syscolumns c INNER JOIN T&ubYF_  
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND m|S 8OPc(  
      o.name <> 'dtproperties' INNER JOIN bl/szk}sO  
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN T6!$MJi^(  
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder W6'`.bS  
ORDER BY o.name, c.colid e/':2.