有个比较有意思的查询语句,问问大家~
有表结构和数据如下,ID表示工号,TYPE表示名字类型,NAME则记录名字
TYPE为0,表示NAME中的名字为中文,1则为英文
行号         id             type             name
1 1 0 王海
2 1 1 jon
3 2 0 小李
4 3 1 happy

先希望通过最简单的SQL语句来查询得到结果如下
行号           ID               CNAME         ENAME
1 1 王海 jon
2 2 小李
3 3 happy

一下是我自己写的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