mysql+join on+case when

没有一个join on 解决不了的，不行就多个join,join使用的时候要注意数据的遗漏

#查询某人对应的所有角色名称以及对应的菜单及数据权限
SELECT DISTINCT
	(a.`name`) AS '角色名称',
	b.permissions_id,
	e.remark,
	n.description
FROM
	t_role a
JOIN t_role_permissions b ON a.id = b.role_id
JOIN t_role_user c ON b.role_id = c.role_id
JOIN t_user d ON c.user_id = d.id
JOIN t_menu e ON e.id = b.permissions_id
JOIN t_data_permissions n ON b.data_permissions = n.id
AND d.full_name = '测试'
AND b.effective = 1
AND c.effective = 1

#通过手机号查询用户所属的组-然后查出组对应的所有用户-查询出姓名
SELECT DISTINCT
	(m.full_name)
FROM
	t_group_personal d
INNER JOIN (
	SELECT
		b.group_id AS id
	FROM
		t_service_personal a
	JOIN t_group_personal b ON a.id = b.personal_id
	AND telephone_no = 11111111111
	AND b.effective = 1
) tmp ON tmp.id = d.group_id
JOIN t_service_personal c ON c.id = d.personal_id
JOIN t_user m ON m.mobile = c.telephone_no
AND d.effective = 1

3.查询"01"课程比"02"课程成绩高的学生的信息，如果对应课程无成绩也要参与比较(下面只是一种方法，应该有多种)

#JSON_TABLE的使用（获取字段值为json数据且要取到list中的所有值）
SELECT 
    order_code,
    td.liushuihao COLLATE utf8mb4_general_ci AS liushuihao,
		td.shoukuanzhanghao COLLATE utf8mb4_general_ci AS shoukuanzhanghao,
		td.shoukuanyinhang COLLATE utf8mb4_general_ci AS shoukuanyinhang,
		td.shoukuanyren COLLATE utf8mb4_general_ci AS shoukuanyren
FROM 
    collection_log a
JOIN 
    JSON_TABLE(
        JSON_EXTRACT(a.bank_request_param, '$.Data.transferDtls'),
        "$[*]"
        COLUMNS (
            liushuihao VARCHAR(255) PATH '$.dtlSerialNo',
						shoukuanzhanghao VARCHAR(255) PATH '$.rcvAcc',
						shoukuanyinhang VARCHAR(255) PATH '$.rcvBank',
						shoukuanyren VARCHAR(255) PATH '$.rcvName'
        )
    ) AS td
WHERE 
    a.content LIKE '%付款中%' and  DATE(operation_time) = CURDATE()