select a.c_id,isnull(b.c_yhzh,c.c_yhzh) from a left join b on a.c_id=b.c_id and a.c_type=1 left join c on a.c_id=c.c_id and a.c_type=2
select A.C_ID,C_YHZH FROM A,B WHERE A.C_ID=B.CID ADN C_type=1 UNION ALL select A.C_ID,C_YHZH FROM A,C WHERE A.C_zhbh=C.C_zhbh ADN C_type=2
CREATE TABLE A(C_ID INT,C_ZHBH INT,C_TYPE INT) INSERT A VALUES(1,1,1) INSERT A VALUES (2,2,1) INSERT A VALUES (2,1,2) INSERT A VALUES (1,2,2) CREATE TABLE b(C_ID INT,C_YHZH INT) INSERT b VALUES (1,2) INSERT b VALUES (2,2) INSERT b VALUES (2,1) CREATE TABLE C(C_ZHBH INT,C_YHZH INT) INSERT C VALUES(1,3) INSERT C VALUES(1,2) INSERT C VALUES(2,2)select A.C_ID,C_YHZH FROM A,B WHERE A.C_ID=B.C_ID AND C_type=1 UNION ALL select A.C_ID,C_YHZH FROM A,C WHERE A.C_zhbh=C.C_zhbh AND C_type=2 /*------------- 1 2 2 2 2 1 2 3 2 2 1 2 --------*/
select C_ID, (case C_type when 1 then (select top 1 C_YHZH from 表B where C_ID =表A.C_ID )) when 2 then (select top 1 C_YHZH from 表C where C_zhbh=表A.C_zhbh)) end) as C_YHZH from 表A
select a.C_ID,b.C_YHZH from ta left join tb on ta.cid=tb.cid where a.C_type=1 union all select a.C_ID,c.C_YHZH from ta left join tc on ta.C_zhbh=tc.C_zhbh where a.C_type=2
select a.c_id,isnull(b.c_yhzh,c.c_yhzh)
from a left join b on a.c_id=b.c_id and a.c_type=1
left join c on a.c_id=c.c_id and a.c_type=2
FROM A,B
WHERE A.C_ID=B.CID ADN C_type=1
UNION ALL
select A.C_ID,C_YHZH
FROM A,C
WHERE A.C_zhbh=C.C_zhbh ADN C_type=2
INSERT A VALUES(1,1,1)
INSERT A VALUES (2,2,1)
INSERT A VALUES (2,1,2)
INSERT A VALUES (1,2,2)
CREATE TABLE b(C_ID INT,C_YHZH INT)
INSERT b VALUES (1,2)
INSERT b VALUES (2,2)
INSERT b VALUES (2,1)
CREATE TABLE C(C_ZHBH INT,C_YHZH INT)
INSERT C VALUES(1,3)
INSERT C VALUES(1,2)
INSERT C VALUES(2,2)select A.C_ID,C_YHZH
FROM A,B
WHERE A.C_ID=B.C_ID AND C_type=1
UNION ALL
select A.C_ID,C_YHZH
FROM A,C
WHERE A.C_zhbh=C.C_zhbh AND C_type=2
/*-------------
1 2
2 2
2 1
2 3
2 2
1 2
--------*/
C_ID,
(case C_type
when 1 then (select top 1 C_YHZH from 表B where C_ID =表A.C_ID ))
when 2 then (select top 1 C_YHZH from 表C where C_zhbh=表A.C_zhbh))
end) as C_YHZH
from
表A
union all
select a.C_ID,c.C_YHZH from ta left join tc on ta.C_zhbh=tc.C_zhbh where a.C_type=2