求一条多表关联语句

[taih]

本人对sql语句不熟，请大家帮忙写一下下面的语句

有三句语句分别为：

第一句:
select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm from v_zb_kc_draft a left join t_bm_splimit b on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1 Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp

第二名：
select bm_gsjg, bm_ck, bm_sp, mc, dj_ls, sl_qm from v_zb_kc Where is_kc = 1 And isnull(bm_gsjg,'') like 'CYZ' And isnull(bm_sp,'') like 'DPZBZD001%' ORDER BY bm_gsjg,bm_ck,bm_sp

第三句：
SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' ) and bm_gsjg = '' and bm_ck = '' and bzgg = 0

第一句与第二句中的bm_gsjg,bm_ck,bm_sp,mc为相同值
第一句、第二句、第三句中的bm_sp为相同值

如何关联这三条语句，一次得到如下的值
第一句中的sl_qm
第二句中的sl_qm
第三句中的t_bm_sp.dj_min

[--小F--]

left join

[bancxc]

select A.sl_qm ,B.sl_qm ,C.dj_min
(
select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  
from  v_zb_kc_draft a 
left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  
Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' 
--ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp 
) A
left join 
( 
select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  
from  v_zb_kc  
Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%' 
--ORDER BY bm_gsjg,bm_ck,bm_sp 
) B on A.bm_gsjg=bm_gsjg. and A.bm_ck=B.bm_ck and A.bm_sp=B.bm_sp and A.mc=B.mc
(
SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  
WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0
) C on A.bm_sp=C.bm_sp
ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp

[fhjzgsy]

看花眼了，呵呵

[bancxc]

select A.sl_qm ,B.sl_qm ,C.dj_min
(
select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  
from  v_zb_kc_draft a 
left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  
Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' 
--ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp 
) A
left join 
( 
select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  
from  v_zb_kc  
Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%' 
--ORDER BY bm_gsjg,bm_ck,bm_sp 
) B on A.bm_gsjg=bm_gsjg. and A.bm_ck=B.bm_ck and A.bm_sp=B.bm_sp and A.mc=B.mc
left join 
(
SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  
WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0
) C on A.bm_sp=C.bm_sp
ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp

[--小F--]

select
a.sl_qm,b.sl_qm,c.dj_min
from
(select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm from v_zb_kc_draft a left join t_bm_splimit b on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1 Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%')a
left join
(select bm_gsjg, bm_ck, bm_sp, mc, dj_ls, sl_qm from v_zb_kc Where is_kc = 1 And isnull(bm_gsjg,'') like 'CYZ' And isnull(bm_sp,'') like 'DPZBZD001%')b
on
a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=.mc
left join
(SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' ) and bm_gsjg = '' and bm_ck = '' and bzgg = 0)c
on
a.bm_sp=c.bm_sp

[jwdream2008]

Up->看明白的！

[--小F--]

select 
  a.sl_qm,b.sl_qm,c.dj_min 
from 
  (select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%')a 
left join 
  (select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%')b 
on 
  a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=.mc 
left join 
  (SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0)c 
on 
  a.bm_sp=c.bm_sp
order by
  a.bm_gsjg,a.bm_ck,a.bm_sp

[taoistong]

select A.sl_qm ,B.sl_qm ,C.dj_min
(
select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm
from v_zb_kc_draft a
left join t_bm_splimit b on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1
Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%'
--ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp
) A
left join
(
select bm_gsjg, bm_ck, bm_sp, mc, dj_ls, sl_qm
from v_zb_kc
Where is_kc = 1 And isnull(bm_gsjg,'') like 'CYZ' And isnull(bm_sp,'') like 'DPZBZD001%'
--ORDER BY bm_gsjg,bm_ck,bm_sp
) B on A.bm_gsjg=bm_gsjg. and A.bm_ck=B.bm_ck and A.bm_sp=B.bm_sp and A.mc=B.mc
left join
(
SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp
WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' ) and bm_gsjg = '' and bm_ck = '' and bzgg = 0
) C on A.bm_sp=C.bm_sp
ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp

[taih]

致 fredrickhu
代码报错啊

select 
  a.sl_qm,b.sl_qm,c.dj_min 
from 
  (select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%')a 
left join 
  (select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%')b 
on 
  a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=.mc 
left join 
  (SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0)c 
on 
  a.bm_sp=c.bm_sp
order by
  a.bm_gsjg,a.bm_ck,a.bm_sp

报错信息如下
服务器: 消息 170，级别 15，状态 1，行 8
第 8 行: '=' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 10
第 10 行: 'c' 附近有语法错误。

[feixianxxx]

[Quote=引用 9 楼 zxsjs 的回复:]
致 fredrickhu
代码报错啊
SQL codeselect
a.sl_qm,b.sl_qm,c.dj_minfrom
(select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qmfrom v_zb_kc_draft aleftjoin t_bm_splimit bon a.bm_gsjg= b.bm_gsjgand a.bm_ck= b.bm_ckand a.bm_sp= b.bm_spand is_kc=1Where1=1Andisnull(a.bm_gsjg,'')like'CYZ'and sl_qm>0and a.bm_splike'DPZBZD001%')aleftjoin
(select bm_gsjg, bm_ck, bm_sp, mc, dj_ls, sl_qmfrom v_zb_kcWhere is_kc=1Andisnull(bm_gsjg,'')like'CYZ'Andisnull(bm_sp,'')like'DPZBZD001%')bon
a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=.mcleftjoin
(SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_minFROM t_bm_spdj, t_bm_spWHERE t_bm_spdj.bm_sp= t_bm_sp.bmand ( t_bm_spdj.bm_sp='DPZBZD001' )and bm_gsjg=''and bm_ck=''and bzgg=0)con
a.bm_sp=c.bm_sporderby
a.bm_gsjg,a.bm_ck,a.bm_sp

报错信息如下
服务器: 消息 170，级别 15，状态 1，行 8
第 8 行: '=' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 10
第 10 行: 'c' 附近有语法错误。

[/Quote]

select 
  a.sl_qm,b.sl_qm,c.dj_min 
from 
  (select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1 
   Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%')a 
left join 
  (select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc 
   Where is_kc = 1   And isnull(bm_gsjg,'') like 'CYZ'  And isnull(bm_sp,'') like 'DPZBZD001%')b 
on 
  a.bm_gsjg=b.bm_gsjg and a.bm_ck=b.bm_ck and a.bm_sp=b.bm_sp and a.mc=b.mc 
left join 
  (SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  
  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0)c 
on 
  a.bm_sp=c.bm_sp
order by
  a.bm_gsjg,a.bm_ck,a.bm_sp

try~

[--小F--]

--掉了个b
select 
  a.sl_qm,b.sl_qm,c.dj_min 
from 
  (select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%')a 
left join 
  (select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%')b 
on 
  a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=b.mc 
left join 
  (SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0)c 
on 
  a.bm_sp=c.bm_sp
order by
  a.bm_gsjg,a.bm_ck,a.bm_sp

[SQL77]

那些条件都得改为AND连接

[--小F--]

提示那么明显 第8行的语句中掉了个b

[--小F--]

a.mc=b.mc

[SQL77]

on
a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=b.mc

你这些点点不改成AND能出来?

[dawugui]

--以下三个不知道哪个适合你?

--1
select t1.sl_qm , t2.sl_qm , t3.dj_min from 
(
  select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp 
) t1 left join 
(
  select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%' ORDER BY bm_gsjg,bm_ck,bm_sp 
) t2 on t1.bm_gsjg = t2.bm_gsjg and t1.bm_ck = t2.bm_ck and t1.bm_sp = t2.bm_sp and t1.mc = t2.mc left join
(
  SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0 
) t3 on t2.bm_sp = t3.bm_sp

--2
select t1.sl_qm , t2.sl_qm , t3.dj_min from 
(
  select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp 
) t1 inner join 
(
  select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%' ORDER BY bm_gsjg,bm_ck,bm_sp 
) t2 on t1.bm_sp = t2.bm_sp inner join
(
  SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0 
) t3 on t2.bm_sp = t3.bm_sp

--3
select t1.sl_qm , t2.sl_qm , t3.dj_min from 
(
  select a.bm_gsjg, a.bm_ck, a.bm_sp, a.mc, a.dj_ls,a.sl_qm  from  v_zb_kc_draft a left join  t_bm_splimit b  on a.bm_gsjg = b.bm_gsjg and a.bm_ck = b.bm_ck and a.bm_sp = b.bm_sp and is_kc = 1  Where 1 = 1 And isnull(a.bm_gsjg,'') like 'CYZ' and sl_qm > 0 and a.bm_sp like 'DPZBZD001%' ORDER BY a.bm_gsjg,a.bm_ck,a.bm_sp 
) t1 ,
(
  select  bm_gsjg,  bm_ck, bm_sp,    mc, dj_ls,    sl_qm  from  v_zb_kc  Where is_kc = 1        And isnull(bm_gsjg,'') like 'CYZ'      And isnull(bm_sp,'') like 'DPZBZD001%' ORDER BY bm_gsjg,bm_ck,bm_sp 
) t2 ,
(
  SELECT t_bm_spdj.bm_sp, t_bm_spdj.dj_ls, t_bm_sp.dj_min FROM t_bm_spdj, t_bm_sp  WHERE t_bm_spdj.bm_sp = t_bm_sp.bm and ( t_bm_spdj.bm_sp = 'DPZBZD001' )  and bm_gsjg = '' and bm_ck = '' and bzgg = 0 
) t3 

[taih]

谢谢，看到了b加上了，条件改为and就可以了，但是还有一个问题现在出来的结果是以第一条语句的查询结果条数为准的，原本第一条和第二条的查询结果条数可能是不相同的，比如说第一条语句查出来的是2条记录，第二条查出来的3条记录，那么通过现在的语句查询出来就只有第一条语句的两条记录
想问一下，可不可以把第一条和第二条记录全部显示出来，也就是说以查询结果记录条数多的那条语句为主

[lovezx1028]

p梁牛啊。。。b都掉了一个。。