精典SQL FAQ收藏

精典SQL FAQ收藏

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 aselect @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=@idset @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))。