表a
name type_code role_type
a 11 1
b 11 1
b 1K 2
c 19 1
c 1K 2
c 11 1
d 1k 2
e 19 2
....................
....................
....................想得到表为
name type_code role_type
a 11 1
b 11 1
c 11 1
d 1k 2
e 19 2取name对应的type_code ,role_type 如果name没有重复的就直接取 如果有重复的 则取 type_code=11或type_code=19
优先级为 11>19>1K 非常感谢!
name type_code role_type
a 11 1
b 11 1
b 1K 2
c 19 1
c 1K 2
c 11 1
d 1k 2
e 19 2
....................
....................
....................想得到表为
name type_code role_type
a 11 1
b 11 1
c 11 1
d 1k 2
e 19 2取name对应的type_code ,role_type 如果name没有重复的就直接取 如果有重复的 则取 type_code=11或type_code=19
优先级为 11>19>1K 非常感谢!
create TABLE t3 (name varchar2(5),type_code VARCHAR2(5), role_type NUMBER(3));insert into t3 values('a','11','1');
insert into t3 values('b','11','1');
insert into t3 values('b','1K','2');
insert into t3 values('c','19','1');
insert into t3 values('c','1K','2');
insert into t3 values('c','11','1');
insert into t3 values('d','1k','2');
insert into t3 values('e','19','2');
COMMIT;SELECT *
FROM (SELECT NAME,
TYPE_CODE,
ROLE_TYPE,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY TYPE_CODE) RN
FROM T3)
WHERE RN = 1;
如果我改变优先级 为19>11>1K 那怎么做?
SELECT *
FROM t3 a
WHERE (a.name, a.type_code) IN
(SELECT t.name, MIN(t.type_code) type_code FROM t3 t GROUP BY t.name);
--方法2:使用exists
SELECT *
FROM t3 a
WHERE EXISTS (SELECT 1
FROM (SELECT t.name, MIN(t.type_code) type_code FROM t3 t GROUP BY t.name) b
WHERE b.name = a.name AND
b.type_code = a.type_code);
--方法3:使用分析函数,如楼上的oracledbalgtu的回复
--如果11,19,1k不是正好升序的关系,可以利用deocde生成一列来排序
select 'a' name,'11' type_code,1 role_type from dual
union all
select 'b' name,'11' type_code,1 role_type from dual
union all
select 'b' name,'1k' type_code,2 role_type from dual
union all
select 'c' name,'19' type_code,1 role_type from dual
union all
select 'c' name,'1k' type_code,2 role_type from dual
union all
select 'c' name,'11' type_code,1 role_type from dual
union all
select 'd' name,'1k' type_code,2 role_type from dual
union all
select 'e' name,'19' type_code,2 role_type from dual
)
select t.name,t.type_code,t.role_type from temp t,
(
select name,case when instr(col1,'11') > 0 then '11'
when instr(col1,'19') > 0 then '19'
when instr(col1,'1k')>0 then '1k' end case from(
select name,wm_concat(type_code) col1,wm_concat(role_type) col2 from temp group by name
)
) p where t.name = p.name and t.type_code = p.case结果:
1 a 11 1
2 b 11 1
3 c 11 1
4 d 1k 2
5 e 19 2
SELECT *
FROM (SELECT NAME,
TYPE_CODE,
ROLE_TYPE,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY(CASE t3.type_code
WHEN '19' THEN
1
WHEN '11' THEN
2
WHEN '1K' THEN
3
END)) RN
FROM T3)
WHERE RN = 1;