行列转换等经典SQL语句

1.--行列转换原表: 姓名 科目 成绩 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 物理 82 李四 英语 90 李四 政治 70 王五 英语 90转换后的表： 姓名 数学 物理 英语 语文 政治 李四 0 82 90 85 70 王五 0 0 90 0 0 张三 90 85 0 80 0实例:create table cj --创建表cj( ID Int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1 Name Varchar(50), Subject Varchar(50), Result Int, primary key (ID) --定义ID为表cj的主键 );--Truncate table cj--Select * from cjInsert into cjSelect '张三','语文',80 union all Select '张三','数学',90 union allSelect '张三','物理',85 union allSelect '李四','语文',85 union allSelect '李四','物理',82 union allSelect '李四','英语',90 union allSelect '李四','政治',70 union allSelect '王五','英语',90--行列转换Declare @sql varchar(8000)Set @sql = 'Select Name as 姓名'Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'from (select distinct Subject from cj) as cj --把所有唯一的科目的名称都列举出来Select @sql = @sql+' from cj group by name'Exec (@sql)2. 行列转换--合并原表: 班级 学号 1 1 1 2 1 3 2 1 2 2 3 1转换后的表: 班级 学号 1 1,2,3 2 1,2 3 1 实例:Create table ClassNo --创建表ClassNo( ID Int IDENTITY(1,1) not null, --创建列ID,并且每次新增一条记录就会加1 Class Varchar(50), --班级列 Number Varchar(50), --学号列 Primary Key(ID) --定义ID为表ClassNo的主键);--Truncate Table ClassNo--Select * from ClassNoInsert Into ClassNoSelect 1,1 Union allSelect 1,2 Union allSelect 1,3 Union allSelect 2,1 Union allSelect 2,2 Union allSelect 3,1创建一个合并的函数--Drop Function KFReturnCreate Function KFReturn(@Class Varchar(50))Returns Varchar(8000)as BeginDeclare @str Varchar(8000)Set @str = ''Select @str = @str + cast(Number as Varchar(50)) + ',' from ClassNo Where Class = @Class Set @str = SubString(@str,1,len(@str)-1)Return(@str)End--调用自定义函数得到结果Select Distinct Class,dbo.KFReturn(Class) From ClassNo3:列转行--Drop Table ColumnToRowCreate table ColumnToRow( ID Int IDENTITY(1,1) not null, --创建列ID,并且每次新增一条记录就会加1 a int, b int, c int, d int, e int, f int, g int, h int, Primary Key(ID) --定义ID为表ColumnToRow的主键 );--Truncate Table ColumnToRow --Select * from ColumnToRowInsert Into ColumnToRow Select 15,9,1,0,1,2,4,2 Union allSelect 22,34,44,5,6,7,8,7 Union allSelect 33,44,55,66,77,88,99,12Declare @sql Varchar(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 @objid int,@objname char(40)set @objname = 'ColumnToRow'--第1种方法select @objid = id from sysobjects where id = object_id(@objname)select 'Column_name' = name from syscolumns where id = @objid order by colid--或也可以写成select name as 'Column_name' from syscolumns where id = @objid order by colid--第2种方法:Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid5. 通过SQL语句来更改用户的密码修改别人的,需要sysadmin role Exec Sp_password '原始密码','更改后密码','账号'Exec sp_password null,ok,sa6. 怎么判断出一个表的哪些字段不允许为空？Declare @objname Varchar(50)set @objname = 'ColumnToRow'Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname7. 如何在数据库里找到含有相同字段的表？a. 查已知列名的情况Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.idand 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' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)8.查询第N行数据假设id是主键： select * from (select top N * from 表) aa where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)9. 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))