鄂冶

1.交接样信息表

SELECT [t1].[jiaoysj],[t1].[jiaoysl],[t2].[saomsj_jiaoy],[t3].[bianm],[t4].[name],[t5].[username] AS jiaoyry ,
[t6].[username] AS jieyry ,[t7].[mingc],[t9].[mingc] AS wullx, 
[t10].[mingc] AS wul,[t11].[mingc] AS wulpz,[t12].[name] AS zhuzjg FROM  [yp_jiaoj] AS  t1
LEFT JOIN [yp_jiaoj_mingx]  AS [t2] ON [t2].[jiaoj_fk]=[t1].[jiaoj_id]
LEFT JOIN [bm_bianm]        AS [t3] ON [t3].[bianm_id]=[t2].[bianm_fk]
LEFT JOIN [sys_organizs]    AS [t4] ON [t4].[organiz_id]=[t1].[organiz_fk]
LEFT JOIN [sys_admin_users] AS [t5] ON [t5].[admin_user_id]=[t1].[admin_user_fk_jiaoy]
LEFT JOIN [sys_admin_users] AS [t6] ON [t6].[admin_user_id]=[t1].[admin_user_fk_jiey]
LEFT JOIN [jc_yangplx]      AS [t7] ON [t7].[yangplx_id]=[t3].[yangplx_fk]
LEFT JOIN [jl_caig_pic]     AS [t8] ON [t8].[caig_pic_id]=[t3].[pic_fk]
LEFT JOIN [jc_wul]          AS [t9] ON [t9].[wul_id]=[t8].[wul_fk_1]
LEFT JOIN [jc_wul]          AS [t10]ON [t10].[wul_id]=[t8].[wul_fk_2]
LEFT JOIN [jc_wul]          AS [t11]ON [t11].[wul_id]=[t8].[wul_fk_3]
LEFT JOIN [sys_organizs]    AS [t12]ON [t12].[organiz_id]=[t8].[organiz_fk]

2.新

SELECT [t1].[jiaoysj],[t1].[jiaoysl],[t2].[saomsj_jiaoy],[t3].[bianm] AS [bianm_bianm],[t4].[name] AS [organiz_name_zhil],
[t5].[username] AS [admin_user_username_jiaoy] ,
[t6].[username] AS [admin_user_username_jiey] ,[t7].[mingc] AS [jc_yangplx_mingc],[t9].[mingc] AS [jc_wul_mingc1],
[t10].[mingc] AS [jc_wul_mingc2],[t11].[mingc] AS [jc_wul_mingc3],
[t13].[guig] AS [jc_wul_guig4] ,[t12].[name] AS [sys_organizs_name] FROM  [yp_jiaoj] AS  t1
LEFT JOIN [yp_jiaoj_mingx]  AS [t2] ON [t2].[jiaoj_fk]=[t1].[jiaoj_id]
LEFT JOIN [bm_bianm]        AS [t3] ON [t3].[bianm_id]=[t2].[bianm_fk]
LEFT JOIN [sys_organizs]    AS [t4] ON [t4].[organiz_id]=[t1].[organiz_fk]
LEFT JOIN [sys_admin_users] AS [t5] ON [t5].[admin_user_id]=[t1].[admin_user_fk_jiaoy]
LEFT JOIN [sys_admin_users] AS [t6] ON [t6].[admin_user_id]=[t1].[admin_user_fk_jiey]
LEFT JOIN [jc_yangplx]      AS [t7] ON [t7].[yangplx_id]=[t3].[yangplx_fk]
LEFT JOIN [jl_caig_pic]     AS [t8] ON [t8].[caig_pic_id]=[t3].[pic_fk]
LEFT JOIN [jc_wul]          AS [t9] ON [t9].[wul_id]=[t8].[wul_fk_1]
LEFT JOIN [jc_wul]          AS [t10]ON [t10].[wul_id]=[t8].[wul_fk_2]
LEFT JOIN [jc_wul]          AS [t11]ON [t11].[wul_id]=[t8].[wul_fk_3]
LEFT JOIN [jc_wul]          AS [t13]ON [t13].[wul_id]=[t8].[wul_fk_4]
LEFT JOIN [sys_organizs]    AS [t12]ON [t12].[organiz_id]=[t8].[organiz_fk]

3.最终版

SELECT [t1].[jiaoysj],[t1].[jiaoysl],[t2].[saomsj_jiaoy],[t3].[bianm] AS [bianm_bianm],[t4].[name] AS [organiz_name_jiaoj],
[t5].[username] AS [admin_user_username_jiaoy] ,
[t6].[username] AS [admin_user_username_jiey] ,[t7].[mingc] AS [yangplx_mingc],[t9].[mingc] AS [wul_mingc_1],
[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3],
[t13].[guig] AS [wul_guig_4],[t13].[xingh] AS [wul_xingh_4] ,[t12].[name] AS [organiz_name] FROM  [yp_jiaoj] AS [t1]
LEFT JOIN [yp_jiaoj_mingx]  AS [t2] ON [t2].[jiaoj_fk]=[t1].[jiaoj_id]
LEFT JOIN [bm_bianm]        AS [t3] ON [t3].[bianm_id]=[t2].[bianm_fk]
LEFT JOIN [sys_organizs]    AS [t4] ON [t4].[organiz_id]=[t1].[organiz_fk]
LEFT JOIN [sys_admin_users] AS [t5] ON [t5].[admin_user_id]=[t1].[admin_user_fk_jiaoy]
LEFT JOIN [sys_admin_users] AS [t6] ON [t6].[admin_user_id]=[t1].[admin_user_fk_jiey]
LEFT JOIN [jc_yangplx]      AS [t7] ON [t7].[yangplx_id]=[t3].[yangplx_fk]
LEFT JOIN [jl_caig_pic]     AS [t8] ON [t8].[caig_pic_id]=[t3].[pic_fk]
LEFT JOIN [jc_wul]          AS [t9] ON [t9].[wul_id]=[t8].[wul_fk_1]
LEFT JOIN [jc_wul]          AS [t10]ON [t10].[wul_id]=[t8].[wul_fk_2]
LEFT JOIN [jc_wul]          AS [t11]ON [t11].[wul_id]=[t8].[wul_fk_3]
LEFT JOIN [jc_wul]          AS [t13]ON [t13].[wul_id]=[t8].[wul_fk_4]
LEFT JOIN [sys_organizs]    AS [t12]ON [t12].[organiz_id]=[t8].[organiz_fk]

4.原料采购一二次编码对应表原版

SELECT [caig_pic_id],[picsj],[organiz_name],[wul_mingc_2],[wul_mingc_3],[2aa56991-02d3-4e0c-81f4-81266ac55e25] AS [bianm_1],[d3f0739c-6426-4fd1-816f-fdf461811d97] AS [bianm_2] FROM (SELECT [t1].[caig_pic_id],[t1].[picsj],[t2].[yangplx_fk],[t2].[bianm],[t3].[name] AS [organiz_name],[t4].[mingc] AS [wul_mingc_2],[t5].[mingc] AS [wul_mingc_3] FROM [jl_caig_pic] AS [t1] LEFT JOIN [bm_bianm] AS [t2] ON [t1].[caig_pic_id] = [t2].[pic_fk] LEFT JOIN [sys_organizs] AS [t3] ON [t1].[organiz_fk] = [t3].[organiz_id] LEFT JOIN [jc_wul] AS [t4] ON [t1].[wul_fk_2]=[t4].[wul_id] LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_3] = [t5].[wul_id] WHERE [t2].[yangplx_fk] IN ('2aa56991-02d3-4e0c-81f4-81266ac55e25','d3f0739c-6426-4fd1-816f-fdf461811d97')) AS [tab1]  PIVOT(max(bianm) FOR [yangplx_fk] IN ([2aa56991-02d3-4e0c-81f4-81266ac55e25],[d3f0739c-6426-4fd1-816f-fdf461811d97]) ) [tab2]

5. 编码问题

SELECT   TOP (200) bianm_id, bianmlx_fk, pic_fk, yangplx_fk, bianm, wul_yangplx_fk, bianm_fk, fenxlx, bianm_fk_guanl, datatype, 
                interface_id, addtime, edittime
FROM      bm_bianm
WHERE   (pic_fk IN
                    (SELECT   pic_fk
                     FROM      bm_bianm AS bm_bianm_1
                     WHERE   (bianm = '081711289107')))

SELECT   TOP (200) bianm_id, bianmlx_fk, pic_fk, yangplx_fk, bianm, wul_yangplx_fk, bianm_fk, fenxlx, bianm_fk_guanl, datatype, 
                interface_id, addtime, edittime
FROM      bm_bianm
WHERE   (pic_fk IN
                    (SELECT   pic_fk
                     FROM      bm_bianm AS bm_bianm_1
                     WHERE   (bianm = '二次编码')))

6. 电煤旧的原始化验记录

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj] FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t4].[pic_fk]=[t5].[caig_pic_id] 
 WHERE [t5].[wul_fk_2]='74cca884-931b-4b48-b2a0-22ce97fbac65' AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([Mt],[Mad],[Aad],[Vad],[St,ad]
 ,[Qgr,ad],[Qnet,ar],[Had],[CRC]))a

7. 简易运输明细

SELECT [t1].[maozsj],[t1].[pizsj],[t1].[maoz],[t1].[piz],[t1].[jingz]
 ,[t2].[cheph] AS [qic_cheph],[t3].[mingc] AS [wul_mingc_1],[t4].[mingc] AS [wul_mingc_2]
 ,[t5].[mingc] AS [wul_mingc_3],[t6].[guig] AS [wul_guig_4],[t6].[xingh] AS [wul_xingh_4]
 ,[t7].[mingc] AS [liaoc_mingc_1_zhuanc],[t8].[mingc] AS [liaoc_mingc_2_zhuanc]
 ,[t9].[mingc] AS [liaoc_mingc_1_zhuanr],[t10].[mingc] AS [liaoc_mingc_2_zhuanr],[t11].[name] AS [organiz_name]
 ,[t12].[contents] AS [public_basic_detail_contents_liuclx],[t13].[mingc] AS [keh_mingc]
 ,[t14].[mingc] AS [gongys_mingc_1],[t15].[mingc] AS [gongys_mingc_2] 
 FROM [jl_qit_mingx] AS [t1] 
 LEFT JOIN [jc_qic] AS [t2] ON [t1].[qic_fk]=[t2].[qic_id] 
 LEFT JOIN [jc_wul] AS [t3] ON [t1].[wul_fk_1]=[t3].[wul_id] 
 LEFT JOIN [jc_wul] AS [t4] ON [t1].[wul_fk_2]=[t4].[wul_id] 
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_3]=[t5].[wul_id] 
 LEFT JOIN [jc_wul] AS [t6] ON [t1].[wul_fk_4]=[t6].[wul_id] 
 LEFT JOIN [jc_liaoc] AS [t7] ON [t1].[liaoc_fk_1_zhuanc]=[t7].[liaoc_id] 
 LEFT JOIN [jc_liaoc] AS [t8] ON [t1].[liaoc_fk_2_zhuanc]=[t8].[liaoc_id] 
 LEFT JOIN [jc_liaoc] AS [t9] ON [t1].[liaoc_fk_1_zhuanr]=[t9].[liaoc_id] 
 LEFT JOIN [jc_liaoc] AS [t10] ON [t1].[liaoc_fk_2_zhuanr]=[t10].[liaoc_id] 
 LEFT JOIN [sys_organizs] AS [t11] ON [t1].[organiz_fk]=[t11].[organiz_id] 
 LEFT JOIN [sys_public_basic_details] AS [t12] ON [t1].[liuclx]=[t12].[public_basic_detail_id] 
 LEFT JOIN [jc_keh] AS [t13] ON [t1].[keh_fk]=[t13].[keh_id] 
 LEFT JOIN [jc_gongys] AS [t14] ON [t1].[gongys_fk_1]=[t14].[gongys_id] 
 LEFT JOIN [jc_gongys] AS [t15] ON [t1].[gongys_fk_2]=[t15].[gongys_id] 
 WHERE [t1].[organiz_fk]='00000000-0000-0000-0000-000000000006'

8. 实时查询

SELECT [picsj],SUM([ches]) AS [ches],SUM([maoz]) AS [maoz],SUM([piz]) AS [piz],
 SUM([kouz]) AS [kouz],SUM([jingz]) AS [jingz],SUM([maoz_gys]) AS [maoz_gys],
 SUM([piz_gys]) AS [piz_gys],SUM([kouz_gys]) AS [kouz_gys],SUM([jingz_gys]) AS [jingz_gys],
 [organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2] FROM (
SELECT [t1].[picsj],[t1].[gongys_fk_1],[t1].[gongys_fk_2],(SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a1] WHERE [a1].[caig_pic_fk]=[t1].[caig_pic_id] AND [a1].[jingz]>0) [ches],[t1].[maoz],[t1].[piz],[t1].[kouz],[t1].[jingz]
 ,[t1].[maoz_gys],[t1].[piz_gys],[t1].[kouz_gys],[t1].[jingz_gys],[t2].[name] AS [organiz_name]
 ,[t3].[mingc] AS [gongys_mingc_1],[t4].[mingc] AS [gongys_mingc_2]
 ,[t5].[mingc] AS [wul_mingc_1],[t6].[mingc] AS [wul_mingc_2],[t7].[mingc] AS [wul_mingc_3]
 ,[t8].[mingc] AS [wul_guig_4],[t8].[xingh] AS [wul_xingh_4]
 FROM [jl_caig_pic] AS [t1] 
 LEFT JOIN [sys_organizs] AS [t2] ON [t1].[organiz_fk]=[t2].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t3] ON [t1].[gongys_fk_1]=[t3].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t4] ON [t1].[gongys_fk_2]=[t4].[gongys_id]
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_1]=[t5].[wul_id]
 LEFT JOIN [jc_wul] AS [t6] ON [t1].[wul_fk_2]=[t6].[wul_id]
 LEFT JOIN [jc_wul] AS [t7] ON [t1].[wul_fk_3]=[t7].[wul_id]
 LEFT JOIN [jc_wul] AS [t8] ON [t1].[wul_fk_4]=[t8].[wul_id]
 WHERE [organiz_fk]='00000000-0000-0000-0000-000000000006') TAB
 GROUP BY [picsj],[organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2]

9.实时查询原版

SELECT [picsj],SUM([ches]) AS [ches],SUM([maoz]) AS [maoz],SUM([piz]) AS [piz],
  SUM([kouz]) AS [kouz],SUM([jingz]) AS [jingz],SUM([maoz_gys]) AS [maoz_gys],
  SUM([piz_gys]) AS [piz_gys],SUM([kouz_gys]) AS [kouz_gys],SUM([jingz_gys]) AS [jingz_gys],
  [organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
  [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2], [wul_fk_3] FROM (
 SELECT [t1].[picsj],[t1].[gongys_fk_1],[t1].[gongys_fk_2],[t1].[wul_fk_3],
 (SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a1] WHERE [a1].[caig_pic_fk]=[t1].[caig_pic_id] AND [a1].[jingz]>0)
  [ches],[t1].[maoz],[t1].[piz],[t1].[kouz],[t1].[jingz]
  ,[t1].[maoz_gys],[t1].[piz_gys],[t1].[kouz_gys],[t1].[jingz_gys],[t2].[name] AS [organiz_name]
  ,[t3].[mingc] AS [gongys_mingc_1],[t4].[mingc] AS [gongys_mingc_2]
  ,[t5].[mingc] AS [wul_mingc_1],[t6].[mingc] AS [wul_mingc_2],[t7].[mingc] AS [wul_mingc_3]
  ,[t8].[mingc] AS [wul_guig_4],[t8].[xingh] AS [wul_xingh_4]
  FROM [jl_caig_pic] AS [t1] 
  LEFT JOIN [sys_organizs] AS [t2] ON [t1].[organiz_fk]=[t2].[organiz_id]
  LEFT JOIN [jc_gongys] AS [t3] ON [t1].[gongys_fk_1]=[t3].[gongys_id]
  LEFT JOIN [jc_gongys] AS [t4] ON [t1].[gongys_fk_2]=[t4].[gongys_id]
  LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_1]=[t5].[wul_id]
  LEFT JOIN [jc_wul] AS [t6] ON [t1].[wul_fk_2]=[t6].[wul_id]
  LEFT JOIN [jc_wul] AS [t7] ON [t1].[wul_fk_3]=[t7].[wul_id]
  LEFT JOIN [jc_wul] AS [t8] ON [t1].[wul_fk_4]=[t8].[wul_id]
  WHERE [organiz_fk]='00000000-0000-0000-0000-000000000008' ) TAB
  GROUP BY [picsj],[organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
  [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2],[wul_fk_3]

11. 电石二公司原料采购一二次编码对应表原版：

SELECT* FROM (SELECT [caig_pic_id],[picsj],[organiz_name],[wul_mingc_2],[wul_mingc_3],[2aa56991-02d3-4e0c-81f4-81266ac55e25] AS [bianm_1],[d3f0739c-6426-4fd1-816f-fdf461811d97] AS [bianm_2] FROM (SELECT [t1].[caig_pic_id],[t1].[picsj],[t2].[yangplx_fk],[t2].[bianm],[t3].[name] AS [organiz_name],[t4].[mingc] AS [wul_mingc_2],[t5].[mingc] AS [wul_mingc_3] FROM [jl_caig_pic] AS [t1]
 LEFT JOIN [bm_bianm] AS [t2] ON [t1].[caig_pic_id] = [t2].[pic_fk] 
 LEFT JOIN [sys_organizs] AS [t3] ON [t1].[organiz_fk] = [t3].[organiz_id]
 LEFT JOIN [jc_wul] AS [t4] ON [t1].[wul_fk_2]=[t4].[wul_id]
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_3] = [t5].[wul_id]
 WHERE [t2].[yangplx_fk] IN ('2aa56991-02d3-4e0c-81f4-81266ac55e25','d3f0739c-6426-4fd1-816f-fdf461811d97')) AS [tab1]
 PIVOT(max(bianm) FOR [yangplx_fk] IN ([2aa56991-02d3-4e0c-81f4-81266ac55e25],[d3f0739c-6426-4fd1-816f-fdf461811d97]) ) [tab2])[tab3]
  WHERE [tab3].organiz_name ='电石二公司'

10.批量修改物料化验审核状态：

update zl_bianm_zhil set  shujlx=0 where bianm_zhil_id in ( select bmzl.bianm_zhil_id from zl_bianm_zhil bmzl
left join bm_bianm bm on bmzl.bianm_fk=bm.bianm_id
left join jl_caig_pic pc on bm.pic_fk=pc.caig_pic_id 
left join jc_wul wl on pc.wul_fk_2=wl.wul_id
where wl.mingc='兰炭' and  (bmzl.shangbsj >= '2017-12-29')  AND (shangbsj < '2017-12-30')AND (bmzl.organiz_fk = 'cf65bf14-f519-4a75-a707-5c4d8d02e87b'))

12.电石二公司原料采购一二次编码对应表新版

SELECT [t1].[caig_pic_id],[t1].[picsj],[t2].[yangplx_fk],[t2].[bianm] AS [bianm_1],
(SELECT MAX(bianm) FROM [bm_bianm] AS [a1] WHERE [a1].[bianm_fk]=[t2].[bianm_id] 
AND [a1].[yangplx_fk]='d3f0739c-6426-4fd1-816f-fdf461811d97') AS [bianm_2],
[t3].[name] AS [organiz_name],[t4].[mingc] AS [wul_mingc_2],[t5].[mingc] AS [wul_mingc_3] 
FROM [jl_caig_pic] AS [t1] 
 LEFT JOIN [bm_bianm] AS [t2] ON [t1].[caig_pic_id]=[t2].[pic_fk]
 LEFT JOIN [sys_organizs] AS [t3] ON [t1].[organiz_fk] = [t3].[organiz_id]
 LEFT JOIN [jc_wul] AS [t4] ON [t1].[wul_fk_2]=[t4].[wul_id]
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_3] = [t5].[wul_id]
 WHERE [t2].[bianmlx_fk]='6a980f28-405f-4f32-a11e-96f5f9c8f9a7'
  AND [t1].[organiz_fk]='4840d92b-48dd-4a4e-963f-823c94fa240f'

13. 兰炭质管部原版sq：

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj]
 ,[t6].[name] AS [organiz_name],[t7].[mingc] AS [gongys_mingc_1],[t8].[mingc] AS [gongys_mingc_2]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[mingc] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t4].[pic_fk]=[t5].[caig_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t7] ON [t5].[gongys_fk_1]=[t7].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t8] ON [t5].[gongys_fk_2]=[t8].[gongys_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]
 WHERE [t5].[wul_fk_2]='cdfe434f-b88a-4697-9aa0-2ca126fb0342' AND [t6].[name] !='电石一公司' AND [t6].[name] !='电石二公司' AND [t6].[name] !='同源化工'  AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([Mt],[Mad],[Aad],[Vad],[Fc,ad],[P]))a

14.采购供应商电力三原版

SELECT [picsj],SUM([ches]) AS [ches], SUM([chesw]) AS [chesw],SUM([maoz]) AS [maoz],SUM([piz]) AS [piz],
 SUM([kouz]) AS [kouz],SUM([jingz]) AS [jingz],SUM([maoz_gys]) AS [maoz_gys],
 SUM([piz_gys]) AS [piz_gys],SUM([kouz_gys]) AS [kouz_gys],SUM([jingz_gys]) AS [jingz_gys],
 [organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2], [wul_fk_3] FROM (
SELECT [t1].[picsj],[t1].[gongys_fk_1],[t1].[gongys_fk_2],[t1].[wul_fk_3],
(SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a1] WHERE [a1].[caig_pic_fk]=[t1].[caig_pic_id] AND [a1].[jingz]>0)
 [ches],(SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a2] WHERE [a2].[caig_pic_fk]=[t1].[caig_pic_id] AND [a2].[piz]=0)
 [chesw],[t1].[maoz],[t1].[piz],[t1].[kouz],[t1].[jingz]
 ,[t1].[maoz_gys],[t1].[piz_gys],[t1].[kouz_gys],[t1].[jingz_gys],[t2].[name] AS [organiz_name]
 ,[t3].[mingc] AS [gongys_mingc_1],[t4].[mingc] AS [gongys_mingc_2]
 ,[t5].[mingc] AS [wul_mingc_1],[t6].[mingc] AS [wul_mingc_2],[t7].[mingc] AS [wul_mingc_3]
 ,[t8].[mingc] AS [wul_guig_4],[t8].[xingh] AS [wul_xingh_4]
 FROM [jl_caig_pic] AS [t1] 
 LEFT JOIN [sys_organizs] AS [t2] ON [t1].[organiz_fk]=[t2].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t3] ON [t1].[gongys_fk_1]=[t3].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t4] ON [t1].[gongys_fk_2]=[t4].[gongys_id]
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_1]=[t5].[wul_id]
 LEFT JOIN [jc_wul] AS [t6] ON [t1].[wul_fk_2]=[t6].[wul_id]
 LEFT JOIN [jc_wul] AS [t7] ON [t1].[wul_fk_3]=[t7].[wul_id]
 LEFT JOIN [jc_wul] AS [t8] ON [t1].[wul_fk_4]=[t8].[wul_id]
 WHERE [organiz_fk]='00000000-0000-0000-0000-000000000007' AND jingz > 0 ) TAB
 GROUP BY [picsj],[organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2],[wul_fk_3]

15新采购供应商

SELECT [picsj],SUM([ches]) AS [ches], SUM([chesw]) AS [chesw],SUM([maoz]) AS [maoz],SUM([piz]) AS [piz],
 SUM([kouz]) AS [kouz],SUM([jingz]) AS [jingz],SUM([maoz_gys]) AS [maoz_gys],
 SUM([piz_gys]) AS [piz_gys],SUM([kouz_gys]) AS [kouz_gys],SUM([jingz_gys]) AS [jingz_gys],
 [organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2], [wul_fk_3],yunsdw_mingc FROM (
SELECT [t1].[picsj],[t1].[gongys_fk_1],[t1].[gongys_fk_2],[t1].[wul_fk_3],
(SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a1] WHERE [a1].[caig_pic_fk]=[t1].[caig_pic_id] AND [a1].[jingz]>0)
 [ches],(SELECT COUNT(1) FROM [jl_caig_pic_mingx] AS [a2] WHERE [a2].[caig_pic_fk]=[t1].[caig_pic_id] AND [a2].[piz]=0)
 [chesw],[t1].[maoz],[t1].[piz],[t1].[kouz],[t1].[jingz]
 ,[t1].[maoz_gys],[t1].[piz_gys],[t1].[kouz_gys],[t1].[jingz_gys],[t2].[name] AS [organiz_name]
 ,[t3].[mingc] AS [gongys_mingc_1],[t4].[mingc] AS [gongys_mingc_2]
 ,[t5].[mingc] AS [wul_mingc_1],[t6].[mingc] AS [wul_mingc_2],[t7].[mingc] AS [wul_mingc_3]
 ,[t8].[mingc] AS [wul_guig_4],[t8].[xingh] AS [wul_xingh_4],[t10].[mingc] AS [yunsdw_mingc]
 FROM [jl_caig_pic] AS [t1] 
 LEFT JOIN [sys_organizs] AS [t2] ON [t1].[organiz_fk]=[t2].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t3] ON [t1].[gongys_fk_1]=[t3].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t4] ON [t1].[gongys_fk_2]=[t4].[gongys_id]
 LEFT JOIN [jc_wul] AS [t5] ON [t1].[wul_fk_1]=[t5].[wul_id]
 LEFT JOIN [jc_wul] AS [t6] ON [t1].[wul_fk_2]=[t6].[wul_id]
 LEFT JOIN [jc_wul] AS [t7] ON [t1].[wul_fk_3]=[t7].[wul_id]
 LEFT JOIN [jc_wul] AS [t8] ON [t1].[wul_fk_4]=[t8].[wul_id]

 LEFT JOIN [jl_caig_pic_mingx] AS [t9] ON [t9].[caig_pic_fk]=[t1].[caig_pic_id]
 LEFT JOIN [jc_yunsdw] AS [t10] ON [t9].[yunsdw_fk]=[t10].[yunsdw_id]

 WHERE [organiz_fk]='00000000-0000-0000-0000-000000000008' AND [t1].[jingz] > 0 ) TAB
 GROUP BY [picsj],[organiz_name],[gongys_mingc_1],[gongys_mingc_2],[wul_mingc_1],[wul_mingc_2],[wul_mingc_3],
 [wul_guig_4],[wul_xingh_4],[gongys_fk_1],[gongys_fk_2],[wul_fk_3],yunsdw_mingc

16.新台账球团矿

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t4].[bianm],[t5].[picsj]
 ,[t6].[name] AS [organiz_name],[t7].[mingc] AS [gongys_mingc_1],[t8].[mingc] AS [gongys_mingc_2]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[mingc] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_pic_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_pic_zhil] AS [t3] ON [t1].[pic_zhil_fk]=[t3].[pic_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[pic_fk]=[t4].[pic_fk]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t3].[pic_fk]=[t5].[caig_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t7] ON [t5].[gongys_fk_1]=[t7].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t8] ON [t5].[gongys_fk_2]=[t8].[gongys_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]

 WHERE [t5].[wul_fk_2]='7781da38-81a5-40a5-ac2f-b541eab061e8' AND t4.yangplx_fk='d3f0739c-6426-4fd1-816f-fdf461811d97' ) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([TFe],[Fe],[AL2O3],[CaO],[TiO2],[P],[MgO],[Mn]))a

17.旧版球团矿台账

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj]
 ,[t6].[name] AS [organiz_name],[t7].[mingc] AS [gongys_mingc_1],[t8].[mingc] AS [gongys_mingc_2]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[mingc] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t4].[pic_fk]=[t5].[caig_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t7] ON [t5].[gongys_fk_1]=[t7].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t8] ON [t5].[gongys_fk_2]=[t8].[gongys_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]
 WHERE [t5].[wul_fk_2]='7781da38-81a5-40a5-ac2f-b541eab061e8' AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([TFe],[Fe],[AL2O3],[CaO],[TiO2],[P],[MgO],[Mn]))a

18.化验台账-电煤旧

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj],[t5].[jingz],[t5].[ches]
 ,[t6].[name] AS [organiz_name],[t7].[mingc] AS [gongys_mingc_1],[t8].[mingc] AS [gongys_mingc_2]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[mingc] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t4].[pic_fk]=[t5].[caig_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t7] ON [t5].[gongys_fk_1]=[t7].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t8] ON [t5].[gongys_fk_2]=[t8].[gongys_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]
 WHERE [t5].[wul_fk_2]='74cca884-931b-4b48-b2a0-22ce97fbac65' AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([Mt],[Mad],[Aad],[Vad],[St,ad]
 ,[kCal],[kCalad],[Had],[CRC]))a

19.兰炭旧版台账

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj]
 ,[t6].[name] AS [organiz_name],[t7].[mingc] AS [gongys_mingc_1],[t8].[mingc] AS [gongys_mingc_2]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[mingc] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_caig_pic] AS [t5] ON [t4].[pic_fk]=[t5].[caig_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_gongys] AS [t7] ON [t5].[gongys_fk_1]=[t7].[gongys_id]
 LEFT JOIN [jc_gongys] AS [t8] ON [t5].[gongys_fk_2]=[t8].[gongys_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]
 WHERE [t5].[wul_fk_2]='cdfe434f-b88a-4697-9aa0-2ca126fb0342' AND [t6].[name] !='电石一公司' AND [t6].[name] !='电石二公司' AND [t6].[name] !='同源化工'  AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([Mt],[Mad],[Aad],[Vad],[Fc,ad],[P]))a

21 key

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDaSgWobB3GDXiWUnbU90QF4pMF69pDrWgeT4nXpPR8sGkBRSQ3LsTpCwSXEODboHf3MlKdskfmaQ2v1qgpMJ7Z8zF/h96JTYItg/satP/xNPQt7FD6BT/4Sft0RoMqLkbFx8qrkHhezYT1Yicb8MPEhSQbhlQg4VsSXPuQOxqkEoiBMxdaDAYPVjnb2Y23gGxVTn/UD0igWmgsFPEGPs7gVC+nm1o3NQqbwVc2T24No+npirioprTzB7ES+B/gO0JFi9y3EoAbmtxEaZDqg91a3ptKvEiDuHEoZsmTUP3DYCpPkBX4HGoKEmpTOz3Arokm6hJPJWeHg2N69JIUj4fR kaolar@outlook.com

20.普通硅铁台账旧

SELECT * FROM ( 
 SELECT [t1].[zhi],[t2].[daim],[t3].[shangbsj],[t4].[bianm],[t5].[picsj]
 ,[t5].[picbh],[t6].[name] AS [organiz_name]
 ,[t9].[mingc] AS [wul_mingc_1],[t10].[mingc] AS [wul_mingc_2],[t11].[mingc] AS [wul_mingc_3]
 ,[t12].[guig] AS [wul_guig_4],[t12].[xingh] AS [wul_xingh_4],[t13].[bianm] AS [bianm1]
  FROM [zl_bianm_zhil_mingx] AS [t1]
 LEFT JOIN [jc_zhilzb] AS [t2] ON [t1].[zhilzb_fk]=[t2].[zhilzb_id]
 LEFT JOIN [zl_bianm_zhil] AS [t3] ON [t1].[bianm_zhil_fk]=[t3].[bianm_zhil_id]
 LEFT JOIN [bm_bianm] AS [t4] ON [t3].[bianm_fk]=[t4].[bianm_id]
 LEFT JOIN [jl_ruk_pic] AS [t5] ON [t4].[pic_fk]=[t5].[ruk_pic_id] 
 LEFT JOIN [sys_organizs] AS [t6] ON [t5].[organiz_fk]=[t6].[organiz_id]
 LEFT JOIN [jc_wul] AS [t9] ON [t5].[wul_fk_1]=[t9].[wul_id]
 LEFT JOIN [jc_wul] AS [t10] ON [t5].[wul_fk_2]=[t10].[wul_id]
 LEFT JOIN [jc_wul] AS [t11] ON [t5].[wul_fk_3]=[t11].[wul_id]
 LEFT JOIN [jc_wul] AS [t12] ON [t5].[wul_fk_4]=[t12].[wul_id]
 LEFT JOIN [bm_bianm] AS [t13] ON [t4].[bianm_fk]=[t13].[bianm_id]
 WHERE [t5].[wul_fk_2]='74cca884-931b-4b48-b2a0-22c197fbac65' AND [t1].[shujlx]=0) AS [tab]
 PIVOT(MAX(zhi) for [daim] in
 ([Al],[C],[Ca],[Cr],[Mn]
 ,[P],[S],[Si],[Ti]))a

posted @ 2017-11-29 17:16 贺兰春树阅读(254) 评论(0) 编辑收藏举报

刷新页面返回顶部