根据一个字段决定由一条记录查出两条记录还是一条记录表t:
id a b c
1 hello world test
2 nihao pengyou 查询结果:
1 hello world
1 test world
2 nihao pengyou
id a b c
1 hello world test
2 nihao pengyou 查询结果:
1 hello world
1 test world
2 nihao pengyou
id englishname age chinesename
1 John 18 张三
2 Smith 20
查询结果:id name age
1 John 18
1 张三 18
2 Smith 20
union select id,chinesename name ,age from tablename where tablename is not null) a order by id
union
select id ,chinesename,age from table where chinesename is not null
select 'John' englishname,'18' age,'三' chinesename from dual
union
select 'Smith' englishname,'20' age,'' chinesename from dual
)
select a.englishname as name,a.age from a where a.chinesename is null
union
select a.englishname as name,a.age from a where a.chinesename is not null
union
select a.chinesename as name,a.age from a where a.chinesename is not null
select id, name, age
from (select id,englishname name,age
from tablename
union
select id,chinesename name ,age
from tablename
where chinesename is not null) a
order by id
SQL> SELECT ID,
2 ENGLISHNAME "NAME",
3 AGE
4 FROM TABLENAME T1
5 UNION
6 SELECT ID,
7 CHINESENAME "NAME",
8 AGE
9 FROM TABLENAME T2
10 WHERE CHINESENAME IS NOT NULL; ID NAME AGE
---------- -------- ----------
1 JOHN 18
1 ZHANGSHA 18
2 SMITH 20SQL>
from (
select id, case when rn = 1 then englishname
when rn = 2 then case when chinesename is not null
then chinesename
end
end as englishname,
age
from (
select b.rn, a.*
from tt a,
(select rownum as rn from dual connect by rownum < 3) b
) x
) y
where englishname is not null