用友总帐SQL.docVIP

用友总帐SQL/*****用友凭证记帐分录*****/SELECT 凭证.iperiod AS 月, DAY ( 凭证.dbill_date ) AS 日, 记帐标志 = CASE WHEN 凭证.ibook = 1 THEN √ ELSE × END, 凭证.csign AS 凭证类别, 凭证.ino_id AS 凭证编号, 凭证.cbill AS 制单, 凭证.cdigest AS 摘要, 凭证.ccode AS 科目编码, 科目表.ccode_name AS 科目名称, 科目表.igrade AS 科目级次, LEFT ( 凭证.ccode , 4 ) AS 一级编码, Table_1.一级科目, Table_2.二级编码, Table_2.二级科目, Table_3.三级编码, Table_3.三级科目, 凭证.md AS 借方金额, 凭证.mc AS 贷方金额, 凭证.nd_s AS 数量借方, 凭证.nc_s AS 数量贷方, 凭证.cdept_id AS 部门编码, 部门表.cDepName AS 部门名称, 凭证.ccus_id AS 客户编码, 客户表.cCusAbbName AS 客户简称, 凭证.csup_id AS 供应商编码, 供应商表.cVenAbbName AS 供应商简称FROM UFDATA_008_2011.dbo.GL_accvouch AS 凭证 LEFT OUTER JOIN UFDATA_008_2011.dbo.Vendor AS 供应商表 ON 凭证.csup_id = 供应商表.cVenCode LEFT OUTER JOIN UFDATA_008_2011.dbo.Department AS 部门表 ON 凭证.cdept_id = 部门表.cDepCode LEFT OUTER JOIN UFDATA_008_2011.dbo.Customer AS 客户表 ON 凭证.ccus_id = 客户表.cCusCode LEFT OUTER JOIN UFDATA_008_2011.dbo.code AS 科目表 ON 凭证.ccode = 科目表.ccode LEFT OUTER JOIN ( SELECT Table0.科目编码, 科目表.ccode_name AS 一级科目 FROM ( SELECT 科目表.ccode AS 科目编码, 科目表.ccode_name AS 科目名称, LEFT ( 科目表.ccode,4 ) AS 一级编码 FROM UFDATA_008_2011.dbo.code AS 科目表 ) AS Table0 LEFT OUTER JOIN UFDATA_008_2011.dbo.code AS 科目表 ON Table0.一级编码 = 科目表.ccode ) AS Table_1 ON 凭证.ccode = Table_1.科目编码 LEFT OUTER JOIN ( SELECT Table0.科目编码, 二级编码, 科目表.ccode_name AS 二级科目 FROM ( SELECT 科目表.ccode AS 科目编码, 科目表.ccode_name AS 科目名称, 二级编码 = CASE WHEN LEN( 科目表.ccode) 4