XLS《公示》

"","项目名称","推荐将种","推荐号","主要完成单位","主要完成人","推荐单位" "1","机器人用新型精密减速器多结构创新及系列产品制造技术","进步奖:技术开发","J180300027","恒丰泰精密机械股份有限公司","孔向东,叶胜康,周仕臣,金良华","温州市人民政府","http://pm.

zjsti.gov.cn/lib/generic/web/viewer.html?file=J180300027" "3","CNiM-TM气体涡轮流量计","进步奖:技术开发","J180309003","浙江苍南仪表集团股份有限公司","章圣意,林景殿,肖云巩,林中柱,林新霞,姚海滨,陈祖寿","苍南县人民政府","http://pm.zjsti.gov.cn/lib/generic/web/viewer.html?file=J180309003" "4","基于PHBV/PLA的可降解竹基复合材料关键技术与应用","进步奖:技术开发","J185900004","浙江省林业科学研究院,浙江大学,中国科学院宁波材料技术与工程研究所,绍兴永N新材料有限公司,杭州品库工艺品有限公司","李琴,盛奎川,陈鹏,袁少飞,王洪艳,张建,徐康,逯柳,翁甫金","浙江省林业厅","http://pm.zjsti.gov.cn/lib/generic/web/viewer.html?file=J185900004" "selectt.id,pl.nameas奖项类别,t.prijectbasenameas项目名称,t.jllbas推荐将种,t.tjjldjas推荐奖励等级,t.recommendnumberas推荐号,eee.result2as主要完成单位,ppp.result1as主要完成人,t.recommendnameas推荐单位,concat(concat(xk.firstleveldiscipline,'、'),xk.one)as第一次序学科分类,concat(concat(xk.subordinatediscipline,'、'),xk.two)as第一次序一级学科,concat(concat(xk.thirdleveldiscipline,'、'),xk.three)as第一次序二级级学科,concat(concat(concat(concat(xk2.one,','),xk2.two),','),xk2.three)as第二次序学科,t.minicurrentstateas状态,t.economicindustryas所属国民分类,t.topicas主题词,t.registrationnumberas成果登记号,t.directbenefitas直接经济效益,t.indirectbenefitas间接经济效益frompms_awardtinnerjoinpms_planprojectbatchplont.batchid=pl.idandpl.htmodelname='usingNow'leftjoin(selects.id,max(s.result)result1from(selectt.id,wmsys.wm_concat(py.name)over(partitionbyt.idorderbypy.seq)resultfrompms_awardtinnerjoinpms_planprojectbatchplont.batchid=pl.idandpl.htmodelname='usingNow'leftjoinpms_projectpartypyonpy.jxid=t.idandpy.ptype='xmwcr')sgroupbys.id)ppponppp.id=t.idleftjoin--完成人拼接(selects.id,max(s.result)result2from(selectt.id,wmsys.wm_concat(py.organizationname)over(partitionbyt.idorderbypy.seq)resultfrompms_awardtinnerjoinpms_planprojectbatchplont.batchid=pl.idandpl.htmodelname='usingNow'leftjoinpms_enterpriseorganizationpyonpy.jxid=t.idandpy.ISMAINENTERPRISE='xmwcdw')sgroupbys.id)eeeoneee.id=t.idleftjoin--单位拼接(selectt.id,z.firstleveldiscipline,z.subordinatediscipline,z.thirdleveldiscipline,(selecttt.namefrompms_subject_typettwherez.firstleveldiscipline=tt.codeandtt.type='xm')asone,(selecttt.namefrompms_subject_typettwherez.subordinatediscipline=tt.codeandtt.type='xm')astwo,(selecttt.namefrompms_subject_typettwherez.thirdleveldiscipline=tt.codeandtt.type='xm')asthreefrompms_awardtinnerjoinpms_planprojectbatchplont.batchid=pl.idandpl.htmodelname='usingNow'innerjoinzjk_ryjbxxxkflzonz.person_id=t.idandz.seq=0)xkonxk.id=t.id--第一次序学科leftjoin(selectt.id,(selecttt.namefrompms_subject_typettwherez.firstleveldiscipline=tt.codeandtt.type='xm')asone,(selecttt.namefrompms_subject_typettwherez.subordinatediscipline=tt.codeandtt.type='xm')astwo,(selecttt.namefrompms_subject_typettwherez.thirdleveldiscipline=tt.codeandtt.type='xm')asthreefrompms_awardtinnerjoinpms_planprojectbatchplont.batchid=pl.idandpl.htmodelname='usingNow'innerjoinzjk_ryjbxxxkflzonz.person_id=t.idandz.seq=1)xk2onxk2.id=t.id--第二次序学科innerjoinpms_recommendtargetpronpr.enterpriseid=t.recommendidwheret.recommendnumberin('J180300027','J180305003','J180309003','J185900004')orderbypr.companycode,t.recommendnumber,pl.name,t.minicurrentstateid,t.minicurrentstate"