表a:
AID Industry
01C001 1
01C000 2
01C003 2
02C001 3
这样的结构,一个Industry可以对应多个AID
表b:
id Menu_List
490 01C001|01C003|
123 01C001|01C003|01C004|
111 01C004|
Menu_List由a表的AID组成,各个菜单项之间以”|”线分隔;
现在想知道b表中的每条数据的Menu_List中存在多少个a表的Industry,请问如何实现?谢谢!
AID Industry
01C001 1
01C000 2
01C003 2
02C001 3
这样的结构,一个Industry可以对应多个AID
表b:
id Menu_List
490 01C001|01C003|
123 01C001|01C003|01C004|
111 01C004|
Menu_List由a表的AID组成,各个菜单项之间以”|”线分隔;
现在想知道b表中的每条数据的Menu_List中存在多少个a表的Industry,请问如何实现?谢谢!
--try
Select * from 表b b
where exists(Select * from 表a
where b.Menu_List like'%'||AID||'%')
结果是出来了,但是性能方面还是差了些,等一下我看一下可不可以优化一下,你先试试看噢 ...
SQL> select distinct
2 new_Industry
3 from (
4 select decode(sign(instr('|'||Menu_List,AID,rn)),1,Industry,0) as new_Industry
5 from B,
6 A,
7 (
8 select rownum rn
9 from all_objects
10 where rownum <= 10
11 )T
12 where instr('|'||Menu_List,'|',rn) >= 0
13 )tt
14 where new_Industry > 0;NEW_INDUSTRY
------------
1
2
select b.id,count(distinct a.industry) from a,b
where '|'||b.menu_list like '%|'||aid||'|%'
group by b.id
SQL> select distinct a.id from a,b
2 where length(replace(b.addr,a.num))<>length(b.addr);LENGTH(REPLACE(B.ADDR,A.NUM)) ADDR NUM ID ID
----------------------------- ------------------------------ -------------------- ---------- -------
8 c20001|c30001| c20001 1 232
7 c2002|c20001| c20001 1 233
14 c20001|c30001|c3004| c20001 1 235
8 c2002|c20001| c2002 1 233
8 c20001|c30001| c30001 2 232
14 c20001|c30001|c3004| c30001 2 235
1 c3004| c3004 3 234
15 c20001|c30001|c3004| c3004 3 235已选择8行。SQL> select * from a; ID NUM
---------- --------------------
1 c20001
1 c2002
2 c30001
3 c3004SQL> select * from b; ID ADDR
---------- ------------------------------
232 c20001|c30001|
233 c2002|c20001|
234 c3004|
235 c20001|c30001|c3004|SQL>
用replace也可以
对于这个我还是推荐用过程来实现 因为那样可以确定是完全正确
像用一个SQL的有点担心它的准确度
搂主现在大概有多少条数据呢?两个表各有多少呢?
select c.Menu_List, count(*)
from (select * from a, b where Instr(b.menu_list, a.aid, 1) <> 0) c
group by c.Menu_List;用这个试试看!不过如果B表中有但A表中没有的,那就不会显示出来,不知道是否满足你的需求!
假如不存在的话,就是长度不变。
长度变化了,就是存在了。
如果要查看B表里对应哪些A表的东西 目前我就知道2个思路:
1、B表每行只记录1条记录,例如:
id Menu_List
490 01C001
490 01C003
123 01C001
123 01C003
123 01C004
111 01C004
2、B表的Menu_list建立全文索引create index myindex on B(Menu_List)
indextype is ctxsys.context parameters('lexer DEFAULT_LEXER');
查询时
select a.*,b.id from A,B where contains(b.menulist,a.AID);
即可
不过全文索引好像每次变动(或者定期)需要重建(alter index myidnex rebuild).(我听说的)
不过由于B网有100万记录 强烈要求B表每条记录只记录1个A网的Aid
XD,下面是两种方法,第一种方法(我最先写的那个)优化了一下,第二种方法是与第一种方法不同的思路,我试了一下第二种的方法速度快一些,你可以试一下看 ..然后相关字段建索引 .. Just try it ..SQL> select distinct
2 new_Industry
3 from (
4 select decode(sign(instr('|'||Menu_List,AID,1,rn)),1,Industry,0) as new_Industry
5 from B,
6 A,
7 T
8 where instr('|'||Menu_List,'|',1,rn+1)-instr('|'||Menu_List,'|',1,rn) > 0
9 )tt
10 where new_Industry > 0;NEW_INDUSTRY
------------
1
2SQL>
SQL> select distinct
2 Industry
3 from A,
4 (
5 select B.id,
6 substr('|'||Menu_List,
7 instr('|'||Menu_List,'|',1,rn)+1,
8 instr('|'||Menu_List,'|',1,rn+1)-instr('|'||Menu_List,'|',1,rn)-1) as new_Industry
9 from B,
10 tt
11 where instr('|'||Menu_List,'|',1,rn+1)-instr('|'||Menu_List,'|',1,rn) > 0
12 )AB
13 where A.AID = AB.new_Industry; INDUSTRY
----------
1
2
他就是把A表的纪录,一个一个检索。
比如说,
01C001 在Menu_List里面出现的话,长度就变了。
然后检索
01C000 没有出现的话,长度就没有变。差不多等于A表的纪录,都去B里面检索了一次,然后Count.这是一个对这个问题比较常见的想法。赫赫。在一个A表数据量 * B表数据量 的笛卡尔积里面运算。你的B里面有百万条,要算上一段时间了。赫赫。¦?是什么符号?赫赫
01C001
01C01
01C011
看看效果呢?我在表T1上面,分别对AID,Industry建立了索引.
T1表1000条数据,T1表1百万条数据的情况下,用了大约30分钟。数据库是10G的。
A表里面的数据,有没有这样的状况?
01C001
01C01
01C011 没有,长度是固定的
属于不算很复杂的问题,29楼的其实基本可以,只要稍微改进以下,要注意到一个industry是可以对应比较多aid的。
所以可以这样修改:
Select Menu_List,(Select Count(aid)
From (select distinct aid from a) T1
Where INSTR(T2.Menu_List,AID) > 0) FROM T2
最后,如果你的这个查询如果是用于一个查询的应用中,这样的设计是非常错误,非常不合格的。把压力分担到无数个小操作中应该是比较基本的原则,你可以在修改menu_list的同时就计算个数(可以增加一个字段)。
这样,你最终只要对b表来个full scan 就可以了,现在就算最简洁的sql,你也要做一个有点恐怖的scan,焉能不慢?