/** 例子一:
问题:假设有张学生成绩表(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
问题:假设有张学生成绩表(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