select newid= ROW_NUMBER() OVER(PARTITION BY type ORDER BY SNumber,gtime desc),sumber,sname,gtime,level from biao
参照下面的排序函数 SELECT *, [ROW_NUMBER_type] = ROW_NUMBER() OVER(PARTITION BY type ORDER BY create_date), [RANK_type] = RANK() OVER(PARTITION BY type ORDER BY create_date), [DENSE_RANK_type] = DENSE_RANK() OVER(PARTITION BY type ORDER BY create_date), [NTILE_type] = NTILE(5) OVER(PARTITION BY type ORDER BY create_date), [ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY type, create_date), [RANK] = RANK() OVER(ORDER BY type, create_date), [DENSE_RANK] = DENSE_RANK() OVER(ORDER BY type, create_date), [NTILE] = NTILE(5) OVER(ORDER BY type, create_date) FROM OBJ ORDER BY type, create_date
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS [NEWID], snumber, sname, gtime, level FROM LI ORDER BY SNUMBER ASC,GTIME DESC
不好意思, 忘了说了, 我用的是sql server2k, 不支持row_number 我知道如何生成newid, 但是不知道按照snumber怎么排序?SELECT (SELECT COUNT(*) FROM Water WHERE GTime=A.GTime AND Id>=A.Id AND '+@strWhere+') AS NewOrder, * FROM Water A WHERE '+@strWhere+' ORDER BY GTime DESC
create table taoistong (id int identity(1,1) ,snumber char(3) ,sname nvarchar(4) ,gtime smalldatetime ,level float ) insert into taoistong select '023', N'测站1', '2009-12-17 10:00:00', 50.12 insert into taoistong select '058', N'测站2', '2009-12-17 10:00:00', 30.24 insert into taoistong select '018', N'测站3', '2009-12-17 10:00:00', 83.01 insert into taoistong select '018', N'测站3', '2009-12-17 09:54:00', 50.12 insert into taoistong select '058', N'测站2', '2009-12-17 09:54:00', 30.24 insert into taoistong select '023', N'测站1', '2009-12-17 09:54:00', 83.01 select (select count(1) from taoistong b where a.gtime=b.gtime and a.snumber>=b.snumber) ,snumber,sname,gtime,level from taoistong a order by gtime desc,snumber
--SQL 2005 declare @tb table (id int,snumber char(3),sname nvarchar(4),gtime smalldatetime,level float)insert @tb select 66,'023', N'测站1', '2009-12-17 10:00:00', 50.12 union all select 65,'058', N'测站2', '2009-12-17 10:00:00', 30.24 union all select 64,'018', N'测站3', '2009-12-17 10:00:00', 83.01 union all select 63,'018', N'测站3', '2009-12-17 09:54:00', 50.12 union all select 62,'058', N'测站2', '2009-12-17 09:54:00', 30.24 union all select 61,'023', N'测站1', '2009-12-17 09:54:00', 83.01 select id=row_number() over(partition by gtime order by snumber,gtime desc), snumber, sname, gtime,level from @tbid snumber sname gtime level -------------------- ------- ----- ----------------------- ---------------------- 1 018 测站3 2009-12-17 09:54:00 50.12 2 023 测站1 2009-12-17 09:54:00 83.01 3 058 测站2 2009-12-17 09:54:00 30.24 1 018 测站3 2009-12-17 10:00:00 83.01 2 023 测站1 2009-12-17 10:00:00 50.12 3 058 测站2 2009-12-17 10:00:00 30.24(6 行受影响)
from biao
SELECT
*,
[ROW_NUMBER_type] = ROW_NUMBER() OVER(PARTITION BY type ORDER BY create_date),
[RANK_type] = RANK() OVER(PARTITION BY type ORDER BY create_date),
[DENSE_RANK_type] = DENSE_RANK() OVER(PARTITION BY type ORDER BY create_date),
[NTILE_type] = NTILE(5) OVER(PARTITION BY type ORDER BY create_date), [ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY type, create_date),
[RANK] = RANK() OVER(ORDER BY type, create_date),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY type, create_date),
[NTILE] = NTILE(5) OVER(ORDER BY type, create_date)
FROM OBJ
ORDER BY type, create_date
snumber, sname, gtime, level
FROM LI
ORDER BY SNUMBER ASC,GTIME DESC
不好意思, 忘了说了, 我用的是sql server2k, 不支持row_number
我知道如何生成newid, 但是不知道按照snumber怎么排序?SELECT
(SELECT COUNT(*)
FROM Water
WHERE GTime=A.GTime AND Id>=A.Id AND '+@strWhere+') AS NewOrder, *
FROM Water A WHERE '+@strWhere+' ORDER BY GTime DESC
(id int identity(1,1)
,snumber char(3)
,sname nvarchar(4)
,gtime smalldatetime
,level float )
insert into taoistong select
'023', N'测站1', '2009-12-17 10:00:00', 50.12
insert into taoistong select
'058', N'测站2', '2009-12-17 10:00:00', 30.24
insert into taoistong select
'018', N'测站3', '2009-12-17 10:00:00', 83.01
insert into taoistong select
'018', N'测站3', '2009-12-17 09:54:00', 50.12
insert into taoistong select
'058', N'测站2', '2009-12-17 09:54:00', 30.24
insert into taoistong select
'023', N'测站1', '2009-12-17 09:54:00', 83.01 select (select count(1) from taoistong b where a.gtime=b.gtime and a.snumber>=b.snumber)
,snumber,sname,gtime,level
from taoistong a order by gtime desc,snumber
declare @tb table (id int,snumber char(3),sname nvarchar(4),gtime smalldatetime,level float)insert @tb
select 66,'023', N'测站1', '2009-12-17 10:00:00', 50.12 union all
select 65,'058', N'测站2', '2009-12-17 10:00:00', 30.24 union all
select 64,'018', N'测站3', '2009-12-17 10:00:00', 83.01 union all
select 63,'018', N'测站3', '2009-12-17 09:54:00', 50.12 union all
select 62,'058', N'测站2', '2009-12-17 09:54:00', 30.24 union all
select 61,'023', N'测站1', '2009-12-17 09:54:00', 83.01 select id=row_number() over(partition by gtime order by snumber,gtime desc), snumber, sname, gtime,level
from @tbid snumber sname gtime level
-------------------- ------- ----- ----------------------- ----------------------
1 018 测站3 2009-12-17 09:54:00 50.12
2 023 测站1 2009-12-17 09:54:00 83.01
3 058 测站2 2009-12-17 09:54:00 30.24
1 018 测站3 2009-12-17 10:00:00 83.01
2 023 测站1 2009-12-17 10:00:00 50.12
3 058 测站2 2009-12-17 10:00:00 30.24(6 行受影响)