本人对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
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
解决方案 »
- sql=" update result set studentstatus = '未知' where num = m_num ";哪里有问题
- 高手帮我看看一段超长的存储过程,分不够再加
- SQL2005怎样可以通过增加服务器的形式提高负荷能力?
- SQL 启动问题
- 火烧眉毛啦!datetime字段存的是格林尼治时间,我怎么办啊
- ms sqlserver导入导出
- SQL中Select的字段别名相加,真是难那,高人帮帮忙
- 在线急等:单行子查询返回多于一行
- SQLSERVER数据库日志文件太大了,怎么减小呀?
- spool on,spool temp.txt等是什么意思?
- 问一个sql语句
- 问个存储过程的参数问题
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
(
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
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
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
(
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
代码报错啊
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' 附近有语法错误。
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~
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
a.bm_gsjg=b.bm_gsjg,a.bm_ck=b.bm_ck,a.bm_sp=b.bm_sp,a.mc=b.mc 你这些点点不改成AND能出来?
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
想问一下,可不可以把第一条和第二条记录全部显示出来,也就是说以查询结果记录条数多的那条语句为主