单品种的对应表

对单品种的若干理解

            1.    每个在系统中，有一个项目编号。这里是0004000002962。

select * from LSHSXM2016where lshsxm_xmnmin (0004000002962，0004000003266)

2.

这个sql可以查到项目编号。（需要定义公司编码才能准确定位）

    Xmnm          xmbh 

0004000002962        009638            xxx

0004000003266        010245            aaa

0004000003266        008564            sss

0004000002962        007942            rrr

select *from ZWSSFZYE2016WHERE ZWSSFZYE_DWBH='CB064'AND ZWSSFZYE_KMBH LIKE'5001001%'AND ZWSSFZYE_KJND='2016'AND ZWSSFZYE_KJQJ='12'and SUBSTRING(ZWSSFZYE_XMBH,8,6) = '009638'

 

select *from ZWSSFZYE2016WHERE ZWSSFZYE_DWBH='CB064'AND ZWSSFZYE_KMBH LIKE'5001001%'AND ZWSSFZYE_KJND='2016'AND ZWSSFZYE_KJQJ='12'and SUBSTRING(ZWSSFZYE_XMBH,8,6) = '010715'and SUBSTRING(ZWSSFZYE_XMBH,92,6)='000001'

3.一个长长的sql

SELECT CPNM,CPFL,CPMC,DJBH,FYDH,ZYZ,BYC,CBZWGCPB_DJ,CBZWGCPB_JZSL,AVG(CBZWGCPB_DJ* CBZWGCPB_JZSL) ZDJ,SUM(SJCLJE),SUM(ZGCLJE),SUM(SJGFJE),SUM(ZGGFJE),SUM(SJXDJE),SUM(ZGXDJE),SUM(SJZBJE),SUM(ZGZBJE),SUM(SJYSJE),SUM(ZGYSJE),SUM(SJZDJE),SUM(ZGZDJE),SUM(SJCBSSJE),SUM(ZGCBSSJE),SUM(SJBSJE),SUM(ZGBSJE),SUM(SJZXJE),SUM(ZGZXJE),SUM(SJZZQJE),SUM(ZGZZQJE),SUM(SJQTJE),SUM(ZGQTJE)FROM (SELECT XM1.LSHSXM_XM13 AS CPFL, XM1.LSHSXM_XMBH AS CPBH,XM1.LSHSXM_XMNMAS CPNM,XM1.LSHSXM_XMMC AS CPMC, XM1.LSHSXM_XM16 AS DJBH, XM1.LSHSXM_XM17 AS FYDH,XM1.LSHSXM_XM02 ASBYC,XM1.LSHSXM_SJ10 AS ZYZ,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000001'THEN ZWSSFZYE_DQYE ELSE 0 END SJCLJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000016' THEN ZWSSFZYE_DQYE ELSE 0 ENDZGCLJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000002' THEN ZWSSFZYE_DQYE ELSE0 END SJGFJE, CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000017' THENZWSSFZYE_DQYE ELSE 0 END ZGGFJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000012'THEN ZWSSFZYE_DQYE ELSE 0 END SJXDJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000018' THEN ZWSSFZYE_DQYE ELSE 0 ENDZGXDJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000004' THEN ZWSSFZYE_DQYE ELSE0 END SJZBJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000019' THENZWSSFZYE_DQYE ELSE 0 END ZGZBJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000005' THEN ZWSSFZYE_DQYE ELSE 0 ENDSJYSJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000020' THEN ZWSSFZYE_DQYE ELSE0 END ZGYSJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000006' THENZWSSFZYE_DQYE ELSE 0 END SJZDJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000021' THEN ZWSSFZYE_DQYE ELSE 0 ENDZGZDJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000007' THEN ZWSSFZYE_DQYE ELSE0 END SJCBSSJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000022' THENZWSSFZYE_DQYE ELSE 0 END ZGCBSSJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000013' THEN ZWSSFZYE_DQYE ELSE 0 ENDSJBSJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000028' THEN ZWSSFZYE_DQYE ELSE0 END ZGBSJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000012' THENZWSSFZYE_DQYE ELSE 0 END SJZXJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000027' THEN ZWSSFZYE_DQYE ELSE 0 ENDZGZXJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000011' THEN ZWSSFZYE_DQYE ELSE0 END SJZZQJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000026' THENZWSSFZYE_DQYE ELSE 0 END ZGZZQJE,CASE WHENSUBSTRING(ZWSSFZYE_XMBH,92,6)='000008' THEN ZWSSFZYE_DQYE ELSE 0 ENDSJQTJE,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000023' THEN ZWSSFZYE_DQYE ELSE0 END ZGQTJE FROM ZWSSFZYE2016 LEFT JOIN LSHSXM2016 XM1 ONSUBSTRING(ZWSSFZYE_XMBH,8,6)=XM1.LSHSXM_XMBH AND XM1.LSHSXM_DWBH=ZWSSFZYE_DWBHAND XM1.LSHSXM_LBBH='02' LEFT JOIN LSHSXM2016 XM2 ON SUBSTRING(ZWSSFZYE_XMBH,92,6)=XM2.LSHSXM_XMBHAND XM2.LSHSXM_DWBH=ZWSSFZYE_DWBH AND XM2.LSHSXM_LBBH='14' WHEREZWSSFZYE_DWBH='CB064' AND ZWSSFZYE_KMBH LIKE '5001001%' AND ZWSSFZYE_KJND='2016'AND ZWSSFZYE_KJQJ='12')TB1 LEFT JOIN DO_DO_CBZWGCPB ON CBZWGCPB_DWBH='CB064'AND CBZWGCPB_KJND='2016' AND CBZWGCPB_KJQJ='12'AND CBZWGCPB_CPNM=CPNM WHERECPNM IN (SELECT CBZWGCPB_CPNM FROM DO_DO_CBZWGCPB WHERE CBZWGCPB_DWBH='CB064'AND CBZWGCPB_KJND='2016' AND CBZWGCPB_KJQJ='12') GROUP BYCPNM,CPFL,CPBH,CBZWGCPB_DJ,CBZWGCPB_JZSL,CPMC,DJBH,ZYZ,BYC,FYDH ORDER BY CPBH

简化为：

SELECT CPNM,CPMC,BYC,CBZWGCPB_DJ,CBZWGCPB_JZSL,AVG(CBZWGCPB_DJ* CBZWGCPB_JZSL) ZDJ,SUM(SJCLJE) FROM (SELECT XM1.LSHSXM_XM13 AS CPFL, XM1.LSHSXM_XMBH AS CPBH,XM1.LSHSXM_XMNM AS CPNM,XM1.LSHSXM_XMMC AS CPMC, XM1.LSHSXM_XM16 AS DJBH,  XM1.LSHSXM_XM17 AS FYDH,XM1.LSHSXM_XM02 AS BYC,XM1.LSHSXM_SJ10 AS ZYZ,CASE WHEN SUBSTRING(ZWSSFZYE_XMBH,92,6)='000001' THEN ZWSSFZYE_DQYE ELSE 0 END SJCLJE FROM ZWSSFZYE2016 LEFT JOIN LSHSXM2016 XM1 ON SUBSTRING(ZWSSFZYE_XMBH,8,6)=XM1.LSHSXM_XMBH AND XM1.LSHSXM_DWBH=ZWSSFZYE_DWBH AND XM1.LSHSXM_LBBH='02' LEFT JOIN LSHSXM2016 XM2 ON SUBSTRING(ZWSSFZYE_XMBH,92,6)=XM2.LSHSXM_XMBH AND XM2.LSHSXM_DWBH=ZWSSFZYE_DWBH AND XM2.LSHSXM_LBBH='14' WHERE ZWSSFZYE_DWBH='CB064' AND ZWSSFZYE_KMBH LIKE '5001001%' AND ZWSSFZYE_KJND='2016' AND ZWSSFZYE_KJQJ='12')TB1 LEFT JOIN DO_DO_CBZWGCPB ON CBZWGCPB_DWBH='CB064' AND CBZWGCPB_KJND='2016' AND CBZWGCPB_KJQJ='12'AND CBZWGCPB_CPNM=CPNM WHERE CPNM IN (SELECT CBZWGCPB_CPNM FROM DO_DO_CBZWGCPB WHERE CBZWGCPB_DWBH='CB064' AND CBZWGCPB_KJND='2016' AND CBZWGCPB_KJQJ='12') GROUP BY CPNM,CPFL,CPBH,CBZWGCPB_DJ,CBZWGCPB_JZSL,CPMC,DJBH,ZYZ,BYC,FYDH ORDER BY CPBH