用友U8是典型的SQL Server数据库架构理解其表结构是实施顾问和二次开发者的基本功。本文从实战出发梳理财务、供应链、生产制造三大模块的核心表及关联关系配合可直接使用的SQL查询示例。背景说明用友U8采用SQL Server数据库数据存储在 UFDATA_年份_账套号 命名的数据库中如 UFDATA_2026_001。每个模块对应一组表表名通常有固定前缀GL → 总账模块AR → 应收模块AP → 应付模块IA → 存货核算ST → 库存模块PU → 采购模块SA → 销售模块PP → 生产模块FA → 固定资产BD → 基础档案公共表免责声明以下表结构基于U8 V16.x实测不同版本可能有字段差异。涉及数据修改前请务必备份并在测试库验证。一、基础档案表最常用基础档案贯穿所有业务模块先搞懂这些公共表。1.1 组织架构- 部门档案bd_department SELECT * FROM bd_department WHERE bclosed 0 -- 未停用 -- 职员档案hr_person SELECT * FROM hr_person WHERE bdimission 0 -- 在职字段含义cDepCode部门编码cDepName部门名称bclosed是否停用cDepGrade部门级次1.2 客户和供应商-- 客户档案bd_cubasdoc SELECT cCusCode, cCusName, cCusAbbName, cCusManager FROM bd_cubasdoc WHERE bCusState 1 -- 客户启用状态 -- 供应商档案bd_supbasdoc SELECT cSupCode, cSupName, cSupAbbName, cSupGrade FROM bd_supbasdoc WHERE bSupState 1 -- 供应商启用状态注意bd_cubasdoc 是客户基础档案主表详细信息在 bd_cubasdoc_extradef自定义项。1.3 存货商品/物料-- 存货档案主表bas_part SELECT a.cInvCode, a.cInvName, a.cInvStd, a.cInvCCode, b.cInvCName -- 存货分类名称 FROM bas_part a LEFT JOIN bas_inventoryclass b ON a.cInvCCode b.cInvCCode WHERE a.bPartState 1 -- 启用状态 AND a.cInvCode CM -- 排除母件 -- 查询某存货分类下的所有物料 SELECT cInvCode, cInvName, cInvStd, iInvSCost FROM bas_part WHERE cInvCCode 01 -- 原材料分类 AND bPartState 1 ORDER BY cInvCode二、财务模块核心表2.1 会计科目表GL_accode-- 查询所有末级科目 SELECT cclass AS 科目大类, ccode AS 科目编码, cname AS 科目名称, cbook_type AS 账簿类型 FROM gl_accvouch WHERE igrade (SELECT MAX(igrade) FROM gl_accvouch WHERE cclass gl_accvouch.cclass) ORDER BY ccode -- 查询有辅助核算的科目 SELECT ccode, cname, bperson, bdept, bitem, bcus, bsup, bproject FROM gl_accvouch WHERE igrade 3 -- 三级科目 AND (bperson 1 OR bdept 1 OR bcus 1 OR bsup 1)2.2 凭证主表GL_accvouch这是U8最核心的表所有凭证数据都存在这里。-- 查询指定期间的凭证 SELECT cbill, dbill_date, csign, ino_id AS 凭证号, ccode AS 科目编码, cname AS 科目名称, md AS 借方金额, mc AS 贷方金额, cxth AS 摘要行号, cdigest AS 摘要 FROM gl_accvouch WHERE iyear 2026 AND imonth 5 AND ino_id 0 ORDER BY dbill_date, ino_id, cxth字段含义cbill制单人dbill_date凭证日期csign凭证字记/转ino_id凭证号ccode科目编码md借方金额mc贷方金额cdigest摘要bdiffirst是否借贷方向不平iperiod期间号2.3 凭证借贷平衡检查-- 查找借贷不平的凭证最常用的审计SQL SELECT dbill_date, ino_id, cbill, SUM(md) AS 借方合计, SUM(mc) AS 贷方合计, SUM(md) - SUM(mc) AS 差异 FROM gl_accvouch WHERE iyear 2026 AND imonth 5 GROUP BY dbill_date, ino_id, cbill HAVING ABS(SUM(md) - SUM(mc)) 0.001 ORDER BY dbill_date, ino_id2.4 应收账款查询AR_receivable / AR_apbilldetail-- 查询客户应收账款余额AR_receivable SELECT b.cCusCode, b.cCusName, SUM(ISNULL(iAmount,0)) AS 应收金额, SUM(ISNULL(iReceiveAmount,0)) AS 已收金额, SUM(ISNULL(iAmount,0)) - SUM(ISNULL(iReceiveAmount,0)) AS 余额 FROM ar_receivable a LEFT JOIN bd_cubasdoc b ON a.ccuscode b.ccuscode WHERE a.dclosed 0 -- 未关闭 GROUP BY b.cCusCode, b.cCusName HAVING SUM(ISNULL(iAmount,0)) - SUM(ISNULL(iReceiveAmount,0)) 0 ORDER BY 余额 DESC三、供应链核心表3.1 销售模块SA系列-- 销售订单主表SA_SaleOrder SELECT a.cSOCode AS 订单号, b.cCusName AS 客户名称, a.dDate AS 订单日期, a.cVerifier AS 审核人, a.cSCloser AS 关闭人, a.cState AS 订单状态 FROM SA_SaleOrder a LEFT JOIN bd_cubasdoc b ON a.cCusCode b.cCusCode WHERE a.dDate 2026-01-01 AND a.dDate 2026-12-31 -- 销售订单明细SA_SaleOrderB SELECT a.cSOCode, b.cInvCode AS 存货编码, b.cInvName AS 存货名称, b.iquantity AS 订单数量, b.irowno AS 行号 FROM SA_SaleOrder a JOIN SA_SaleOrderB b ON a.id b.id WHERE a.dDate 2026-05-013.2 销售出库单ST_SaleOut-- 查询某月销售出库汇总按存货 SELECT b.cInvCode AS 存货编码, b.cInvName AS 存货名称, SUM(b.iquantity) AS 出库数量, SUM(ISNULL(b.iCessAmount,0)) AS 含税金额 FROM ST_SaleOut a JOIN ST_SaleOutB b ON a.id b.id WHERE a.dDate 2026-05-01 AND a.dDate 2026-06-01 AND a.cVouchState 1 -- 已审核 GROUP BY b.cInvCode, b.cInvName ORDER BY SUM(ISNULL(b.iCessAmount,0)) DESC3.3 采购模块PU系列-- 采购订单明细 SELECT a.cPOCode AS 采购订单号, b.cInvCode AS 存货编码, b.cInvName AS 存货名称, b.iquantity AS 订单数量, b.iarrqty AS 到货数量, b.iQAQuantity AS 质检数量 FROM PU_ArrivalVouch a JOIN PU_ArrivalVouchB b ON a.id b.id WHERE a.dDate 2026-01-01 ORDER BY a.dDate DESC -- 采购入库汇总对接存货核算 SELECT b.cWhCode AS 仓库编码, b.cInvCode AS 存货编码, SUM(b.iquotedpricequantity) AS 入库数量, SUM(b.iCessAmount) AS 含税金额 FROM PU_ArrivalVouch a JOIN PU_ArrivalVouchB b ON a.id b.id WHERE a.dDate 2026-05-01 AND a.cVouchState 1 GROUP BY b.cWhCode, b.cInvCode3.4 库存台账ST_InvMoreSum / ST_Inventory-- 当前库存查询按仓库存货 SELECT a.cWhCode AS 仓库编码, b.cWhName AS 仓库名称, a.cInvCode AS 存货编码, c.cInvName AS 存货名称, a.iquantity AS 库存数量, a.iTaxCost AS 库存单价, a.iTaxAmount AS 库存金额 FROM ST_Inventory a JOIN bd_warehouse b ON a.cWhCode b.cWhCode JOIN bas_part c ON a.cInvCode c.cInvCode WHERE a.iquantity 0 ORDER BY a.cWhCode, a.cInvCode四、生产制造核心表4.1 BOM表物料清单-- 查询某产品的BOM结构PP_BOM SELECT a.cInvCode AS 父件编码, a.cInvName AS 父件名称, b.cInvCode AS 子件编码, b.cInvName AS 子件名称, b.Quantity AS 用量, b.SCrap AS 损耗率, b.id AS BOM版本ID FROM bas_part a JOIN ( SELECT p.id AS bom_id, p.cInvCode AS cInvCode, p.cInvName AS cInvName, b.cInvCode, b.cInvName, b.Quantity, b.SCrap FROM PP_BOM p JOIN PP_BOMDetail b ON p.id b.id WHERE p.bFX 0 -- 非反向 AND p.bSet 1 -- 有效版本 ) b ON a.cInvCode b.cInvCode WHERE a.cInvCode FG-001 -- 替换为实际产品编码4.2 生产订单PP_ProductOrder-- 查询在产生产订单 SELECT a.cMoCode AS 生产订单号, b.cInvName AS 产品名称, a.dDate AS 下单日期, a.dStartDate AS 开工日期, a.dDueDate AS 完工日期, a.iquantity AS 订单数量, a.QualifiedInQuantity AS 合格入库数量, CASE a.Status WHEN 1 THEN 计划 WHEN 2 THEN 锁定 WHEN 3 THEN 下达 WHEN 5 THEN 完工 ELSE 其他 END AS 订单状态 FROM PP_ProductOrder a JOIN bas_part b ON a.cInvCode b.cInvCode WHERE a.Status NOT IN (5, 9) -- 排除完工和关闭 ORDER BY a.dDueDate4.3 MRP运算结果MRP_MRPPlan-- 查询MRP运算后的采购建议 SELECT a.cPTCode AS 计划订单号, b.cInvCode AS 物料编码, b.cInvName AS 物料名称, b.Quantity AS 建议数量, b.dDemandDate AS 需求日期, CASE b.cMrpType WHEN P THEN 采购 WHEN M THEN 生产 WHEN T THEN 请购 END AS 建议类型 FROM MRP_MRPPlan a JOIN MRP_MRPPlanDetail b ON a.id b.id WHERE b.cMrpType P -- 仅采购建议 AND b.Quantity 0 ORDER BY b.dDemandDate五、常用业务查询实战5.1 销售-出库-开票-收款全流程追踪-- 追踪某客户某月的完整销售闭环 SELECT sa.cSOCode AS 订单号, sa.dDate AS 接单日期, st.cCode AS 出库单号, st.dDate AS 出库日期, st.iquantity AS 出库数量, ia.cVouchID AS 发票号, ia.dVouchDate AS 开票日期, ia.iCessAmount AS 发票金额 FROM SA_SaleOrder sa JOIN ST_SaleOut st ON sa.cSOCode st.cSOCode JOIN IA_SalesInvoice ia ON st.cCode ia.cCode WHERE sa.cCusCode CUS001 -- 替换为实际客户编码 AND YEAR(sa.dDate) 2026 AND MONTH(sa.dDate) 5 ORDER BY sa.dDate5.2 库存呆滞料分析-- 90天以上无出入库的存货呆滞料 SELECT cInvCode AS 存货编码, cInvName AS 存货名称, iquantity AS 当前库存, iTaxCost AS 单价, iTaxAmount AS 库存金额 FROM ST_Inventory WHERE cInvCode NOT IN ( -- 过去90天内有出入库记录的存货 SELECT DISTINCT cInvCode FROM ST_InvMove WHERE dDate DATEADD(day, -90, GETDATE()) ) AND iquantity 0 ORDER BY iTaxAmount DESC5.3 某销售员业绩统计-- 按销售员统计某月销售情况 SELECT a.cCusCode AS 客户编码, b.cCusName AS 客户名称, SUM(st.iquantity) AS 销售数量, SUM(st.iCessAmount) AS 销售金额, COUNT(DISTINCT st.cCode) AS 出库单数 FROM ST_SaleOut st JOIN bd_cubasdoc b ON st.cCusCode b.cCusCode JOIN hr_person p ON st.cPersonCode p.cPsn_Num WHERE st.dDate 2026-05-01 AND st.dDate 2026-06-01 AND p.cPsn_Name 张三 -- 替换为实际销售员姓名 GROUP BY a.cCusCode, b.cCusName ORDER BY SUM(st.iCessAmount) DESC六、二次开发注意事项6.1 常用字段类型说明字段特征含义i 开头如 iquantity, iAmount数量/金额字段通常decimal类型c 开头如 cInvCode, cCusName字符型字段d 开头如 dDate, dDueDate日期型字段b 开头如 bclosed, bPartState布尔型字段1是/0否id 结尾主键ID6.2 跨年查询注意U8每个年度的数据可能在不同的数据库中-- 跨年查询需要切换数据库 USE UFDATA_2025_001; -- 2025年账套 SELECT COUNT(*) AS 凭证数 FROM gl_accvouch WHERE iyear 2025; USE UFDATA_2026_001; -- 2026年账套 SELECT COUNT(*) AS 凭证数 FROM gl_accvouch WHERE iyear 2026;6.3 禁止直接修改的数据以下表/字段严禁直接用UPDATE/DELETE修改gl_accvouch 中的已记账凭证需反记账ia_* 存货核算相关表需通过业务单据走核算流程凭证号、期间号等关联校验字段总结用友U8的数据库表结构虽然看起来复杂但有以下规律表名前缀 模块缩写SA销售、PU采购、ST库存、GL总账、AR应收、AP应付主表_B 明细表如 SA_SaleOrder SA_SaleOrderB基础档案在 BD_开头的公共表中业务数据引用其编码凭证表 GL_accvouch 是核心几乎所有财务报表的数据都从这里出掌握这些规律配合上面的SQL示例日常工作足够用了。成都云策数链科技有限公司 | 用友四川授权服务中心 | 专注企业数字化转型