这条语句什么意思,哪位详细说一下,谢谢。 select row_number() over (partition by userid order by ctime,otime ) as sn, t1.* into #temp2from #temp1 t1 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 ROW_NUMBER()说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 <order_by_clause>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型:bigint 。示例:/*以下示例将根据年初至今的销售额,返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/USE AdventureWorksGOSELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCodeFROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactIDJOIN Person.Address a ON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL AND SalesYTD <> 0/*FirstName LastName Row Number SalesYTD PostalCode--------- ---------- ---------- ------------ ----------------------------Shelley Dyck 1 5200475.2313 98027Gail Erickson 2 5015682.3752 98055Maciej Dusza 3 4557045.0459 98027Linda Ecoffey 4 3857163.6332 98027Mark Erickson 5 3827950.238 98055Terry Eminhizer 6 3587378.4257 98055Michael Emanuel 7 3189356.2465 98055Jauna Elson 8 3018725.4858 98055Carol Elliott 9 2811012.7151 98027Janeth Esteves 10 2241204.0424 98055Martha Espinoza 11 1931620.1835 98055Carla Eldridge 12 1764938.9859 98027Twanna Evans 13 1758385.926 98055(13 行受影响)*/ /*以下示例将返回行号为 50 到 60(含)的行,并以 OrderDate 排序。*/ USE AdventureWorks;GOWITH OrderedOrders AS(SELECT SalesOrderID, OrderDate,ROW_NUMBER() OVER (order by OrderDate)as RowNumberFROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber between 50 and 60;/*SalesOrderID OrderDate RowNumber------------ ----------------------- --------------------43708 2001-07-03 00:00:00.000 5043709 2001-07-03 00:00:00.000 5143710 2001-07-03 00:00:00.000 5243711 2001-07-04 00:00:00.000 5343712 2001-07-04 00:00:00.000 5443713 2001-07-05 00:00:00.000 5543714 2001-07-05 00:00:00.000 5643715 2001-07-05 00:00:00.000 5743716 2001-07-05 00:00:00.000 5843717 2001-07-05 00:00:00.000 5943718 2001-07-06 00:00:00.000 60(11 行受影响)*/ 详细说明就是:在#temp1表中按照userid分组,然后ctime,otime 排序号,生成从1开始的序号,然后插入临时表#temp2 就是把t1里面的数据出入到t2中,其中会多插入一列,这一列是根据userid分类,并按ctime,otime排序的结果如下: userid ctime otime sn 1 2008-1-2 2008-1-3 1 1 2008-1-2 2008-1-4 2 1 2008-1-3 2008-1-2 3 2 2008-1-3 2008-1-2 1........... 如何将字符串转换成数值 高分求,如果从另一个关联内多个数值内取第一条? SQL2000问题请教高手 刚才发的贴子打不开,请教一条sql语句 求一正则表达式,有点难度:怎么样取出新闻内容里面的图片字符串。 三表查询疑难问题。 再请教一个sqlserver语句!!!!!!!!! 求助 向各位高手请教:什么情况才需要分表?分表的原则是什么? 在ADO中如何添加SQL Server数据库用户? 附加数据库出错,大侠们快来帮帮忙啊 归类问题
语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型:bigint 。示例:
/*以下示例将根据年初至今的销售额,返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
/*
FirstName LastName Row Number SalesYTD PostalCode
--------- ---------- ---------- ------------ ----------------------------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 2 5015682.3752 98055
Maciej Dusza 3 4557045.0459 98027
Linda Ecoffey 4 3857163.6332 98027
Mark Erickson 5 3827950.238 98055
Terry Eminhizer 6 3587378.4257 98055
Michael Emanuel 7 3189356.2465 98055
Jauna Elson 8 3018725.4858 98055
Carol Elliott 9 2811012.7151 98027
Janeth Esteves 10 2241204.0424 98055
Martha Espinoza 11 1931620.1835 98055
Carla Eldridge 12 1764938.9859 98027
Twanna Evans 13 1758385.926 98055
(13 行受影响)
*/
/*以下示例将返回行号为 50 到 60(含)的行,并以 OrderDate 排序。*/
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
/*
SalesOrderID OrderDate RowNumber
------------ ----------------------- --------------------
43708 2001-07-03 00:00:00.000 50
43709 2001-07-03 00:00:00.000 51
43710 2001-07-03 00:00:00.000 52
43711 2001-07-04 00:00:00.000 53
43712 2001-07-04 00:00:00.000 54
43713 2001-07-05 00:00:00.000 55
43714 2001-07-05 00:00:00.000 56
43715 2001-07-05 00:00:00.000 57
43716 2001-07-05 00:00:00.000 58
43717 2001-07-05 00:00:00.000 59
43718 2001-07-06 00:00:00.000 60
(11 行受影响)
*/
详细说明就是:在#temp1表中按照userid分组,然后ctime,otime 排序号,生成从1开始的序号,然后插入临时表#temp2
结果如下:
userid ctime otime sn
1 2008-1-2 2008-1-3 1
1 2008-1-2 2008-1-4 2
1 2008-1-3 2008-1-2 3
2 2008-1-3 2008-1-2 1
...........