SQL 查询排序

t101lian 2017-06-01 05:24:45

现在有两列数据, oder by date 查询出来的结果如下
date, setnum
2017-05-27 14:27:07.813 1
2017-05-27 14:27:07.813 1
2017-05-27 14:27:07.813 1
2017-05-27 14:27:07.823 12
2017-05-27 14:27:07.823 12
2017-05-27 14:27:07.823 12
2017-05-27 14:27:07.830 23
2017-05-27 14:27:07.830 23
2017-05-27 14:27:07.830 23
2017-05-27 14:27:07.837 34
2017-05-27 14:27:07.837 34
2017-05-27 14:27:07.837 34
2017-05-27 14:27:07.847 36
2017-05-27 14:27:07.847 36
2017-05-27 14:27:07.847 36
2017-05-27 14:27:07.853 37
2017-05-27 14:27:07.853 37
2017-05-27 14:27:07.853 37
2017-05-27 14:27:07.860 38
2017-05-27 14:27:07.860 38
2017-05-27 14:27:07.860 38

------
因为setnum的顺序是乱的，现在想得到新的newnum ,结果如下

date, setnum , newnum
2017-05-27 14:27:07.813 1 1
2017-05-27 14:27:07.813 1 1
2017-05-27 14:27:07.813 1 1
2017-05-27 14:27:07.823 12 2
2017-05-27 14:27:07.823 12 2
2017-05-27 14:27:07.823 12 2
2017-05-27 14:27:07.830 23 3
2017-05-27 14:27:07.830 23 3
2017-05-27 14:27:07.830 23 3
2017-05-27 14:27:07.837 34 4
2017-05-27 14:27:07.837 34 4
2017-05-27 14:27:07.837 34 4
2017-05-27 14:27:07.847 36 5
2017-05-27 14:27:07.847 36 5
2017-05-27 14:27:07.847 36 5
..。。。。。。

...全文

94 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2017-06-01

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

 CREATE TABLE #T([date] DATETIME,setnum INT)

Insert #T

select '2017-05-27 14:27:07.813',1 union all

select '2017-05-27 14:27:07.813',1 union all

select '2017-05-27 14:27:07.813',1 union all

select '2017-05-27 14:27:07.823',12 union all

select '2017-05-27 14:27:07.823',12 union all

select '2017-05-27 14:27:07.823',12 union all

select '2017-05-27 14:27:07.830',23 union all

select '2017-05-27 14:27:07.830',23 union all

select '2017-05-27 14:27:07.830',23 union all

select '2017-05-27 14:27:07.837',34 union all

select '2017-05-27 14:27:07.837',34 union all

select '2017-05-27 14:27:07.837',34 union all

select '2017-05-27 14:27:07.847',36 union all

select '2017-05-27 14:27:07.847',36 union all

select '2017-05-27 14:27:07.847',36 union all

select '2017-05-27 14:27:07.853',37 union all

select '2017-05-27 14:27:07.853',37 union all

select '2017-05-27 14:27:07.853',37 union all

select '2017-05-27 14:27:07.860',38 union all

select '2017-05-27 14:27:07.860',38 union all

select '2017-05-27 14:27:07.860',38

Go

--测试数据结束

Select *,DENSE_RANK()OVER(ORDER BY setnum) AS newnum  from #T