日期:2014-05-16 浏览次数:20742 次
select Name,color from
(select Name,COLOR1 color,1 col
from Pen,Color
where Pen.ID=Color.ID
union all
select Name,COLOR2,2
from Pen,Color
where Pen.ID=Color.ID)
order by Name desc,col;
create table t1 (id number,name VARCHAR2(10));
create table t2 (id number,color1 varchar2(10),color2 varchar(10));
insert into t1 values(1,'钢笔');
insert into t1 values(3,'水笔');
insert into t1 values(4,'圆珠笔');
insert into t2 values(1 ,'红色','黑色');
insert into t2 values(4,'黄色','红色');
select t1.name,t.color from t1,
(
select id,color1 as color from T2
UNION
select id,color2 from t2) t where t1.id=t.id;
drop table t1;
drop table t2;
结果显示:
NAME COLOR
---------- ----------
钢笔 黑色
钢笔 红色
圆珠笔 红色
圆珠笔 黄色
4 rows selected
WITH pen AS(
SELECT '1'id,'gang bi'NAME FROM dual
UNION ALL
SELECT '3','shui bi' FROM dual
UNION ALL
SELECT '4','yuan zhu bi' FROM dual
),
color AS(
SELECT '1'id,'hong se'color1,'hei se'color2 FROM dual
UNION ALL
SELECT '4','huang se','hong se' FROM dual
)
select pen.NAME,color.color1,color.color2 FROM pen,color WHERE pen.id=color.id