【网学网提醒】：网学会员为需要朋友们搜集整理了sqlSERVER语法分组文本文档相关资料，希望对各位网友有所帮助!
=================================
selectcheck_id,[zhgl],[jfgl],[zsb],[zhgl]+[jfgl]+[zsb]as总扣分
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromtbgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb]))
aspiv
eck_id
selectt1.*,t2.总分
from
(selectcheck_id,[综合（管理）],[机房（环境）],[主设备],[综合（管理）]+[机房（环境）]+[主设备]as项数
from
(selectcheck_id,itemname,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemname,check_id
)tt
pivot(sum(realCount)foritemnamein([综合（管理）],[机房（环境）],[主设备]))aspiv
)t1,
(selectcheck_id,sum(maxvalue*realvalue)as总分
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)t2
wheret1.check_id=t2.check_id
=================================
--每个check_id各行==》列
selectcheck_id,[zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb],
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asscore
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))
aspiv
---显示checkid,zhgl,.....score
=================================
selectt1.*,t2.*
from
(selectcheck_id,[综合（管理）],[机房（环境）],[主设备],[综合（管理）]+[机房（环境）]+[主设备]as项数
from
(selectcheck_id,itemname,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemname,check_id
)tt
pivot(sum(realCount)foritemnamein([综合（管理）],[机房（环境）],[主设备]))aspiv
)t1,
(selectcheck_id,[综合（管理）]ass1,[机房（环境）]ass2,[主设备]ass3,[综合（管理）]+[机房（环境）]+[主设备]as分数
from
(selectcheck_id,itemname,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemname,check_id
)tt
pivot(sum(realscore)foritemnamein([综合（管理）],[机房（环境）],[主设备]))aspiv
)t2
selectt1.*,t2.*
from
(selectcheck_id,[zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb],
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asitemnums
from
(selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)t1,
(selectcheck_id,[zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb],
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asitemscors
from
(selectcheck_id,itemcode,sum(
maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realscore)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)t2
wheret1.check_id=t2.check_id
=================================
selectstep1.*,step2.*
from
(selectcheck_id,
[zhgl]aszhglnums,
[jfgl]asjfglnums,
[zsb]aszsbnums,
[ttsb]asttsbnums,
[tksb]astksbnums,
[dlsb]asdlsbnums,
[jksb]asjksbnums,
[cssb]ascssbnums,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asitemnums
from
(selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tbgroup
pivot(sum(realCount)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)step1,
(selectcheck_id,
[zhgl]aszhglscore,
[jfgl]asjfglscore,
[zsb]aszsbscore,
[ttsb]asttsbscore,
[tksb]astksbscore,
[dlsb]asdlsbscore,
[jksb]asjksbscore,
[cssb]ascssbscore,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asitemscors
from
(selectcheck_id,itemcode,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tbgroup
pivot(sum(realscore)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)step2
wherestep1.check_id=step2.check_id
==================ok==============================
selectYD_KHJZ_XJZLJCB.file_ip,
sum(step.zhglscore)aszhglscore,
sum(step.jfglscore)asjfglscore,
sum(step.zsbscore)aszsbscore,
sum(step.ttsbscore)asttsbscore,
sum(step.tksbscore)astksbscore,
sum(step.dlsbscore)asdlsbscore,
sum(step.jksbscore)asjksbscore,
sum(step.cssbscore)ascssbscore,
sum(step.itemscors)astotalscore
fromYD_KHJZ_XJZLJCBINNERJOIN
(selectstep1.*,step2.*
from
(selectcheck_idascheck_id1,
[zhgl]aszhglnums,
[jfgl]asjfglnums,
[zsb]aszsbnums,
[ttsb]asttsbnums,
[tksb]astksbnums,
[dlsb]asdlsbnums,
[jksb]asjksbnums,
[cssb]ascssbnums,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asitemnums
from
(selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tbgroup
pivot(sum(realCount)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)step1,
(selectcheck_idascheck_id2,
[zhgl]aszhglscore,
[jfgl]asjfglscore,
[zsb]aszsbscore,
[ttsb]asttsbscore,
[tksb]astksbscore,
[dlsb]asdlsbscore,
[jksb]asjksbscore,
[cssb]ascssbscore,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]
asitemscors
from
(selectcheck_id,itemcode,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tbgroup
pivot(sum(realscore)foritemcodein([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))aspiv
)step2
wherestep1.check_id1=step2.check_id2
)step
ONYD_KHJZ_XJZLJCB.CHECK_ID=step.CHECK_ID1
where(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_KHJZ_XJZLJCB.file_ip
orderbyYD_KHJZ_XJZLJCB.file_ip
---显示ip,zhglscors,....totalscore
==================ok==============================
selectYD_RY.RY_BM_ID,YD_KHJZ_XJZLJCB.file_ip,
sum(step.zhgl)aszhglscore,
sum(step.jfgl)asjfglscore,
sum(step.zsb)aszsbscore,
sum(step.ttsb)asttsbscore,
sum(step.tksb)astksbscore,
sum(step.dlsb)asdlsbscore,
sum(step.jksb)asjksbscore,
sum(step.cssb)ascssbscore,
sum(step.score)astotalscore
fromYD_KHJZ_XJZLJCBINNERJOIN
(selectcheck_id,zhgl,jfgl,zsb,ttsb,tksb,dlsb,jksb,cssb,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asscore
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))
aspiv
)step
ONYD_KHJZ_XJZLJCB.CHECK_ID=step.CHECK_ID
INNERJOINYD_RYONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
where(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_RY.RY_BM_ID,YD_KHJZ_XJZLJCB.file_ip
orderbyYD_RY.RY_BM_ID,YD_KHJZ_XJZLJCB.file_ip
---显示YD_RY.RY_BM_ID,ip,zhglscors,....totalscore
==================ok==============================
selectYD_BM.BM_ID,YD_BM.BM_NAME,
sum(step.zhgl)aszhglscore,
sum(step.jfgl)asjfglscore,
sum(step.zsb)aszsbscore,
sum(step.ttsb)asttsbscore,
sum(step.tksb)astksbscore,
sum(step.dlsb)asdlsbscore,
sum(step.jksb)asjksbscore,
sum(step.cssb)ascssbscore,
sum(step.score)astotalscore
fromYD_KHJZ_XJZLJCBINNERJOIN
(selectcheck_id,zhgl,jfgl,zsb,ttsb,tksb,dlsb,jksb,cssb,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asscore
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))
aspiv
)step
ONYD_KHJZ_XJZLJCB.CHECK_ID=step.CHECK_ID
INNERJOINYD_RYONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
INNERJOINYD_BMONYD_RY.RY_BM_ID=YD_BM.BM_ID
where(CONVERT(varchar
(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM_NAME
orderbyYD_BM.BM_ID,YD_BM.BM_NAME
---显示YD_BM.BM_ID,YD_BM.BM_NAME,zhglscors,....totalscore
==================ok==============================
selectYD_BM.BM_ID,YD_BM.BM_NAME,
sum(step.zhgl)aszhglscore,
sum(step.jfgl)asjfglscore,
sum(step.zsb)aszsbscore,
sum(step.ttsb)asttsbscore,
sum(step.tksb)astksbscore,
sum(step.dlsb)asdlsbscore,
sum(step.jksb)asjksbscore,
sum(step.cssb)ascssbscore,
sum(step.score)astotalscore
fromYD_KHJZ_XJZLJCBINNERJOIN
(selectcheck_id,zhgl,jfgl,zsb,ttsb,tksb,dlsb,jksb,cssb,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asscore
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))
aspiv
)step
ONYD_KHJZ_XJZLJCB.CHECK_ID=step.CHECK_ID
INNERJOINYD_RYONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
INNERJOINYD_BMONYD_RY.RY_BM_ID=YD_BM.BM_ID
whereYD_BM.BM_TYPE='ywjd'ANDYD_BM.BM_LEVEL>=2AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM_NAME
orderbyYD_BM.BM_ID,YD_BM.BM_NAME
---多个条件：显示YD_BM.BM_ID,YD_BM.BM_NAME,zhglscors,....totalscore
===================ok======================
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(step.zhgl)aszhglscore,
sum(step.jfgl)asjfglscore,
sum(step.zsb)aszsbscore,
sum(step.ttsb)asttsbscore,
sum(step.tksb)astksbscore,
sum(step.dlsb)asdlsbscore,
sum(step.jksb)asjksbscore,
sum(step.cssb)ascssbscore,
sum(step.score)astotalscore
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_KHJZ_XJZLJCBONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectcheck_id,zhgl,jfgl,zsb,ttsb,tksb,dlsb,jksb,cssb,
[zhgl]+[jfgl]+[zsb]+[ttsb]+[tksb]+[dlsb]+[jksb]+[cssb]asscore
from
(
selectcheck_id,itemcode,sum(realvalue)realCount
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)tt
pivot(sum(realCount)foritemcode
in([zhgl],[jfgl],[zsb],[ttsb],[tksb],[dlsb],[jksb],[cssb]))
aspiv
)step
ONYD_KHJZ_XJZLJCB.CHECK_ID=step.CHECK_ID
whereYD_BM.BM_TYPE='ywjd'ANDYD_BM.BM_LEVEL>=2AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM
_NAME
orderbyYD_BM.BM_ID,YD_BM.BM_NAME
---多个条件：显示YD_BM.BM_ID,YD_BM.BM_NAME,zhglscors,....totalscore
==================
selectallstep.check_id1ascheck_id,
allstep.itemscorsascheck_id,
allstep.itemnumsascheck_id,
step0.itemcodeascheck_id,
step0.itemscorsascheck_id,
step0.itemnumsascheck_id
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep,
(selectstep1.check_id1,step1.itemcode,step1.realCountasitemnums,step1.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)step1
)step0
whereallstep.check_id1=step0.check_id1
orderbystep0.check_id1,step0.itemcode
---------------------
selectallstep1.check_id1ascheck_id,
allstep1.itemscorsascheck_id,
allstep1.itemnumsascheck_id,
allstep2.itemcodeascheck_id,
allstep2.itemscorsascheck_id,
allstep2.itemnumsascheck_id
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.realCountasitemnums,group2.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbyitemcode,check_id
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
orderbyallstep2.check_id1,allstep2.itemcode
-------------------------------------
selectallstep1.check_id1ascheck_id,
allstep1.itemscorsasbmitemscors,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemscorsasitemscors,
allstep2.itemnumsasitemnums
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.realCountasitemnums,group2.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,sum(realvalue)realCount,su
m(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id,itemcode
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
orderbyallstep1.check_id1,allstep2.itemcode
------------------------------------------------------
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
allstep.itemcodeasitemcode,
sum(allstep.bmitemscors)asbmitemscors,
sum(allstep.bmitemnums)asbmitemnums,
sum(allstep.itemscors)asitemscors,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_KHJZ_XJZLJCBONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.check_id1ascheck_id,
allstep1.itemscorsasbmitemscors,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemscorsasitemscors,
allstep2.itemnumsasitemnums
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.realCountasitemnums,group2.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id,itemcode
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
orderbyallstep1.check_id1,allstep2.itemcode
)allstep
ONYD_KHJZ_XJZLJCB.CHECK_ID=allstep.CHECK_ID
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode
------------------------------------------------------
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemscors)asbmitemscors,
sum(allstep.bmitemnums)asbmitemnums,
allstep.itemcodeasitemcode,
sum(allstep.itemscors)asitemscors,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_KHJZ_XJZLJCBONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.check_id1ascheck_id,
allstep1.itemscorsasbmitemscors,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemscorsasitemscors,
allstep2.itemnumsasitemnums
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalu
e)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.realCountasitemnums,group2.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id,itemcode
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
)allstep
ONYD_KHJZ_XJZLJCB.CHECK_ID=allstep.CHECK_ID
whereYD_BM.BM_TYPE='ywjd'ANDYD_BM.BM_LEVEL>=2AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode
------------------------------------------------------------------------------------
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemscors)astotalscore,
sum(allstep.bmitemnums)astotalnums,
allstep.itemcodeasitemcode,
allstep.itemnameasitemname,
sum(allstep.itemscors)asitemscors,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_KHJZ_XJZLJCBONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.check_id1ascheck_id,
allstep1.itemscorsasbmitemscors,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemnameasitemname,
allstep2.itemscorsasitemscors,
allstep2.itemnumsasitemnums
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.itemname,group2.realCountasitemnums,group2.realscoreasitemscors
from
(selectcheck_idascheck_id1,itemcode,itemname,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id,itemcode,itemname
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
)allstep
ONYD_KHJZ_XJZLJCB.CHECK_ID=allstep.CHECK_ID
whereYD_BM.BM_TYPE='ywjd'ANDYD_BM.BM_LEVEL>=2AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM_NAME
,allstep.itemcode,allstep.itemname
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode,allstep.itemname
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemscors)astotalscore,
sum(allstep.bmitemnums)astotalnums,
allstep.itemcodeasitemcode,
allstep.itemnameasitemname,
sum(allstep.itemscore)asitemscore,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_KHJZ_XJZLJCBONYD_KHJZ_XJZLJCB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.check_id1ascheck_id,
allstep1.itemscorsasbmitemscors,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemnameasitemname,
allstep2.itemscoreasitemscore,
allstep2.itemnumsasitemnums
from
(selectgroup1.check_id1,group1.realCountasitemnums,group1.realscoreasitemscors
from
(selectcheck_idascheck_id1,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id
)group1
)allstep1,
(selectgroup2.check_id1,group2.itemcode,group2.itemname,group2.realCountasitemnums,group2.realscoreasitemscore
from
(selectcheck_idascheck_id1,itemcode,itemname,sum(realvalue)realCount,sum(maxvalue*realvalue)realscore
fromYD_KHJZ_XJZLJCB_STEPgroupbycheck_id,itemcode,itemname
)group2
)allstep2
whereallstep1.check_id1=allstep2.check_id1
)allstep
ONYD_KHJZ_XJZLJCB.CHECK_ID=allstep.CHECK_ID
whereYD_BM.BM_TYPE='ywjd'ANDYD_BM.BM_LEVEL>=2AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)>='2011-05-01')AND
(CONVERT(varchar(10),YD_KHJZ_XJZLJCB.FILE_TIME,120)<='2011-09-30')
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode,allstep.itemname
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode,allstep.itemname
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemnums)astotalnums,
allstep.itemcodeasitemcode,
allstep.itemnameasitemname,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_JHXS_ZXZBONYD_JHXS_ZXZB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.xs_id1asxs_id,
allstep1.itemnumsasbmitemnums,
allstep2.itemcodeasitemcode,
allstep2.itemnameasitemname,
allstep2.itemnumsasitemnums
from
(selectgroup1.xs_id1,group1.realcountasitemnums
from
(selectxs_idasxs_id1,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id
)group1
)allstep1,
(selectgroup2.xs_id1,group2.itemcode,group2.itemname,group2.realcountasitemnums
from
(selectxs_idasxs_id1,xs_sb_typeasitemcode,xs_sb_nameasitemname,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id,xs_sb_type,xs_sb_name
)group2
)allstep2
whereallstep1.xs_id1=allstep2.xs_id1
)allstep
ONYD_JHXS_ZXZB.xs_id=allstep.xs_id
whereYD_BM.BM_TYPE='ywzx'ANDYD_BM.BM_LEVEL>=2
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode,allstep.itemname
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.itemcode,allstep.itemname
------------------------------------------------------------------------
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemnums)astotalnums,
allstep.zycodeaszycode,
allstep.itemcodeasitemcode,
allstep.itemnameasitemname,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_JHXS_ZXZBONYD_JHXS_ZXZB.FILE_IP=YD_RY.RY_PDA_IP
leftjoin
(selectallstep1.xs_id1asxs_id,
allstep1.itemnumsasbmitemnums,
allstep2.zycodeaszycode,
allstep2.itemcodeasitemcode,
allstep2.itemnameasitemname,
allstep2.itemnumsasitemnums
from
(selectgroup1.xs_id1,group1.realcountasitemnums
from
(selectxs_idasxs_id1,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id
)group1
)allstep1,
(selectgroup2.xs_id1,group2.zycode,group2.itemcode,group2.itemname,group2.realcountasitemnums
from
(selectxs_idasxs_id1,xs_zyaszycode,xs_sb_typeasitemcode,xs_sb_nameasitemname,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id,xs_zy,xs_sb_type,xs_sb_name
)group2
)allstep2
whereallstep1.xs_id1=allstep2.xs_id1
)allstep
ONYD_JHXS_ZXZB.xs_id=allstep.xs_id
whereYD_BM.BM_TYPE='ywzx'ANDYD_BM.BM_LEVEL>=2
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.zycode,allstep.itemcode,allstep.itemname
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,allstep.zycode,allstep.itemcode,allstep.itemname
======================================================================================
selectYD_BM.BM_IDasbmid,
YD_BM.BM_NAMEasbmname,
sum(allstep.bmitemnums)astotalnums,
allstep.zycodeaszycode,
YD_SBZY.ZY_NAME,
allstep.itemcodeasitemcode,
allstep.itemnameasitemname,
sum(allstep.itemnums)asitemnums
fromYD_BM
innerjoinYD_RYONYD_RY.RY_BM_ID=YD_BM.BM_ID
leftjoinYD_JHXS_ZXZBONYD_JHXS_ZXZB.FILE_IP=YD_RY.RY_PDA_IP
left
join
(selectallstep1.xs_id1asxs_id,
allstep1.itemnumsasbmitemnums,
allstep2.zycodeaszycode,
allstep2.itemcodeasitemcode,
allstep2.itemnameasitemname,
allstep2.itemnumsasitemnums
from
(selectgroup1.xs_id1,group1.realcountasitemnums
from
(selectxs_idasxs_id1,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id
)group1
)allstep1,
(selectgroup2.xs_id1,group2.zycode,group2.itemcode,group2.itemname,group2.realcountasitemnums
from
(selectxs_idasxs_id1,xs_zyaszycode,xs_sb_typeasitemcode,xs_sb_nameasitemname,sum(1-xs_result)realcount
fromYD_JHXS_ZXZB_STEPgroupbyxs_id,xs_zy,xs_sb_type,xs_sb_name
)group2
)allstep2
whereallstep1.xs_id1=allstep2.xs_id1
)allstep
ONYD_JHXS_ZXZB.xs_id=allstep.xs_id
leftjoinYD_SBZYONYD_SBZY.PDA_ID=allstep.zycode
whereYD_BM.BM_TYPE='ywzx'ANDYD_BM.BM_LEVEL>=2
groupbyYD_BM.BM_ID,YD_BM.BM_NAME,YD_SBZY.ZY_NAME,allstep.zycode,allstep.itemcode,allstep.itemname
orderbyYD_BM.BM_ID,YD_BM.BM_NAME,YD_SBZY.ZY_NAME,allstep.zycode,allstep.itemcode,allstep.itemname
