根据记录值有条件的查询（可能有点复杂）

[大道如海]

不用存储过程，只能用SQL文

TA表
ID
kind
no

MA表
no
name

MB表
no
name

能否根据TA的kind值来动态选择MA或者MB表来进行查询

如kind=10
则TA.no = MA.no

kind=20
则TA.no = MB.no

[bzszp]

select * from ta,ma where ta.kind=10 and ta.no=ma.no
union all
select * from ta,mb where ta.kind=20 and ta.no=mb.no;

[freddy2003]

用case...when

[xhyph]

好像不太可能吧,,,,
光表名就没办法选择,,表结构有问题

[freddy2003]

Simple CASE Example
For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.

SELECT cust_last_name,
CASE credit_limit WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium' END
FROM customers;

CUST_LAST_NAME CASECR
-------------------- ------
...
Bogart Medium
Nolte Medium
Loren Medium
Gueney Medium

Searched CASE Example
The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000 END) "Average Salary" from employees e;

Average Salary
--------------
6461.68224

[freddy2003]

试一下，没测试过
select case kind=10 when then (select ta.id from ta,ma where ta.kind=10 and ta.no=ma.no)
else (select ta.id from ta,mb where ta.kind=20 and ta.no=mb.no) end from tA;

[大道如海]

case...when
可以用在WHERE子句里吗

[大道如海]

原先我也用union解决
但是如果原来有100行SQL文
UNION一下就200行
如果有10种情况，则就是1000行，
感觉不是很好，
不知道能不能在WHERE里解决

[bzszp]

嵌套查询
select ta.*,decode(id,10,select name from ma where ta.no=ma.no,
20,select name from mb where ta.no=mab.no,null) name from ta;

[大道如海]

楼上的好像和用UNION的效果差不错

[大道如海]

急

[biliky]

select decode(t.id,10,a.no,20,b.no,null) from ta t,
(select no from MA where t.no=MA.no) a,
(select no from MB where t.no=MB.no) b;

楼主还想追求到何种完美方才罢休？

[liuyi8903]

用sql应该解决不了.

[suxiao]

用decode试试！

[大道如海]

这样可以吗
SELECT TA.ID,
TA.kind,
TA.no,
DECODE(TA.kind,10,MA.NAME,20,MB.NAME,'')
FORM TA,
MA,
MB
WHERE TA.no = MA.no(+)
AND TA.no = MB.no(+)