select a.name,b.name from test a,test b where a.value=TRANSLATE (a.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') and b.value<>TRANSLATE (b.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') and a.value=TRANSLATE (b.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
有一表 value name A 大类一 A01 小类1 A02 小类2 B 大类二
我想得到一个查询,查出一条数据,即显示小类,又显示小类所属的大类的名称 大类 小类 大类一 小类1 大类一 小类2 select m.name , n.name from (select * from tb where len(value) = 1) m, (select * from tb where len(value) <> 1) n where m.value = substr(n.name,1,1)
select m.name "大類", n.name "小類" from (select * from ( select 'A' value, '大類一' name from dual union select 'A01' , '小類1' from dual union select 'A02' , '小類2' from dual union select 'A03' , '小類3' from dual union select 'B' , '大類二' from dual union select 'B01' , '小類1' from dual ) t where length(t.value) = 1) m, (select * from ( select 'A' value, '大類一' name from dual union select 'A01' , '小類1' from dual union select 'A02' , '小類2' from dual union select 'A03' , '小類3' from dual union select 'B' , '大類二' from dual union select 'B01' , '小類1' from dual ) t1 where length(t1.value) <> 1) n where m.value = substr(n.value,1,1) order by 1------------------------- 大類 小類 1 大類一 小類1 2 大類一 小類2 3 大類一 小類3 4 大類二 小類1
from test a,test b
where a.value=TRANSLATE (a.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
and b.value<>TRANSLATE (b.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
and a.value=TRANSLATE (b.value, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
A01 小类1
A02 小类2 B 大类二
我想得到一个查询,查出一条数据,即显示小类,又显示小类所属的大类的名称
大类 小类
大类一 小类1
大类一 小类2
select m.name , n.name from
(select * from tb where len(value) = 1) m,
(select * from tb where len(value) <> 1) n
where m.value = substr(n.name,1,1)
select m.name "大類", n.name "小類" from
(select * from
(
select 'A' value, '大類一' name from dual union
select 'A01' , '小類1' from dual union
select 'A02' , '小類2' from dual union
select 'A03' , '小類3' from dual union
select 'B' , '大類二' from dual union
select 'B01' , '小類1' from dual
) t where length(t.value) = 1) m,
(select * from
(
select 'A' value, '大類一' name from dual union
select 'A01' , '小類1' from dual union
select 'A02' , '小類2' from dual union
select 'A03' , '小類3' from dual union
select 'B' , '大類二' from dual union
select 'B01' , '小類1' from dual
) t1 where length(t1.value) <> 1) n
where m.value = substr(n.value,1,1)
order by 1-------------------------
大類 小類
1 大類一 小類1
2 大類一 小類2
3 大類一 小類3
4 大類二 小類1