56,677
社区成员
发帖
与我相关
我的任务
分享
select a.ROLE_ID,a.INFO
from (T_ACC_ROLE a left join T_ACC_ROLE b on a.ROLE_ID=b.ROLE_ID and a.PRIORITY<b.PRIORITY)
left join T_ACC_ROLE_NOR on a.ROLE_ID=T_ACC_ROLE_NOR.ROLE_ID
where b.ROLE_ID is null and T_ACC_ROLE_NOR.ROLE_ID is null
union all
select ROLE_ID,INFO
from T_ACC_ROLE_NOR
mysql> -- -------------------------------
mysql> -- Author: liangCK 小梁
mysql> -- -------------------------------
mysql>
mysql> -- > 生成测试数据: [T_ACC_ROLE]
mysql> DROP TABLE IF EXISTS T_ACC_ROLE;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE T_ACC_ROLE (ACC_ROLE_ID INT,ROLE_ID INT,PRIORITY INT,INFO INT);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO T_ACC_ROLE
-> SELECT 1,2,1,11 UNION ALL
-> SELECT 2,2,2,12 UNION ALL
-> SELECT 3,2,3,13 UNION ALL
-> SELECT 4,4,1,14;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> -- > 生成测试数据: [T_ACC_ROLE_NOR]
mysql> DROP TABLE IF EXISTS T_ACC_ROLE_NOR;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE T_ACC_ROLE_NOR (ACC_ROLE_ID INT,ROLE_ID INT,INFO INT);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO T_ACC_ROLE_NOR
-> SELECT 1,2,16 UNION ALL
-> SELECT 2,3,17 UNION ALL
-> SELECT 4,5,19;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> -- SQL查询如下:
mysql>
mysql> SELECT ROLE_ID,INFO FROM T_ACC_ROLE_NOR
-> UNION ALL
-> SELECT ROLE_ID,INFO FROM T_ACC_ROLE AS A
-> WHERE NOT EXISTS(SELECT * FROM T_ACC_ROLE_NOR WHERE ROLE_ID=A.ROLE_ID)
-> AND NOT EXISTS(SELECT * FROM T_ACC_ROLE WHERE A.ROLE_ID=ROLE_ID AND PRIORITY<A.PRIORITY)
-> ORDER BY 1;
+---------+------+
| ROLE_ID | INFO |
+---------+------+
| 2 | 16 |
| 3 | 17 |
| 4 | 14 |
| 5 | 19 |
+---------+------+
4 rows in set (0.00 sec)