sql语句求教

[程序员阿歪]

SOS,sql请教一波,感谢。
ad表如下:
app_code :app唯一标识
crt_time:创建时间

查询:每个APP的日记录条数,最近一小时后的数据条数(查询结果如下结构)
app 最新更新时间 最近一个小时收集数 今天收集数
app1 2018-7-12 00:00 90 200
app2 2018-7-12 10:00 50 100

[zjcxc]

SELECT app_code AS APP, MAX(crt_time) AS 最新更新时间,
	SUM(CASE WHEN crt_time >= NOW() - INTERVAL 1 HOUR THEN 1 END) AS 最近一个小时收集数,
	COUNT(*) AS 今天收集数
FROM ad
WHERE crt_time >= CURRENT_DATE
GROUP BY app_code

[zjcxc]

如果考虑最后一小时可能跨天的情况

SELECT app_code AS APP, MAX(crt_time) AS 最新更新时间,
	SUM(CASE WHEN crt_time >= NOW() - INTERVAL 1 HOUR THEN 1 END) AS 最近一个小时收集数,
	SUM(CASE WHEN crt_time >= CURRENT_ATE THEN 1 END) AS 今天收集数
FROM ad
WHERE crt_time >= NOW() - INTERVAL 24 HOUR
GROUP BY app_code

[程序员阿歪]

感谢,但是这样where 条件是不是全部只能满足crt_time >= CURRENT_DATE?

[程序员阿歪]

贴一下我的sql(mysql):

SELECT a.app_code,a.lastAdTime,c.todayCount,b.latestOneHourCount FROM
(SELECT app_code,MAX(crt_time) lastAdTime FROM advertisement GROUP BY app_code) a
 LEFT JOIN 
(SELECT app_code,COUNT(1) latestOneHourCount  FROM advertisement WHERE crt_time >= :lastMin and crt_time <= :now  GROUP BY app_code) b 
ON a.app_code = b.app_code 
LEFT JOIN
 ( SELECT	app_code,COUNT(1) todayCount  FROM advertisement WHERE crt_time >= :today  GROUP BY app_code) c 
ON a.app_code = c.app_code

如有不足之处还请各位指出~