精典SQL FAQ收藏

在網上經常轉,常?？吹接行┤藶榱饲蟮媚承?a href='http://www.kjueaiud.com/ceshi/ruanjianceshikaifajishu/rjcskf' target='_blank'>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))。

感謝那些網上提供相關SQL的作者