分类数据:
CID CName
-------------
1 A
2 B
3 C
主表数据:
MID MName CID CODE
------------------------
1 M 1 1
2 M 2 1
3 H 1 2
4 H 3 2
5 K 1 3
6 K 2 3
现在 我想根据 分类 A + B 查询 主表数据 结果集为:
CODE MName
------------------
1 M
3 K
-------------------------------------------------------
哪位大牛 可以帮小弟 写下? 在线等 完事立马结贴把分送上!!!
数据库 Oracle
------解决方案--------------------
假设上下两张表分别为TAB1和TAB2:
select tab2.code, tab2.mname
from tab2, tab1
where tab2.cid = tab1.cid
and tab1.cid in ('A', 'B')
group by tab2.code, tab2.mname;
------解决方案--------------------
create table pm_test(CID number, CName varchar2(30))
insert into pm_test values(1,'A');
insert into pm_test values(2,'B');
insert into pm_test values(3,'C');
commit;
create table pm_test1(MID number, MName varchar2(50), CID number, CODE number)
insert into pm_test1 values (1,'M',1,1);
insert into pm_test1 values (2,'M',2,1);
insert into pm_test1 values (3,'H',1,2);
insert into pm_test1 values (4,'H',3,2);
insert into pm_test1 values (5,'K',1,3);
insert into pm_test1 values (6,'K',2,3);
commit;
--查询思路:把同一个CODE+MNAME的CID组合成一个值,然后根据这个值关联查询即可
select code,mname from
(
select t2.code,t2.mname,wm_concat(t2.cid) cid from pm_test1 t2 group by t2.code,t2.mname
) t3,(select wm_concat(t2.cid) cid from pm_test t2 where cname in ('A', 'B')) t4
where t3.cid=t4.cid
--结果
CODE MNAME
1 1 M
2 3 K