SQL语句按日期分组求最新记录

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

1.第一次尝试

先根据creator_name,template_name 获取最新分组，然后求其最大的max(create_time)创建时间，然后使用inner join方式，根据u2.create_time =t.mt创建时间相同的记录，将其他的字段值取出。

1	SELECT * FROM userlog u2 inner join (SELECT max(create_time) as mt,template_name FROM userlog u group by creator_name,template_name) as t ON u2.create_time =t.mt

出现的问题，就是如果create_time不是精确到秒，而是精确到分钟，在执行内连接时，就可能出现很多的重复时间记录，所以，最好是创建时间是唯一记录。

2.第二次尝试

因为时间不唯一，所以就想着使用唯一值report_id进行区分，这样产生的后果就是，通过report_id分组之后，就达不到取最新记录的目的了。

1	SELECT * FROM userlog u2 inner join (SELECT max(create_time) as mt,template_name,report_id FROM userlog u group by creator_name,template_name,report_id) as t ON u2.report_id =t.report_id

3.第三次尝试

虽然时间上尽量的做到精确到秒了，但是和需求出现了偏差，时间上，要按照日期分组，但是第一次的sql语句没有按照日期进行分组，所以不符合要求。

/*将时间2020-02-03 03:22:22,转换为日期2020-02-03*/
select convert(char(10),u.create_time ,120) as Date FROM userlog u

/*将日期加入到数据表中*/
SELECT *,convert(char(10),u.create_time ,120) as c_date FROM userlog u

/*将上一步的数据表作为新的数据库，按name,template和c_date进行分组求最新时间*/
SELECT max(create_time) as mt,template_name FROM (SELECT *,convert(char(10),u.create_time ,120) as c_date FROM userlog u) as temp group by creator_name,template_name,c_date

/*在上一步的基础上，使用内连接的方式，获取其他的属性*/
SELECT * FROM userlog u2 inner join (SELECT max(create_time) as mt,template_name FROM (SELECT *,convert(char(10),u.create_time ,120) as c_date FROM userlog u) as temp group by creator_name,template_name,c_date) as t ON u2.create_time =t.mt

/**最后的结果*/
SELECT * FROM phhlgypvc.realtime r2  inner join (SELECT max(create_time) as mt,template_name FROM (SELECT *,date_format(r.RecordTime, '%Y-%m-%d %H:%i:%s') as c_date FROM userlog u) as temp group by creator_name,template_name,c_date) as t ON r2.RecordTime =t.mt

参考文章：
1. SQL Server 取日期时间部分
2. sql语句中获取datetime的日期部分或时间部分

4.总结

在数据库中每五秒钟存储一条记录，每条记录中有一个字段Sum字段，如何求每一天中每一个小时的用量，可以用每一小时的最后一条记录减去每小时的第一条记录，这样就可以获取这一小时的用量了。思路如下：

(1)首先是先将数据记录的时间按60分钟分为一组，获取每条记录分组id号，创建临时表temp。

(2)在临时表中temp,根据这个分组id，进行分组，获取每一个分组内的最大的RecordTime,和分组id号，保存为 group_table 表。

(3)创建一个临时表，获取表的全部属性和groupid字段。

(4)然后使用内连接的方式，取时间等于RecordTime并且分组id相同的记录，这样就可以得到这一条数据的全部剩余属性了。

(5)最后一步就是使用java语言，变量所有的记录，用后一条记录减去钱一条记录，然后求每一个小时的用量就可以了。

SELECT *
FROM 
(
	select *,
		(
	    convert(numeric(20,0),(
	            convert(varchar,DATEADD(minute ,-1,RecordTime),112) * 100
	            +
	            (
	                DATEPART(HOUR ,DATEADD(minute ,-1,RecordTime)) * 60
	                +
	                DATEPART(MINUTE,DATEADD(minute ,-1,RecordTime))
	            ) / 60
	        )
	    )*1000+SiteNo
	) AS groupid
	FROM dbo.realtime r2
) AS result_table
inner join 
(
	SELECT
	    max(RecordTime) AS maxtime,
	    gid
	FROM (
		SELECT
		RecordTime,
		(
		    convert(numeric(20,0),(
		            convert(varchar,DATEADD(minute ,-1,RecordTime),112) * 100
		            +
		            (
		                DATEPART(HOUR ,DATEADD(minute ,-1,RecordTime)) * 60
		                +
		                DATEPART(MINUTE,DATEADD(minute ,-1,RecordTime))
		            ) / 60
		        )
		    )*1000+SiteNo
		) AS gid
		FROM dbo.realtime r
		WHERE (	RecordTime >= '2016-10-30 00:00:00' ) AND (RecordTime <= '2016-10-30 23:59:59' )  AND (SumQuant is NOT NULL ) AND (SumQuant >= 0)
	) AS temp
	GROUP BY gid
) AS group_table
ON (result_table.RecordTime =group_table.maxtime) AND (group_table.gid=result_table.groupid)
ORDER BY SiteNo ,RecordTime

还有一种写法，就是使用with语句，虽然这样看起来方便了，但是使用起来，效果要更加的差一些，执行时间更长。

with c as(
		SELECT
		*,
		(
		    convert(numeric(20,0),(
		            convert(varchar,DATEADD(minute ,-1,RecordTime),112) * 100
		            +
		            (
		                DATEPART(HOUR ,DATEADD(minute ,-1,RecordTime)) * 60
		                +
		                DATEPART(MINUTE,DATEADD(minute ,-1,RecordTime))
		            ) / 60
		        )
		    )*1000+SiteNo
		) AS gid
		FROM dbo.realtime r
		WHERE (	RecordTime >= '2016-10-30 00:00:00' ) AND (RecordTime <= '2016-10-30 23:59:59' )  AND (SumQuant is NOT NULL ) AND (SumQuant >= 0)
)
SELECT *
FROM c as result_table
inner join 
(
	SELECT
	    max(RecordTime) AS maxtime,
	    gid
	FROM c
	GROUP BY gid
) AS group_table
ON (result_table.RecordTime =group_table.maxtime) AND (group_table.gid=result_table.gid)
ORDER BY SiteNo ,RecordTime

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。