在網上經常轉,常常看到有些人為了求得某些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 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)asbegindeclare @str varchar(8000)set @str=''select @[email protected]+','+cast(pid as varchar) from 表a where [email protected] @str=right(@str,len(@str)-1)return(@str)endgo
--調用自定義函數得到結果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的作者
