基础SQL语句整合

/** 例子一:


问题：假设有张学生成绩表(tb)如下:
姓 名 课 程 分 数
张三   语文  74
张三   数学  83
张三   物理  93
李四   语文  74
李四   数学  84
李四   物理  94


想变成(得到如下结果)： 
姓名 语 文 数 学 物 理 
李四  74    84    94
张三  74    83    93
*/
--T-SQL行转列(方法一),SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql='select 姓名' 
select @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end) ['+课程+']' 
from (select distinct 课程 from tb) a  
set @sql=@sql+' from tb group by 姓名'
exec (@sql)




--T-SQL行转列(方法二),SQL SERVER 2005 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
select @sql=isnull(@sql+'],[','')+课程 from tb group by 课程 
set @sql='['+@sql+']'
exec ('select * from tb pivot (max(分数) for 课程 in ('+@sql+')) as b')




/***  例子二:


1.一道SQL语句面试题，关于group by
表(A)内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负


如果要生成下列结果, 该如何写sql语句?


           胜 负
2005-05-09 2  2
2005-05-10 1  2
*/
declare @sql varchar(8000)
set @sql='select SDate ' 
select @sql=@sql+',sum(case when SResult='''+SResult+''' then 1 else 0 end) ['+SResult+']' 
from (select distinct SResult from A) a  
set @sql=@sql+' from dbo.A group by SDate'
exec (@sql)




/**  例子三:


问题：假设有张学生成绩表(tb)如下:


姓名 语 文 数 学 物 理 
李四  74   84     94
张三  74   83     93


想变成(得到如下结果)：


姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/


--T-SQL列转行(方法一)
declare @sql varchar(8000) 
select @sql=isnull(@sql+'],[','')+name from syscolumns 
where id = object_id('StudentScores') and name not in ('ID','StuNo')
set @sql='['+@sql+']' 
exec('select StuNo,Subject,Score from dbo.StudentScores unpivot (Score for subject in ('+@sql+')) as b')




/**  例子四


 表一(Achievement)：
  
月份mon   部门dep  业绩yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8


表二(Deprtment)：


    部门dep      部门名称dname
    --------------------------------
      01         国内业务一部
      02         国内业务二部
      03         国内业务三部
      04         国际业务部


结果：


    部门dep  一月份    二月份    三月份
    --------------------------------------
      01      10        null      null
      02      10         8        null
      03      null       5        8
      04      null      null      9
    --------------------------------------


*/


declare @sql varchar(8000)
set @sql='select B.depName ' 
select @sql=@sql+',max(case when A.mon='''+A.mon+''' then achi else 0 end) ['+A.mon+']' 
from (select distinct mon from Achievement) a 
set @sql=@sql+' from dbo.Achievement A left join dbo.Deprtment B on A.dept=B.dept group by B.depName' 
exec (@sql)




--T-SQL分页查询


--SQL Server2000中的分页方式


select top 30 * from Pagin where id not in (select top 30 id from Pagin order by id desc) order by id desc




/**
   over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
   over（partition by columnname1 order by columnname2）
   含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
   例如：employees表中，有两个部门的记录：department_id ＝10和20
   select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，
   在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。
   
   row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
   dense_rank() 是连续排序，两个第二名仍然跟着第三名
   rank()       是跳跃拍学，两个第二名下来就是第四名
*/
--SQL Server2005分页方式


select number from (select number,row_number() over(order by number desc) as num from Pagin) t where num between 30 and 60 order by number desc




--SQL Server2012分页方式


select number from Pagin order by number offset 10 rows fetch next 5 rows only




--T-SQL每科成绩都在80分以上的学员
select * from c where name not in (select name from c where number<80)


--删除所有重复，保留一条最大ID的记录
delete from dbo.Pagin where ID not in (select max(ID) as id from dbo.Pagin group by Number,[Type],[Count])


--查询所有重复数据中的最大ID的记录
select ID,Number,[Type],[Count] from dbo.Pagin except (select max(ID) as id,Number,[Type],[Count] from dbo.Pagin group by Number,[Type],[Count])
--相反结果
select ID,Number,[Type],[Count] from dbo.Pagin intersect (select max(ID) as id,Number,[Type],[Count] from dbo.Pagin group by Number,[Type],[Count])


--查询第201行数据
select top 1 * from dbo.Pagin where ID not in (select top 200 id from Pagin order by ID) order by ID 


--获取一张用户表的所有列明
select name from syscolumns where id=object_id('dbo.Pagin')


--判断一张表那些字段不能为空
select column_name from information_schema.columns where is_nullable='NO' and table_name='Pagin' 


--快速查询表记录的行数
select rows from sysindexes where id = object_id('Pagin') and indid in (0,1)




/**
    ID  Station    endTime        rdLong  RdDate


    1 3#筒仓 2014-1-1 5:45:52 9   NULL
2 3#筒仓 2014-1-2 7:36:15 9   NULL
3 1#筒仓 2014-1-3 15:08:18 42   NULL
4 1#筒仓 2014-1-4 18:08:08 42   NULL
5 1#筒仓 2014-1-5 21:08:04 43   NULL
*/


--查询时间大于07:30并且小于17:30更新RdDate字段为2014-02-13 日班,否则更新为2014-02-13 夜班
update dbo.Table1 set RdDate=
                             case when convert(varchar(5),endTime,114)>'07:30' and 
                                       convert(varchar(5),endTime,114)<'17:30' then 
                                       convert(varchar(10),endTime,120)+'日班' else convert(varchar(10),endTime,120)+'夜班' end