SQL如何取一段时间内的数据进行公式计算

[way193]

insertintoRB_ZLRB(date,zt_bll,zt_mbz,xz_bll,xz_mbz,hck_bll,hck_mbz,dg_bll,dg_mbz,jd_bll,jd_mbz,mk_bll,mk_mbz,bm_bll,bm_mbz,dc_bll,dc_mbz,mt_bll,mt_mbz,fj_bll,fj_mbz,a_bll,a_mbz,b_bll,b_mbz,mkl_bll,mkl_mbz,bml_bll,bml_mbz,dcl_bll,dcl_mbz,mtl_bll,mtl_mbz,fjl_bll,fjl_mbz,qcl_bll,qcl_mbz,tpv_bll,tpv_mbz,gj_bll,gj_mbz,ycl_bll,ycl_mbz,wg_bll,wg_mbz,gc_bll,gc_mbz,jz_scsl,jz_blsl,jz_bll,jz_mbz,mft_scsl,mft_blsl,mft_bll,mft_mbz,jzzj_jypc,jzzj_blpc,jzzj_bll,jzzj_mbz,jh_jypc,jh_blpc,jh_bll,jh_mbz,tpv_jypc,tpv_blpc,tpv1_bll,
tpv1_mbz,bf_qcl,bf_lh,bf_jc,bf_jh,zxs_wtgj,dq_wtgj,aq_wtgj,zxs_wtyf,dq_wtyf,aq_wtyf,zxs_jytl,dq_jytl,aq_jytl,zxs_glxt,dq_glxt,aq_glxt)SELECTa2.dateasdate,max(a2.zt_bll)aszt_bll,max(a2.zt_mbz)aszt_mbz,max(a2.xz_bll)asxz_bll,
max(a2.xz_mbz)asxz_mbz,max(a2.hck_bll)ashck_bll,max(a2.hck_mbz)ashck_mbz,MAX(a2.dg_bll)asdg_bll
,MAX(a2.dg_mbz)asdg_mbz,MAX(a2.jd_bll)asjd_bll,MAX(a2.jd_mbz)asjd_mbz,MAX(a2.mk_bll)asmk_bll,
MAX(a2.mk_mbz)ASmk_mbz,MAX(a2.bm_bll)asbm_bll,MAX(a2.bm_mbz)asbm_mbz,MAX(a2.dc_bll)asdc_bll,
MAX(a2.dc_mbz)asdc_mbz,MAX(a2.mt_bll)asmt_bll,MAX(a2.mt_mbz)asmt_mbz,MAX(a2.fj_bll)asfj_bll,
MAX(a2.fj_mbz)asfj_mbz,MAX(a2.a_bll)asa_bll,MAX(a2.a_mbz)asa_mbz,MAX(a2.b_bll)asb_bll,
MAX(a2.b_mbz)asb_mbz,MAX(A2.mkl_bll)ASmkl_bll,MAX(a2.mkl_mbz)asmkl_mbz,MAX(a2.bml_bll)asbml_bll,
MAX(a2.bml_mbz)asbml_mbz,MAX(a2.dcl_bll)asdcl_bll,MAX(a2.dcl_mbz)asdcl_mbz,MAX(a2.mtl_bll)asmtl_bll,
MAX(a2.mtl_mbz)asmtl_mbz,MAX(a2.fjl_bll)asfjl_bll,MAX(a2.fjl_mbz)asfjl_mbz,MAX(a2.qcl_bll)asqcl_bll,
MAX(a2.qcl_mbz)asqcl_mbz,MAX(a2.tpv_bll)astpv_bll,MAX(a2.tpv_mbz)astpv_mbz,MAX(a2.gj_bll)asgj_bll,
MAX(a2.gj_mbz)asgj_mbz,MAX(a2.ycl_bll)asycl_bll,max(a2.ycl_mbz)asycl_mbz,MAX(a2.wg_bll)aswg_bll,MAX(a2.wg_mbz)aswg_mbz,
MAX(a2.gc_bll)asgc_bll,MAX(a2.gc_mbz)asgc_mbz,MAX(a2.jz_scsl)asjz_scsl,MAX(a2.jz_blsl)asjz_blsl,
MAX(a2.jz_bll)asjz_bll,MAX(a2.jz_mbz)asjz_mbz,MAX(a2.mft_scsl)asmft_scsl,MAX(a2.mft_blsl)asmft_blsl,
MAX(a2.mft_bll)asmft_bll,MAX(a2.mft_mbz)asmft_mbz,MAX(a2.jzzj_jypc)asjzzj_jypc,MAX(a2.jzzj_blpc)asjzzj_blpc,
MAX(a2.jzzj_jzbll)asjzzj_bll,MAX(a2.jzzj_mbz)asjzzj_mbz,MAX(a2.jh_jypc)asjh_jypc,max(a2.jh_blpc)asjh_blpc,
MAX(a2.jh_bll)asjh_bll,MAX(a2.jh_mbz)asjh_mbz,MAX(a2.tpv_jypc)astpv_jypc,max(a2.tpv_blpc)astpv_blpc,MAX(a2.tpv1_bll)astpv1_bll,
MAX(a2.tpv1_mbz)astpv1_mbz,MAX(a2.bf_qcl)asbf_qcl,MAX(a2.bf_lh)asbf_lh,MAX(a2.bf_jc)asbf_jc,MAX(a2.bf_jh)asbf_jh,
MAX(a2.zxs_wtgj)aszxs_wtgj,MAX(a2.dq_wtgj)asdq_wtgj,MAX(a2.aq_wtgj)asaq_wtgj,MAX(a2.zxs_wtyf)aszxs_wtyf,
MAX(a2.dq_wtyf)asdq_wtyf,MAX(a2.aq_wtyf)asaq_wtyf,MAX(a2.zxs_jytl)aszxs_jytl,MAX(a2.dq_jytl)asdq_jytl,
MAX(a2.aq_jytl)asaq_jytl,MAX(a2.zxs_glxt)aszxs_glxt,MAX(a2.dq_glxt)asdq_glxt,MAX(a2.aq_glxt)asaq_glxt
FROM (selectISNULL(LH_ZT.date,convert(varchar(10),getdate()- 1,120))ASdate,
isnull(SUM(LH_ZT.lh_fq)/SUM(LH_ZT.lh_cl)*1000000,0)aszt_bll,isnull(MBZ_LH.zt_mbz,0)aszt_mbz,
isnull(SUM(LH_XZ.lh_fq)/SUM(LH_XZ.lh_cl)*1000000,0)asxz_bll,isnull(MBZ_LH.xz_mbz,0)asxz_mbz,
isnull(SUM(LH_HCK.lh_fq)/SUM(LH_HCK.lh_cl)*1000000,0)ashck_bll,MBZ_LH.hck_mbzashck_mbz,
isnull(SUM(LH_DG.lh_fq)/SUM(LH_DG.lh_cl)*1000000,0)asdg_bll,isnull(MBZ_LH.dg_mbz,0)asdg_mbz,
isnull(SUM(LH_JD.lh_fq)/SUM(LH_JD.lh_cl)*1000000,0)asjd_bll,isnull(MBZ_LH.jd_mbz,0)asjd_mbz,
isnull((sum(JH_RBB.jh_fgzl)+sum(JH_RBB.jh_bfzl))/sum(JH_RBB.jh_jhzl)*1000000,0)ASmk_bll,
isnull(MBZ_JH.mk_mbz,0)asmk_mbz,isnull((sum(d.jh_fgzl)+sum(d.jh_bfzl))/sum(d.jh_jhzl)*1000000,0)ASbm_bll,
isnull(MBZ_JH.bm_mbz,0)asbm_mbz,isnull((sum(e.jh_fgzl)+sum(e.jh_bfzl))/sum(e.jh_jhzl)*1000000,0)ASdc_bll,
isnull(MBZ_JH.dc_mbz,0)asdc_mbz,isnull((sum(f.jh_fgzl)+sum(f.jh_bfzl))/sum(f.jh_jhzl)*1000000,0)ASmt_bll,
isnull(MBZ_JH.mt_mbz,0)asmt_mbz,isnull((sum(g.jh_fgzl)+sum(g.jh_bfzl))/sum(g.jh_jhzl)*1000000,0)ASfj_bll,
isnull(MBZ_JH.fj_mbz,0)ASfj_mbz,isnull(SUM(LJ_ZLRB.lj_blcs)/sum(LJ_ZLRB.lj_sccs)*1000000,0)asa_bll,
isnull(MBZ_LJ.a_mbz,0)ASa_mbz,isnull(SUM(a1.lj_blcs)/sum(a1.lj_sccs)*1000000,0)asb_bll,
isnull(MBZ_LJ.b_mbz,0)ASb_mbz,isnull((sum(JC_JCGC.jc_ssje)+sum(JC_FLTH.jc_je)+sum(JC_BJ.jc_bfje)+sum(JC_CLBLP.jc_bfje))/sum(JC_JCJH.jc_decz)*1000000,0)ASmkl_bll,
isnull(MBZ_JC.mkl_mbz,0)asmkl_mbz,isnull((sum(h.jc_ssje)+sum(i.jc_je)+sum(j.jc_bfje)+sum(k.jc_bfje))/sum(l.jc_decz)*1000000,0)ASbml_bll,
isnull(MBZ_JC.bml_mbz,0)asbml_mbz,isnull((sum(m.jc_ssje)+sum(n.jc_je)+sum(o.jc_bfje)+sum(p.jc_bfje))/sum(q.jc_decz)*1000000,0)ASdcl_bll,
isnull(MBZ_JC.dcl_mbz,0)asdcl_mbz,isnull((sum(r.jc_ssje)+sum(x.jc_je)+sum(t.jc_bfje)+sum(u.jc_bfje))/sum(v.jc_decz)*1000000,0)ASmtl_bll,
isnull(MBZ_JC.mtl_mbz,0)asmtl_mbz,isnull((sum(w.jc_ssje)+sum(s.jc_je)+sum(y.jc_bfje)+sum(z.jc_bfje))/sum(aa.jc_decz)*1000000,0)ASfjl_bll,
isnull(MBZ_JC.fjl_mbz,0)asfjl_mbz,isnull(SUM(qcl_wtbkt)/SUM(qcl_cl)*1000000,0)ASqcl_bll,
isnull(MBZ_QCL.qcl_mbz,0)ASqcl_mbz,isnull(SUM(TPV_SCGC_JH.tpv_blsl)/sum(TPV_SCGC_JH.tpv_jhsl)*1000000,0)astpv_bll,
ISNULL(MBZ_TPV.tpv_mbz,0)AStpv_mbz,
hgl_gjasgj_bll,mbz_gjasgj_mbz,bll_yclasycl_bll,mbz_yclasycl_mbz,bll_wgaswg_bll,
mbz_wgaswg_mbz,bll_gcasgc_bll,mbz_gcasgc_mbz,RB_JZ.scslasjz_scsl,RB_JZ.blslasjz_blsl,
RB_JZ.bllasjz_bll,RB_JZ.mbzasjz_mbz,RB_MFT.scslasmft_scsl,RB_MFT.blslasmft_blsl,
RB_MFT.bllasmft_bll,RB_MFT.mbzasmft_mbz,jz_jypcasjzzj_jypc,jz_blpcasjzzj_blpc,
jz_jzbllasjzzj_jzbll,jz_mbzasjzzj_mbz,jh_jypcasjh_jypc,jh_blpcasjh_blpc,jh_bllasjh_bll,
jh_mbzasjh_mbz,tpv_jypcastpv_jypc,tpv_blpcastpv_blpc,tpv1_bllastpv1_bll,
RB_JZZJ.tpv1_mbzastpv1_mbz,je_qclasbf_qcl,je_lhasbf_lh,je_jcasbf_jc,je_jhasbf_jh,
zxs_wtgjaszxs_wtgj,dq_wtgjasdq_wtgj,aq_wtgjasaq_wtgj,zxs_wtyfaszxs_wtyf,dq_wtyfasdq_wtyf,
aq_wtyfasaq_wtyf,zxs_jytlaszxs_jytl,dq_jytlasdq_jytl,aq_jytlasaq_jytl,zxs_glxtaszxs_glxt,
dq_glxtasdq_glxt,aq_glxtasaq_glxt
fromLH_ZTfullJOINLH_XZONLH_XZ.date=LH_ZT.date
fulljoinMBZ_LHONMBZ_LH.id=MBZ_LH.id
fulljoinLH_HCKONLH_HCK.date=LH_ZT.date
fulljoinLH_DGONLH_DG.date=LH_ZT.date
fulljoinLH_JDONLH_JD.date=LH_ZT.date
fulljoinJH_RBBONJH_RBB.date=LH_ZT.date
fulljoinMBZ_JHONMBZ_JH.id=MBZ_JH.id
fulljoinJH_RBBasdond.date=LH_ZT.date
fulljoinJH_RBBaseone.date=LH_ZT.date
fulljoinJH_RBBasfonf.date=LH_ZT.date
fulljoinJH_RBBasgong.date=LH_ZT.date
fulljoinLJ_ZLRBonLJ_ZLRB.date=LH_ZT.date
fullJOINLJ_ZLRBASa1ona1.date=LH_ZT.date
fulljoinMBZ_LJONMBZ_LJ.id=MBZ_LJ.id
fulljoinJC_JCGCONJC_JCGC.date=LH_ZT.date
fulljoinJC_FLTHONJC_FLTH.date=LH_ZT.date
fulljoinJC_BJONJC_BJ.date=LH_ZT.date
fulljoinJC_CLBLPONJC_CLBLP.date=LH_ZT.date
fulljoinJC_JCJHONJC_JCJH.date=LH_ZT.date
fulljoinMBZ_JConMBZ_JC.id=MBZ_JC.id
fulljoinJC_JCGCashONh.date=LH_ZT.date
fulljoinJC_FLTHasiONi.date=LH_ZT.date
fulljoinJC_BJasjonj.date=LH_ZT.date
fulljoinJC_CLBLPaskONk.date=LH_ZT.date
fulljoinJC_JCJHaslonl.date=LH_ZT.date
fulljoinJC_JCGCasmONm.date=LH_ZT.date
fulljoinJC_FLTHasnONn.date=LH_ZT.date
fulljoinJC_BJasoono.date=LH_ZT.date
fulljoinJC_CLBLPaspONp.date=LH_ZT.date
fulljoinJC_JCJHasqonq.date=LH_ZT.date
fulljoinJC_JCGCasrONr.date=LH_ZT.date
fulljoinJC_FLTHasxONx.date=LH_ZT.date
fulljoinJC_BJastont.date=LH_ZT.date
fulljoinJC_CLBLPasuONu.date=LH_ZT.date
fulljoinJC_JCJHasvonv.date=LH_ZT.date
fulljoinJC_JCGCaswONw.date=LH_ZT.date
fulljoinJC_FLTHassONs.date=LH_ZT.date
fulljoinJC_BJasyony.date=LH_ZT.date
fulljoinJC_CLBLPaszONz.date=LH_ZT.date
fulljoinJC_JCJHasaaonaa.date=LH_ZT.date
fulljoinQCL_HZonQCL_HZ.date=LH_ZT.date
fulljoinMBZ_QCLONMBZ_QCL.id=MBZ_QCL.id
fulljoinTPV_SCGC_JHonTPV_SCGC_JH.date=LH_ZT.date
fulljoinMBZ_TPVonMBZ_TPV.id=MBZ_TPV.id
fulljoinRBonRB.date=h.date
fulljoinRB_JZ onRB_JZ.date=h.date
fulljoinRB_MFTonRB_MFT.date=h.date
fulljoinRB_JZZJonRB_JZZJ.date=h.date
fulljoinRB_BFSSonRB_BFSS.date=h.date
fulljoinRB_BRTSonRB_BRTS.date=h.date
whereLH_ZT.date=convert(varchar(10),getdate()- 1,120)
ORLH_XZ.date=convert(varchar(10),getdate()- 1,120)
ORLH_HCK.date=convert(varchar(10),getdate()- 1,120)
ORLH_DG.date=convert(varchar(10),getdate()- 1,120)
ORLH_JD.date=convert(varchar(10),getdate()- 1,120)
ORJH_RBB.date=convert(varchar(10),getdate()- 1,120)
ORJH_RBB.date=convert(varchar(10),getdate()- 1,120)andJH_RBB.jh_lb='门框类'
ORd.date=convert(varchar(10),getdate()- 1,120)andd.jh_lb='背门类'
ORe.date=convert(varchar(10),getdate()- 1,120)ande.jh_lb='导槽类'
ORf.date=convert(varchar(10),getdate()- 1,120)andf.jh_lb='门条类'
ORg.date=convert(varchar(10),getdate()- 1,120)andg.jh_lb='附件类'
ORLJ_ZLRB.date=convert(varchar(10),getdate()- 1,120)ANDLJ_ZLRB.lj_lx='A段胶'
ORa1.date=convert(varchar(10),getdate()- 1,120)ANDa1.lj_lx='B段胶'
ORJC_JCGC.date=convert(varchar(10),getdate()- 1,120)andJC_JCGC.jc_lb='门框'
ORJC_FLTH.date=convert(varchar(10),getdate()- 1,120)andJC_FLTH.jc_lb='门框'
ORJC_BJ.date=convert(varchar(10),getdate()- 1,120)andJC_BJ.jc_lb='门框'
ORJC_CLBLP.date=convert(varchar(10),getdate()- 1,120)andJC_CLBLP.jc_lb='门框'
ORJC_JCJH.date=convert(varchar(10),getdate()- 1,120)andJC_JCJH.jc_lb='门框'
ORh.date=convert(varchar(10),getdate()- 1,120)andh.jc_lb='背门'
ORi.date=convert(varchar(10),getdate()- 1,120)andi.jc_lb='背门'

groupbyLH_ZT.date,MBZ_LH.zt_mbz,MBZ_LH.xz_mbz,MBZ_LH.hck_mbz,MBZ_LH.dg_mbz,MBZ_LH.jd_mbz,
MBZ_JH.mk_mbz,MBZ_JH.bm_mbz,MBZ_JH.dc_mbz,MBZ_JH.mt_mbz,MBZ_JH.fj_mbz,MBZ_LJ.a_mbz,MBZ_LJ.b_mbz,
MBZ_JC.mkl_mbz,MBZ_JC.bml_mbz,MBZ_JC.dcl_mbz,MBZ_JC.mtl_mbz,MBZ_JC.fjl_mbz,MBZ_QCL.qcl_mbz,MBZ_TPV.tpv_mbz
,RB.hgl_gj,RB.hgl_gj,RB.mbz_gj,RB.bll_ycl,RB.mbz_ycl,RB.bll_wg,RB.mbz_wg,RB.bll_gc,RB.mbz_gc,RB_JZ.scsl,
RB_JZ.blsl,RB_JZ.bll,RB_JZ.mbz,RB_MFT.scsl,RB_MFT.blsl,RB_MFT.bll,RB_MFT.mbz,RB_JZZJ.jz_jypc,
RB_JZZJ.jz_blpc,RB_JZZJ.jz_jzbll,RB_JZZJ.jz_mbz,RB_JZZJ.jh_jypc,RB_JZZJ.jh_blpc,RB_JZZJ.jh_bll,
RB_JZZJ.jh_mbz,RB_JZZJ.tpv_jypc,RB_JZZJ.tpv_blpc,RB_JZZJ.tpv1_bll,RB_JZZJ.tpv1_mbz,RB_BFSS.je_qcl,
RB_BFSS.je_lh,RB_BFSS.je_jc,RB_BFSS.je_jh,RB_BRTS.zxs_wtgj,RB_BRTS.dq_wtgj,RB_BRTS.aq_wtgj,
RB_BRTS.zxs_wtyf,RB_BRTS.dq_wtyf,RB_BRTS.aq_wtyf,RB_BRTS.zxs_jytl,RB_BRTS.dq_jytl,
RB_BRTS.aq_jytl,RB_BRTS.zxs_glxt,RB_BRTS.dq_glxt,RB_BRTS.aq_glxt)a2
groupbya2.date





这是一段连接多表进行公式运算的SQL语句，目的是为了将所有结果插入到RB_ZLRB中，这段SQL语句查询的条件是系统当前时间的前一天，现需求对系统当前时间的前七天的所有数据进行上述公式计算，结果插入到RB_ZLZB中，请问如何写？

[xdashewan]

convert(varchar(10),getdate()- 1,120)这是前1天，那么convert(varchar(10),getdate()- 7,120)这就是前7天

[way193]

引用 1 楼 xdashewan 的回复:

convert(varchar(10),getdate()- 1,120)这是前1天，那么convert(varchar(10),getdate()- 7,120)这就是前7天

不是单指一个时间，是一段时间，是convert(varchar(10),getdate()- 1,120)到convert(varchar(10),getdate()- 7,120)这段时间的数据用公式计算

[LongRui888]

你这个语句看着，真复杂啊，而且格式有问题

[KeepSayingNo]

你这前7天的数据放到临时表中，然后对临时表处理

[ahhfql]

[霜寒月冷]

时间段就这么写，注意 tb1字段数据好和tb字段数目一致

insert into tb1
select * from  tb where  t_date>= convert(varchar(10),getdate()- 7,120)
and t_date<=convert(varchar(10),getdate()- 1,120)