表1:ms_cf01select brid,brxm,ksdm,ysdm from ms_cf01 where zfpb=0 group by brid,brxm,ksdm,ysdm表2:ms_yj01select brid,brxm,ksdm,ysdm from ms_yj01 where zfpb=0 group by brid,brxm,ksdm,ysdm
然后,把表1和表2结果用union 连接起来(同时去掉重复的记录。)之后的结果是brid,brxm,ksdm,ysdm的列表表3 gy_ks的字段是
ksdm,ksmc表4 gy_ys的字段是
ysdm,ysmc
我想要的结果是
brid,brxm,ksmc,ysmc请高手指教!
然后,把表1和表2结果用union 连接起来(同时去掉重复的记录。)之后的结果是brid,brxm,ksdm,ysdm的列表表3 gy_ks的字段是
ksdm,ksmc表4 gy_ys的字段是
ysdm,ysmc
我想要的结果是
brid,brxm,ksmc,ysmc请高手指教!
SELECT t.BRID, t.BRXM, t.KSDM, t.YSDM, GY_KSDM.KSMC, GY_YGDM.YGXM
FROM (SELECT BRID, BRXM, KSDM, YSDM
FROM MS_CF01
WHERE (ZFPB = 0) AND (KFRQ >= '2010-1-28')
GROUP BY BRID, BRXM, KSDM, YSDM
UNION
SELECT BRID, BRXM, KSDM, YSDM
FROM MS_YJ01
WHERE (ZFPB = 0) AND (KDRQ >= '2010-1-28')
GROUP BY BRID, BRXM, KSDM, YSDM) AS t LEFT OUTER JOIN
GY_KSDM ON t.KSDM = GY_KSDM.KSDM LEFT OUTER JOIN
GY_YGDM ON t.YSDM = GY_YGDM.YGDM
from
(select brid,brxm,ksdm,ysdm from ms_cf01 where zfpb=0 group by brid,brxm,ksdm,ysdm ) a,
(select brid,brxm,ksdm,ysdm from ms_yj01 where zfpb=0 group by brid,brxm,ksdm,ysdm) b
,gy_ks c,gy_ys d
where a.brid = b.brid and a.ksdm = c.ksdm and a.ysdm = d.ysdm
from
(select brid,brxm,ksdm,ysdm
from (
select brid,brxm,ksdm,ysdm from ms_cf01
union
select brid,brxm,ksdm,ysdm from ms_yj01 ) a
where zfpb=0 group by brid,brxm,ksdm,ysdm) b
,gy_ks c,gy_ys d
where a.ksdm = c.ksdm and a.ysdm = d.ysdm
select distinct brid,brxm,ksdm,ysdm
from(
select brid,brxm,ksdm,ysdm from ms_cf01 where zfpb=0 group by brid,brxm,ksdm,ysdm
union all
select brid,brxm,ksdm,ysdm from ms_yj01 where zfpb=0 group by brid,brxm,ksdm,ysdm )T--链接
select Tb.brid,Tb.brxm,G.ksmc,Y.ysmc
From
(select distinct brid,brxm,ksdm,ysdm
from(select brid,brxm,ksdm,ysdm from ms_cf01 where zfpb=0 group by brid,brxm,ksdm,ysdm
union all
select brid,brxm,ksdm,ysdm from ms_yj01 where zfpb=0 group by brid,brxm,ksdm,ysdm )T
)Tb
left join gy_ks G on Tb.ksdm = G.ksdm
left join gy_ys Y on Tb.ysdm = G.ysdm
TRY
select brid,brxm,ysdm,ysmc
from ms_cf01 a inner join gy_ks b on a.ksdm=b.ksdm
inner join gy_ys c on a.ysdm=c.ysdm
where zfpb=0
union all
select brid,brxm,ysdm,ysmc
from ms_yj01 a inner join gy_ks b on a.ksdm=b.ksdm
inner join gy_ys c on a.ysdm=c.ysdm
where zfpb=0