有个比较有意思的查询语句,问问大家~
有表结构和数据如下,ID表示工号,TYPE表示名字类型,NAME则记录名字
TYPE为0,表示NAME中的名字为中文,1则为英文
行号 id type name
110王海
211jon
320小李
431happy
先希望通过最简单的SQL语句来查询得到结果如下
行号 ID CNAME ENAME
11王海jon
22小李
33happy
一下是我自己写的SQL,但是总觉得效率有问题,请大家帮忙出出主意
select distinct t.id,t1.cname,t2.ename from test_table t,
(select id,name as cname from test_table where type = 0) t1,
(select id,name as ename from test_table where type = 1) t2
where t.id = t1.id(+) and t.id = t2.id(+)
order by t.id
[解决办法]
select
id,
max(decode(type,0,name, ' ')) cname,
max(decode(type,1,name, ' ')) ename
from tb
group by id
[解决办法]
测试范例
CREATE TABLE AA(RNUM NUMBER, ID NUMBER, TYPE NUMBER, NAME VARCHAR2(20));
INSERT INTO AA VALUES(1, 1, 0, '王海 ');
INSERT INTO AA VALUES(2, 1, 1, 'jon ');
INSERT INTO AA VALUES(3, 2, 0, '小李 ');
INSERT INTO AA VALUES(4, 3, 1, 'happy ');
SELECT ROWNUM, ID, DECODE(TYPE, 0, NAME) AS NAME,
DECODE(SEC_NAME, NULL, DECODE(TYPE, 1, NAME, SEC_NAME),
SEC_NAME) AS CNAME
FROM (
SELECT ID, TYPE, NAME,
LEAD(NAME, 1, NULL) OVER (PARTITION BY ID ORDER BY TYPE) AS SEC_NAME,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TYPE) AS SEQ
FROM AA
)
WHERE SEQ = 1
