常用SQL语句(行列转换等)

1. 行列转换--普通

假设有张学生成绩表(CJ)如下
Name Subject Result
张三   语文     80
张三   数学     90
张三   物理     85
李四   语文     85
李四   数学     92
李四   物理     82

想变成
姓名 语文 数学 物理
张三  80      90      85
李四  85      92      82

declare@sqlvarchar(4000)
set@sql='select Name'
select@sql=@sql+',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (selectdistinct Subject from CJ) as a
select@sql=@sql+' from test group by name'
exec(@sql)

2. 行列转换--合并

有表A,
id pid
11
12
13
21
22
31
如何化成表B:
id pid
11,2,3
21,2
31

创建一个合并的函数
createfunction fmerg(@idint)
returnsvarchar(8000)
as
begin
declare@strvarchar(8000)
set@str=''
select@str=@str+','+cast(pid asvarchar) from 表A where id=@id
set@str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
selectdistinct id,dbo.fmerg(id) from 表A

3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare@objidint,@objnamechar(40)
set@objname='tablename'
select@objid= id from sysobjects where id =object_id(@objname)
select'Column_name'= name from syscolumns where id =@objidorderby 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 INNERJOIN 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'AndExists (
Select1From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id
)

7. 查询第xxx行数据

假设id是主键：
select*from (selecttop xxx *from yourtable) aa
wherenotexists(select1from (selecttop xxx-1*from yourtable) bb where aa.id=bb.id)

如果使用游标也是可以的 fetch absolute [number]from[cursor_name]
行数为绝对行数

8. SQL Server日期计算
a. 一个月的第一天
SELECTDATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECTDATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECTDATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECTDATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECTdateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECTdateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
selectDATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
i. 本年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。