没有一个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()