多表左连接、多排序mysql

• 说明：

eaa_schedule_user是记录用户是否已读的（中间）表，base_v_user_group_department是用户（视图）表，其中readTime为null视为未读，readTime不为null视为已读，base_v_user_group_department中userOrder是用户的排序

• 需求：

1、未读排在前面，已读排在后面；2、用户按照userOrder升序排序；

• 分析：

1、mysql字段值为null，值最小，所以使用升序排序；
2、未读和已读分开查询，然后使用union关键字连接查询结果；
3、内部使用left join左连接查询，eaa_schedule_user作为左表，base_v_user_group_department为右表；

SELECT * FROM (
		(SELECT
		b.departmentName,
		b.groupName,
		b.`name` AS readUserName,
		b.userOrder,
		a.readTime,
		0 as od
		FROM
		eaa_schedule_user AS a
		LEFT JOIN base_v_user_group_department AS b ON a.userId = b.id
		WHERE 1 = 1
		AND ( a.scheduleId = '')
		AND ( b.departmentName != NULL OR b.departmentName != '')
		AND a.readTime IS NULL
		ORDER BY b.userOrder ASC)

		UNION

		(SELECT
		b.departmentName,
		b.groupName,
		b.`name` AS readUserName,
		b.userOrder,
		a.readTime,
		1 as od
		FROM
		eaa_schedule_user AS a
		LEFT JOIN base_v_user_group_department AS b ON a.userId = b.id
		WHERE 1 = 1
		AND ( a.scheduleId = '')
		AND ( b.departmentName != NULL OR b.departmentName != '')
		AND a.readTime IS NOT NULL
		ORDER BY b.userOrder ASC)
) as a
where 1 = 1 
order by a.od ASC,a.userOrder asc