mysql union all多表order by和group by一起使用(先排序后分组)

分组之前

SELECT *
FROM (
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_fore_credit_apply_record
	WHERE shop_id = '2000043'
	UNION ALL
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_apply_credit_record
	WHERE shop_id = '2000043'
	UNION ALL
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_loan_record
	WHERE shop_id = '2000043'
) temp1
ORDER BY temp1.user_id, temp1.CREATE_TIME DESC;

分组之后

-- order by和group by 一起使用
SELECT temp2.user_id, max(temp2.create_time)
FROM (
	SELECT *
	FROM (
		SELECT user_id, CREATE_TIME, shop_id
		FROM large_fore_credit_apply_record
		WHERE shop_id = '2000043'
		UNION ALL
		SELECT user_id, CREATE_TIME, shop_id
		FROM large_apply_credit_record
		WHERE shop_id = '2000043'
		UNION ALL
		SELECT user_id, CREATE_TIME, shop_id
		FROM large_loan_record
		WHERE shop_id = '2000043'
	) temp1
	ORDER BY temp1.CREATE_TIME DESC
) temp2
GROUP BY temp2.user_id;


-- 进行分组取最大的max(create_time)对应的user_id
SELECT temp1.user_id, max(temp1.create_time)
FROM (
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_fore_credit_apply_record
	WHERE shop_id = '2000043'
	UNION ALL
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_apply_credit_record
	WHERE shop_id = '2000043'
	UNION ALL
	SELECT user_id, CREATE_TIME, shop_id
	FROM large_loan_record
	WHERE shop_id = '2000043'
) temp1
GROUP BY temp1.user_id;