【sql进阶】查询每天、每个设备的第一条数据

需求如下

每个设备（不同DeviceID）、每天会向数据库插入多条数据，求每天、每个设备插入的第一条数据。下面SQL中的 ShareRecommendID 类比不同设备的DeviceID。

ROW_NUMBER()函数介绍

说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法：ROW_NUMBER () OVER ([ <partition_by_clause>]<order_by_clause> ) 。
备注：ORDERBY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

方案1

使用ID自增长规则，取每天最小ID

sql

 SELECT CONVERT(varchar(100), CreateTime, 23) AS share_date, CreateTime, * FROM _ShareRelation WHERE ShareRelationID in (select MIN(ShareRelationID) from _ShareRelation group by CONVERT(varchar(100), CreateTime, 23) ) ORDER BY [_ShareRelation].CreateTime DESC

方案2

思路类似方案1，采用最大函数

sql

SELECT  CONVERT(varchar(100), CreateTime, 23) AS create_date,min(CreateTime) AS min_date,max(CreateTime) as max_date 
FROM [_ShareRelation] 
GROUP by CONVERT(varchar(100), CreateTime, 23) 
ORDER BY  CONVERT(varchar(100), CreateTime, 23) DESC

方案3

使用ROW_NUMBER函数，对每天、每个设备的数据进行编号

sql

SELECT * FROM 
(
SELECT CONVERT(varchar(100), CreateTime, 23) AS share_date, ROW_NUMBER() OVER(PARTITION BY CONVERT(varchar(100), CreateTime, 23), ShareRecommendID 
ORDER BY CreateTime ASC) row_num, * FROM dbo.[_ShareRelation] 
) AS tmp
WHERE tmp.row_num < 2

搞定！