如题:create table #t(s_time datetime,na varchar(20))insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'select * from #t/*
希望得到的结果 group_no s_time na
1 2013-10-01 aa
1 2013-10-01 bb
1 2013-10-01 cc
2 2013-10-15 dd
2 2013-10-15 ee
3 2013-10-20 ff*/
sql排序分组
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'select * from #t/*
希望得到的结果 group_no s_time na
1 2013-10-01 aa
1 2013-10-01 bb
1 2013-10-01 cc
2 2013-10-15 dd
2 2013-10-15 ee
3 2013-10-20 ff*/
sql排序分组
create table #t(s_time datetime,na varchar(20))
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
select DENSE_RANK()over(order by s_time) as group_no,* from #tdrop table #t--
group_no s_time na
-------------------- ----------------------- --------------------
1 2013-10-01 00:00:00.000 aa
1 2013-10-01 00:00:00.000 bb
1 2013-10-01 00:00:00.000 cc
2 2013-10-15 00:00:00.000 dd
2 2013-10-15 00:00:00.000 ee
3 2013-10-20 00:00:00.000 ff(6 行受影响)
create table #t(s_time datetime,na varchar(20))
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
select DENSE_RANK()over(order by s_time) as group_no
,convert(char(10),s_time,120) as s_time,na
from #tdrop table #t--结果
group_no s_time na
-------------------- ---------- --------------------
1 2013-10-01 aa
1 2013-10-01 bb
1 2013-10-01 cc
2 2013-10-15 dd
2 2013-10-15 ee
3 2013-10-20 ff(6 行受影响)
create table #t(s_time datetime,na varchar(20))insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'SELECT DENSE_RANK() OVER(ORDER BY s_time) AS group_no
,CAST(s_time AS DATE) AS Date
,na
FROM #t/*
1 2013-10-01 aa
1 2013-10-01 bb
1 2013-10-01 cc
2 2013-10-15 dd
2 2013-10-15 ee
3 2013-10-20 ff*/
a.*
FROM #t a
INNER JOIN ( SELECT s_time ,
ROW_NUMBER() OVER ( ORDER BY s_time ) group_no
FROM ( SELECT DISTINCT
s_time
FROM #t
) b
) b ON a.s_time = b.s_time
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'select group_no=(select count(distinct s_time) from #t b where b.s_time<=a.s_time),s_time,na
from #t a
/*
group_no s_time na
1 2013-10-01 00:00:00.000 aa
1 2013-10-01 00:00:00.000 bb
1 2013-10-01 00:00:00.000 cc
2 2013-10-15 00:00:00.000 dd
2 2013-10-15 00:00:00.000 ee
3 2013-10-20 00:00:00.000 ff*/
if object_id('tb') is not null
drop table tb
go
create table tb(s_time datetime,na varchar(20))
insert tb
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
select DENSE_RANK() over(order by s_time) as group_no,
convert(varchar(10),s_time,120) as s_time,
na
from tb
/*
group_no s_time na
-------------------- ---------- --------------------
1 2013-10-01 aa
1 2013-10-01 bb
1 2013-10-01 cc
2 2013-10-15 dd
2 2013-10-15 ee
3 2013-10-20 ff
*/