行列转换等经典SQL语句 1.--行列转换 原表: 姓名 科目 成绩 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 物理 82 李四 英语 90 李四 政治 70 王五 英语 90 转换后的表: 姓名 数学 物理 英语 语文 政治 李四 082908570 王五 009000 张三 90850800 实例: createtable cj --创建表cj ( ID IntIDENTITY (1,1) notnull, --创建列ID,并且每次新增一条记录就会加1 Name Varchar(50), Subject Varchar(50), Result Int, primarykey (ID) --定义ID为表cj的主键 ); --Truncate table cj --Select * from cj Insertinto cj Select'张三','语文',80unionall Select'张三','数学',90unionall Select'张三','物理',85unionall Select'李四','语文',85unionall Select'李四','物理',82unionall Select'李四','英语',90unionall Select'李四','政治',70unionall Select'王五','英语',90 --行列转换 Declare@sqlvarchar(8000) Set@sql='Select Name as 姓名' Select@sql=@sql+',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']' from (selectdistinct Subject from cj) as cj --把所有唯一的科目的名称都列举出来 Select@sql=@sql+' from cj group by name' Exec (@sql) 2. 行列转换--合并 原表: 班级 学号 11 12 13 21 22 31 转换后的表: 班级 学号 11,2,3 21,2 31 实例: Createtable ClassNo --创建表ClassNo ( ID IntIDENTITY(1,1) notnull, --创建列ID,并且每次新增一条记录就会加1 Class Varchar(50), --班级列 NumberVarchar(50), --学号列 PrimaryKey(ID) --定义ID为表ClassNo的主键 ); --Truncate Table ClassNo --Select * from ClassNo InsertInto ClassNo Select1,1Unionall Select1,2Unionall Select1,3Unionall Select2,1Unionall Select2,2Unionall Select3,1 创建一个合并的函数 --Drop Function KFReturn CreateFunction KFReturn(@ClassVarchar(50)) ReturnsVarchar(8000) as Begin Declare@strVarchar(8000) Set@str='' Select@str=@str+cast(NumberasVarchar(50)) +','from ClassNo Where Class =@Class Set@str=SubString(@str,1,len(@str)-1) Return(@str) End --调用自定义函数得到结果 SelectDistinct Class,dbo.KFReturn(Class) From ClassNo 3:列转行 --Drop Table ColumnToRow Createtable ColumnToRow ( ID IntIDENTITY(1,1) notnull, --创建列ID,并且每次新增一条记录就会加1 a int, b int, c int, d int, e int, f int, g int, h int, PrimaryKey(ID) --定义ID为表ColumnToRow的主键 ); --Truncate Table ColumnToRow --Select * from ColumnToRow InsertInto ColumnToRow Select15,9,1,0,1,2,4,2Unionall Select22,34,44,5,6,7,8,7Unionall Select33,44,55,66,77,88,99,12 Declare@sqlVarchar(8000) Set@sql='' Select@sql=@sql+rtrim(name) +' from ColumnToRow union all Select 'from SysColumns Where id =object_id('ColumnToRow') Set@sql=SubString(@sql,1,len(@sql)-70) --70的长度就是这个字符串'from ColumnToRow union all Select ID from ColumnToRow union all Select ',因为它会把ID这一列的值也算进去,所以要把它截掉 Exec ('Select '+@sql+' from ColumnToRow') 4. 如何取得一个数据表的所有列名 方法如下:先从sysobjects系统表中取得数据表的systemid,然后再syscolumns表中取得该数据表的所有列名。 SQL语句如下: Declare@objidint,@objnamechar(40) set@objname='ColumnToRow' --第1种方法 select@objid= id from sysobjects where id =object_id(@objname) select'Column_name'= name from syscolumns where id =@objidorderby colid --或也可以写成 select name as'Column_name'from syscolumns where id =@objidorderby colid --第2种方法: Select name as'Column_Name'from SysColumns where id =object_id(@objname) Orderby colid 5. 通过SQL语句来更改用户的密码 修改别人的,需要sysadmin role Exec Sp_password '原始密码','更改后密码','账号' Exec sp_password null,ok,sa 6. 怎么判断出一个表的哪些字段不允许为空? Declare@objnameVarchar(50) set@objname='ColumnToRow' Select Column_Name from information_schema.Columns where is_nullable ='No'and Table_Name =@objname 7. 如何在数据库里找到含有相同字段的表? a. 查已知列名的情况 Select a.name as Columnname,b.name as tablename from SysColumns a innerjoin sysobjects b on a.id = b.id and b.type ='U'and a.name ='您要查找的字段名' b. 未知列名查所有在不同表出现过的列名 Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s Where s1.id = s.id and s.Type ='U'andExists (Select1from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id) 8.查询第N行数据 假设id是主键: select* from (selecttop N *from 表) aa wherenotexists(select1from (selecttop N-1*from 表) bb where aa.id=bb.id) 9. 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))
posted on
2008-07-28 20:04redfox
阅读(227)
评论(0)
编辑收藏举报