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

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

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

/*将时间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

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 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