mysql 获取昨天数据 utc时间

# yzj邀请昨日数据
SELECT s.id, s.create_at, ch.id, ch.code AS channel, c.id
	, c.code AS custom, so.id, so.code AS source
FROM invite_ship s
	LEFT JOIN invite_channel ch ON ch.id = s.invite_channel_id
	LEFT JOIN invite_code_custom c ON c.id = s.code_custom_id
	LEFT JOIN invite_source so ON s.invite_source_id = so.id
WHERE s.invite_source_id != 0
	AND s.create_at > date_sub(date_sub(curdate(), INTERVAL 1 DAY), INTERVAL 8 HOUR)
	AND s.create_at < date_sub(curdate(), INTERVAL 8 HOUR)
ORDER BY s.id DESC

　　此处 s.create_at是时间字段， 如果你库里存的是utc时间 用这条sql逻辑准没错

 

此sql是获取聚合后的集合 

SELECT t.user_id, GROUP_CONCAT(t.amount ORDER BY t.amount DESC)
FROM (SELECT ord.user_id, ord.amount, ord.create_at
	FROM order_order ord
	WHERE ord.user_id > 0
		AND create_at > 0
	ORDER BY user_id ASC, create_at DESC
	) t
GROUP BY user_id;

　　

此sql是集合根据逗号分隔取第一个

SELECT t.user_id, substring_index(GROUP_CONCAT(t.amount ORDER BY t.amount DESC), ',', 1)
FROM (SELECT ord.user_id, ord.amount, ord.create_at
	FROM order_order ord
	WHERE ord.user_id > 0
		AND create_at > 0
	ORDER BY user_id ASC, create_at DESC
	) t
GROUP BY user_id;

　　

 

转载于:https://www.cnblogs.com/shenwenlong/p/7375056.html