在网上经常转,常常看到有些人为了求得某些SQL语句而焦头烂额,现在我特别把自己收藏的一些比较精典的SQL拿出来和大家分享一下
1. 行列转换--普通
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)
2. 行列转换--合并
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
3. 如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
是不是太简单了？ 呵呵 不过经常用阿.
4. 通过SQL语句来更改用户的密码
修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'
如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa
5. 怎么判断出一个表的哪些字段不允许为空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'
b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
7. 查询第xxx行数据
假设id是主键：
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)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数
8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
一、得到一个表中所有的列名select name as '列名' from syscolumns where id=object_id('subject_name')
二、得到一个数据库中所有列名为xlt的所有表select * from sysobjects where id in
(select id from syscolumns where name='xlt')
三、生成随机函数：
方法1
步骤1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_rand]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_rand]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO
--需要这样一个视图
create view v_rand as select re=rand()
go
/*----取得指定上下限的随机数
--邹建 2003.12(引用请保留此信息)--*/
/*--调用示例
select dbo.f_rand(10,100),dbo.f_rand(10,100)
--*/
create function f_rand(
@下限 int,
@上限 int
)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@上限-@下限)+@下限 as decimal(38,0)) from v_rand
return(@r)
end
go
方法1
步骤2
--下面是调用函数实现生成1000个不重复的5位随机数
--生成随机数用到的临时表
create table #t(随机数 int unique) --用唯一约束保证不重复
go
--生成1000个5位的不重复随机数(错误提示不要理会,这是正常的)
declare @i int
set @i=1000 --1000要要生成的随机数的数量
while @i>0
begin
set rowcount @i
insert #t select dbo.f_rand(10000,99999) from sysobjects
set @i=@i-@@rowcount
end
set rowcount 0
--显示结果
select count(*) from #t
go
--删除测试
drop table #t
方法2
取随机数可以这样：
select cast(rand()*100000 as int) --你要取几位就要多少个0
四、每种类型第二和第三大记录
sortid（类型） name createtime
1 数 2007
1 英 2004
2 中 2006
2 三 2004
3 阿 2005
3 峨 2004
3 我 2003
查询出每种类型的createtime第二和第三大的两条记录
sortid（类型） name createtime
1 英 2004
2 三 2004
3 峨 2004
3 我 2003
实现的sql语句
select * from 表 a
where (select count(*) from 表 where sortid=a.sortid and createtime>=a.createtime) in(2,3)
语句分析
createtime>=a.createtime) in(2,3)只有两条或者三条大于或者等于当前记录当然是第二条和第三条最大记录
五、查看死锁
查看锁信息
create table #t(req_spid int,obj_name sysname)
declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname
declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb
select 进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=case rsc_type when 1 then 'NULL 资源（未使用）'
when 2 then '数据库'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '页'
when 7 then '键'
when 8 then '扩展盘区'
when 9 then 'RID（行 ID)'
when 10 then '应用程序'
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
go
drop table #t
--------------------------------------------------------------
use master
go
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select ' |_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
六、一个交叉表
select IDENTITY(int,1,1) as F_ID,* into #t_ProInfo1 from(
select top 100 percent F_Number,(F_Fraction1+F_Fraction2) as F_Fraction
from t_ProInfo order by F_Fraction desc)a
select IDENTITY(int,1,1) as F_ID,F_Number,(F_Fraction1+F_Fraction2) as F_Fraction into #t_ProInfo1 from t_ProInfo order by F_Fraction desc
select IDENTITY(int,1,1) as F_ID,F_Number into #t_ProInfo2 from t_ProInfo order by F_Fraction2 desc
select a.F_Number,a.F_Fraction1+F_Fraction2 as '总分',a.F_Fraction1,a.F_Fraction2,b.F_ID as 总排名,c.F_ID as 阶段排名 from T_ProInfo as a,#T_ProInfo1 as b,#T_ProInfo2 as c where a.F_NUmber=b.F_Number and a.F_Number=c.F_Number
drop table #t_ProInfo1
drop table #T_ProInfo2
版本2
select F_Number,F_Fraction1+F_Fraction2 as '总分',F_Fraction1,F_Fraction2 from t_Proinfo order by F_Fraction1+F_Fraction2 desc
create table #t_ProInfo1 (F_ID int identity(1,1),F_Number varchar(20),F_Fraction int)
select IDENTITY(int,1,1) as F_ID,F_Number,F_Fraction2 into #t_ProInfo2 from t_ProInfo order by F_Fraction2 desc
insert into #t_ProInfo1 select F_Number ,F_Fraction1+F_Fraction2 as F_Fraction from t_ProInfo order by F_Fraction desc
select * from #t_ProInfo1
select a.F_Number,a.F_Fraction1+a.F_Fraction2 as '总分',a.F_Fraction1,a.F_Fraction2,b.F_ID as 总排名,c.F_ID as 阶段排名 from T_ProInfo as a,#T_ProInfo1 as b,#T_ProInfo2 as c where a.F_NUmber=b.F_Number and a.F_Number=c.F_Number order by b.F_ID
drop table #t_ProInfo1
drop table #T_ProInfo2
交叉表方法1
select f_nUMBER as '学员',
SUM(case f_subject when 'A01' then f_nUM end) as 'A01',
SUM(case f_subject when 'A02' then f_nUM end) as 'A02' ,
SUM(case f_subject when 'A03' then f_nUM end) as 'A03' ,
SUM(case f_subject when 'A04' then f_nUM end) as 'A04' ,
SUM(case f_subject when 'A05' then f_nUM end) as 'A05' ,
SUM(case f_subject when 'A06' then f_nUM end) as 'A06' ,
SUM(case f_subject when 'A07' then f_nUM end) as 'A07' ,
SUM(case f_subject when 'A08' then f_nUM end) as 'A08' ,
SUM(case f_subject when 'A09' then f_nUM end) as 'A09'
from rowdata group by f_nUMBER order by f_nUMBER
方法2
declare @sql nvarchar(2000)
set @sql=''
select @sql=@sql+ 'sum(case F_subject when '''+ a.F_subject +''' then F_Num else 0 end) as '
+a.F_Name+','
from (select distinct top 100 percent F_subject,F_Name from rowdata b JOIN SUBJECT_name c on b.F_subject=c.F_number order by F_subject ) a
set @sql='select f_nUMBER as '+'"学员",'+@sql + 'count(F_Number) as '+'"考试数目"'+
'from rowdata group by F_Number order by F_Number'
print @sql
exec sp_executesql @sql
得到一个表中所有的列名
select name as '列名' from syscolumns where id=object_id('subject_name')
列出数据中有xlt字段的所有表名
select * from sysobjects where id in
(select id from syscolumns where name='xlt'
