sql server 2005 中的分区函数用法(partition by 字段)
来源:互联网 发布:qq旋风mac版下载 编辑:程序博客网 时间:2024/05/17 17:16
以下例子根据Item 分组后.排序.
应用例子如下:
if exists(select 1 from sys.objects where object_id=object_id('tab'))
begin
drop table tab
end
go
create table tab(item int, date datetime, temp int)
insert tab select 10,'2006-01-01',0
union all select 10,'2006-02-01',0
union all select 10,'2006-03-01',0
union all select 20,'2006-01-01',0
union all select 20,'2006-02-01',0
union all select 30,'2006-01-01',0
union all select 30,'2006-02-01',0
union all select 30,'2006-03-01',0
union all select 30,'2006-04-01',0
union all select 30,'2006-05-01',0
go
select *,row_number() over(partition by item order by date ) as t from tab
go
--结果
/*
item date temp t
----------- ----------------------- ----------- --------------------
10 2006-01-01 00:00:00.000 0 1
10 2006-02-01 00:00:00.000 0 2
10 2006-03-01 00:00:00.000 0 3
20 2006-01-01 00:00:00.000 0 1
20 2006-02-01 00:00:00.000 0 2
30 2006-01-01 00:00:00.000 0 1
30 2006-02-01 00:00:00.000 0 2
30 2006-03-01 00:00:00.000 0 3
30 2006-04-01 00:00:00.000 0 4
30 2006-05-01 00:00:00.000 0 5
(10 行受影响)
*/
begin
drop table tab
end
go
create table tab(item int, date datetime, temp int)
insert tab select 10,'2006-01-01',0
union all select 10,'2006-02-01',0
union all select 10,'2006-03-01',0
union all select 20,'2006-01-01',0
union all select 20,'2006-02-01',0
union all select 30,'2006-01-01',0
union all select 30,'2006-02-01',0
union all select 30,'2006-03-01',0
union all select 30,'2006-04-01',0
union all select 30,'2006-05-01',0
go
select *,row_number() over(partition by item order by date ) as t from tab
go
--结果
/*
item date temp t
----------- ----------------------- ----------- --------------------
10 2006-01-01 00:00:00.000 0 1
10 2006-02-01 00:00:00.000 0 2
10 2006-03-01 00:00:00.000 0 3
20 2006-01-01 00:00:00.000 0 1
20 2006-02-01 00:00:00.000 0 2
30 2006-01-01 00:00:00.000 0 1
30 2006-02-01 00:00:00.000 0 2
30 2006-03-01 00:00:00.000 0 3
30 2006-04-01 00:00:00.000 0 4
30 2006-05-01 00:00:00.000 0 5
(10 行受影响)
*/
- sql server 2005 中的分区函数用法(partition by 字段)
- sql server 2005 中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005 中的分区函数用法(partition by 字段)
- sql server 2005 中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- sql server 2005中的分区函数用法(partition by 字段)
- [MSSQL]SQL SERVER 2005-OVER (PARTITION BY…)的用法
- SQL SERVER 2005-OVER (PARTITION BY…)的用法
- sql server 2005 中的列置换函数用法(pivot 字段)
- sqlserver中分区函数 partition by的用法
- sqlserver中分区函数 partition by的用法
- sqlserver中分区函数 partition by的用法
- a different object with the same identifier value was already associated with the session 一个经典的hibernate错误
- Qt学习之路(59): 编写跨平台的程序
- 介绍一篇关于session的好文章,写的很详细--转
- Qt学习之路(60): 创建shared library
- 面试题--写一函数把短语转换为字母顺序颠倒的特殊语句
- sql server 2005 中的分区函数用法(partition by 字段)
- 这是我在出空指针异常后找的 java.lang.NullPointerException
- CE6.0用户态驱动和应用程序访问物理内存的方法
- 《狗日的腾讯》原文转自《计算机世界》
- 高质量的网页布局模板
- linux系统解释器
- 微笑
- samba 安装过程和经验
- C语言常用转义字符表