userid roleid rightid
1 1 1
23 3321 2
32323 3 3
2 23 4
4325 6 75
1 23 5
1 4 6
54 23 67
1 1 1筛选出roleid列不重复的
选出来应该是1 1 1
23 3321 2
32323 3 3
2 23 4
4325 6 75
54 23 67
1 1 1
23 3321 2
32323 3 3
2 23 4
4325 6 75
1 23 5
1 4 6
54 23 67
1 1 1筛选出roleid列不重复的
选出来应该是1 1 1
23 3321 2
32323 3 3
2 23 4
4325 6 75
54 23 67
from URTInfo a
where a.roleid in(
select roleid from (
select t.roleid,count(*) from URTInfo t group by t.roleid having count(*) <= 1
)
)
from user_info a
group by a.userid
having count(*) =1;
CREATE TABLE TEST_T
(
USERID NUMBER,
ROLEID NUMBER,
RIGHTID NUMBER
);---- 测试数据
INSERT INTO TEST_T VALUES(1 , 1 , 1 );
INSERT INTO TEST_T VALUES(23 , 3321 , 2 );
INSERT INTO TEST_T VALUES(32323 , 3 , 3 );
INSERT INTO TEST_T VALUES(2 , 23 , 4 );
INSERT INTO TEST_T VALUES(4325 , 6 , 75 );
INSERT INTO TEST_T VALUES(1 , 23 , 5 );
INSERT INTO TEST_T VALUES(1 , 4 , 6 );
INSERT INTO TEST_T VALUES(54 , 23 , 67 );
INSERT INTO TEST_T VALUES(1 , 1 , 1 );
COMMIT;---- SQL 语句
SELECT R.USERID,
R.ROLEID,
R.RIGHTID
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.USERID ORDER BY T.USERID, T.ROLEID, T.RIGHTID) RN --- 具体排序规则自己定
FROM TEST_T T) R
WHERE R.RN = 1