WITH a AS( SELECT '01' code,'足球' NAME FROM dual UNION ALL SELECT '02','排球' FROM dual UNION ALL SELECT '03','公司级' FROM dual UNION ALL SELECT '04','市级' FROM dual ), b AS( SELECT '03' classle1,'01' classle2 FROM dual UNION ALL SELECT '04','02' FROM dual) SELECT a1.NAME,a2.NAME FROM b,a a1,a a2 WHERE b.classle1=a1.code(+) AND b.classle2=a2.code(+)
这个意思?with a as( select '01' code,'足球' name from dual union all select '02' code,'排球' name from dual union all select '03' code,'公司级' name from dual union all select '04' code,'市级' name from dual ),b as( select '03' classle1,'01' classle2 from dual union all select '04' code,'02' name from dual ) select (select name from a where a.code = b.classle1) classle1, (select name from a where a.code = b.classle2) name from b
select d.name,e.name from 表a d , 表a e, 表b f where d.code = f.classle1 and e.code = f.classle2
select E.NAME AS CLASSLE1,F.NAME AS NAME from ( select C.NAME,C.CLASSLE2 from( select * from a inner join b on a.code=b.classle1) C) E join (select D.NAME,D.CLASSLE2 from( select * from a inner join b on a.code=b.classle2) D ) F on E.CLASSLE2=F.CLASSLE2 ORDER BY F.CLASSLE2 DESC 楼上似乎忽略了排序问题,楼主期望的答案是倒序的。
classle2对应不是表a的code么?没错,表b里的classle1和classle2都是表a里面的code。
SELECT '01' code,'足球' NAME FROM dual UNION ALL
SELECT '02','排球' FROM dual UNION ALL
SELECT '03','公司级' FROM dual UNION ALL
SELECT '04','市级' FROM dual
),
b AS(
SELECT '03' classle1,'01' classle2 FROM dual UNION ALL
SELECT '04','02' FROM dual)
SELECT a1.NAME,a2.NAME FROM b,a a1,a a2 WHERE b.classle1=a1.code(+) AND b.classle2=a2.code(+)
select '01' code,'足球' name from dual
union all
select '02' code,'排球' name from dual
union all
select '03' code,'公司级' name from dual
union all
select '04' code,'市级' name from dual
),b as(
select '03' classle1,'01' classle2 from dual
union all
select '04' code,'02' name from dual
)
select (select name from a where a.code = b.classle1) classle1,
(select name from a where a.code = b.classle2) name
from b
这是写死的语句吗?code和name是可以维护的。
这是写死的语句吗?code和name是可以维护的。
可以维护,不影响使用。。但是注意code的唯一性。。
这是写死的语句吗?code和name是可以维护的。
可以维护,不影响使用。。但是注意code的唯一性。。
请教:增加05,06,07....这样的,不需要改动这个语句了吗?
这是写死的语句吗?code和name是可以维护的。
建议你使用我写那个关联,子查询要用的话要确保数据不误,否则会报返回多行的。。
这是写死的语句吗?code和name是可以维护的。没有写死啊,你只要保证a表的code唯一就行
from 表a d , 表a e, 表b f
where
d.code = f.classle1 and
e.code = f.classle2
select C.NAME,C.CLASSLE2 from(
select * from a inner join b on a.code=b.classle1)
C) E
join
(select D.NAME,D.CLASSLE2 from(
select * from a inner join b on a.code=b.classle2)
D ) F
on
E.CLASSLE2=F.CLASSLE2
ORDER BY F.CLASSLE2 DESC
楼上似乎忽略了排序问题,楼主期望的答案是倒序的。