放其中两张把,估计太复杂,不易看 CREATE view v_cw_qt_base(clid,fydate,qf,qk,qz,tb,tj,tk,tp,je,ts) as SELECT v_qf_bxtb.vehid, v_qf_bxtb.end_date, 0,0,0,1,0,0,0, 0 as je, datediff(day,begin_date,getdate()) as ts FROM v_qf_bxtb WHERE v_qf_bxtb.end_date < getdate() union all SELECT SF_CLHT.ID, v_sf_clht_qk.zzrq, 0,1,0,0,0,0,0, v_sf_clht_qk.qk, datediff(day,v_sf_clht_qk.zzrq,getdate()) as ts FROM v_sf_clht_qk, SF_CLHT WHERE ( v_sf_clht_qk.clhtid = SF_CLHT.CLHTID ) and SF_CLHT.ID is not null union all SELECT v_cw_sf_sfjs.ID, min(v_cw_sf_sfjs.jfsj), 1,0,0,0,0,0,0, sum(v_cw_sf_sfjs.jfje), datediff(day,min(v_cw_sf_sfjs.jfsj),getdate()) as ts FROM v_cw_sf_sfjs where v_cw_sf_sfjs.jfsj<getdate() and sfmmid in('1','4','7','22' ) group by v_cw_sf_sfjs.ID; CREATE view v_cw_sf_sfjs as select v_clxx.ID, v_clxx.ttype, v_cw_sf_sfbz.SFMMID, v_cw_sf_dbsfsj.jfsj, v_cw_sf_sfbz.sfdj , round((datediff(month,jfsj,getdate( ))+1)/month_charge_cycle+0.4,0) as jfsl, round((datediff(month,jfsj,getdate( ))+1)/month_charge_cycle+0.4,0) *v_cw_sf_sfbz.sfdj as jfje,
dateadd(day,1,jfsj) as znjsj, (case when dateadd(month,month_charge_cycle,jfsj)>=getdate() then 0 else sfdj*delay_percent/100*dbo.f_combination(round(datediff(day,jfsj,getdate())/(month_charge_cycle*30.4),0),round(datediff(day,jfsj,getdate())/month_charge_cycle*30.4,0)) +sfdj*delay_percent/100*round(cast(datediff(day,jfsj,getdate()) as int)%cast(month_charge_cycle*30 as int),0) end) as znj, sfdj*round(datediff(month,jfsj,getdate())/month_charge_cycle+0.4,0) as qkje, v_cw_sf_sfbz.CHARGE_MODE, v_cw_sf_sfbz.VEHICLE_MODE, v_cw_sf_sfbz.MONTH_CHARGE_CYCLE, v_cw_sf_sfbz.DELAY_PERCENT,1 as lx
from v_cw_sf_dbsfsj, v_cw_sf_sfbz, v_clxx where ( v_cw_sf_dbsfsj.SFMMID *= v_cw_sf_sfbz.SFMMID ) and ( v_cw_sf_dbsfsj.ID = v_clxx.ID ) and ( v_cw_sf_dbsfsj.GSID = v_cw_sf_sfbz.GSID ) and ( v_clxx.TeamID = v_cw_sf_sfbz.GSID ) and v_clxx.zjbz=0 AND case when syxz="非营运" then syxz else "营运" end =v_cw_sf_sfbz.yy AND rated_load/1000=v_cw_sf_sfbz.hd AND case when v_clxx.cllb is null or rtrim(cllb)='' then '货' else cllb end =v_cw_sf_sfbz.lb AND v_clxx.dw_ylf =v_cw_sf_sfbz.yd AND (v_clxx.ZW_YLF= v_cw_sf_sfbz.zz or v_clxx.ZW_YLF is null or v_clxx.ZW_YLF=0)下面还有好几层
问题是这个视图本身是一张报表,也是一个输出结果,不是让我再建一个存储过程吧?
forgot2000(忘记2000年)
太多,不好拆开(这些报表是最后的汇总表)
shinebei(灰灰)
那是不是说我要再做一张正式表,再从正式表中关联是吗?
我想应该能解决
问题是:我要改好多,而且做成VIEW可以支持很多查询条件
而存储过程就没有那么灵活了。
CREATE view v_cw_qt_base(clid,fydate,qf,qk,qz,tb,tj,tk,tp,je,ts)
as
SELECT v_qf_bxtb.vehid,
v_qf_bxtb.end_date,
0,0,0,1,0,0,0,
0 as je,
datediff(day,begin_date,getdate()) as ts
FROM v_qf_bxtb
WHERE v_qf_bxtb.end_date < getdate()
union all
SELECT SF_CLHT.ID,
v_sf_clht_qk.zzrq,
0,1,0,0,0,0,0,
v_sf_clht_qk.qk,
datediff(day,v_sf_clht_qk.zzrq,getdate()) as ts
FROM v_sf_clht_qk,
SF_CLHT
WHERE ( v_sf_clht_qk.clhtid = SF_CLHT.CLHTID ) and SF_CLHT.ID is not null
union all
SELECT v_cw_sf_sfjs.ID,
min(v_cw_sf_sfjs.jfsj),
1,0,0,0,0,0,0,
sum(v_cw_sf_sfjs.jfje),
datediff(day,min(v_cw_sf_sfjs.jfsj),getdate()) as ts
FROM v_cw_sf_sfjs where v_cw_sf_sfjs.jfsj<getdate() and sfmmid in('1','4','7','22' ) group by v_cw_sf_sfjs.ID;
CREATE view v_cw_sf_sfjs as
select
v_clxx.ID,
v_clxx.ttype,
v_cw_sf_sfbz.SFMMID,
v_cw_sf_dbsfsj.jfsj,
v_cw_sf_sfbz.sfdj ,
round((datediff(month,jfsj,getdate( ))+1)/month_charge_cycle+0.4,0) as jfsl,
round((datediff(month,jfsj,getdate( ))+1)/month_charge_cycle+0.4,0) *v_cw_sf_sfbz.sfdj as jfje,
dateadd(day,1,jfsj) as znjsj,
(case when dateadd(month,month_charge_cycle,jfsj)>=getdate() then 0
else sfdj*delay_percent/100*dbo.f_combination(round(datediff(day,jfsj,getdate())/(month_charge_cycle*30.4),0),round(datediff(day,jfsj,getdate())/month_charge_cycle*30.4,0))
+sfdj*delay_percent/100*round(cast(datediff(day,jfsj,getdate()) as int)%cast(month_charge_cycle*30 as int),0)
end) as znj,
sfdj*round(datediff(month,jfsj,getdate())/month_charge_cycle+0.4,0) as qkje,
v_cw_sf_sfbz.CHARGE_MODE,
v_cw_sf_sfbz.VEHICLE_MODE,
v_cw_sf_sfbz.MONTH_CHARGE_CYCLE,
v_cw_sf_sfbz.DELAY_PERCENT,1 as lx
from v_cw_sf_dbsfsj,
v_cw_sf_sfbz,
v_clxx
where ( v_cw_sf_dbsfsj.SFMMID *= v_cw_sf_sfbz.SFMMID ) and
( v_cw_sf_dbsfsj.ID = v_clxx.ID ) and
( v_cw_sf_dbsfsj.GSID = v_cw_sf_sfbz.GSID ) and
( v_clxx.TeamID = v_cw_sf_sfbz.GSID ) and
v_clxx.zjbz=0 AND
case when syxz="非营运" then syxz else "营运" end =v_cw_sf_sfbz.yy AND
rated_load/1000=v_cw_sf_sfbz.hd AND
case when v_clxx.cllb is null or rtrim(cllb)='' then '货' else cllb end =v_cw_sf_sfbz.lb AND
v_clxx.dw_ylf =v_cw_sf_sfbz.yd AND
(v_clxx.ZW_YLF= v_cw_sf_sfbz.zz or v_clxx.ZW_YLF is null or v_clxx.ZW_YLF=0)下面还有好几层
=SQLEXEC(ZQLCON,"EXEC BZCBDBB ?CPDHTEMP","TEMP")
执行后放入到表TEMP中,带参数CPDHTEMP,有什么不灵活的?
支持自定义报表吗?用户想再从其他地方选几列出来怎么办?好麻烦的。
都是用户的需求搞的。
因为视图嵌套太多层,SQL SERVER无法解释最终SQL。在早期的SQL Server™ 2000 版本中,使用 *= 和 =* 在 WHERE 子句中指定左、右外部联接条件。有时,该语法会导致有多种解释的不明确查询。FROM 子句中指定遵从 SQL-92 的外部联接,不会导致上述不确定性。