汇总同一工作簿下多张数据列表记录创建数据透视表，明细字段未正常显示合计金额

XXKENG

利用SQL汇总同一工作簿下多张数据列表记录创建数据透视表，仅“合计”字段有数字，但其它明细字段全部显示0，SQL语句如下：SELECT "A项目" AS 项目名称,* FROM [A项目$] UNION ALL SELECT "B项目" AS 项目名称,* FROM [B项目$] UNION ALL SELECT "C项目" AS 项目名称,* FROM [C项目$] UNION ALL SELECT "D项目" AS 项目名称,* FROM [D项目$] UNION ALL SELECT "E项目" AS 项目名称,* FROM [E项目$] UNION ALL SELECT "F项目" AS 项目名称,* FROM [F项目$]

请问是我源数据有什么问题还是SQL语句有错？

q1579398897

SELECT 后面不要写成星号，因为除了D项目外，您的其他工作表都是空表，SQL会根据第一个工作表前几行自动把字段值识别成非数值。

XXKENG

q1579398897 发表于 2022-3-11 08:38
SELECT 后面不要写成星号，因为除了D项目外，您的其他工作表都是空表，SQL会根据第一个工作表前几行自动把 ...

那语句要如何修改呢，向大神请教

q1579398897

1. SELECT "A项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [A项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL
   
2. UNION ALL
   
3. SELECT "B项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [B项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL
   
4. UNION ALL
   
5. SELECT "C项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [C项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL
   
6. UNION ALL
   
7. SELECT "D项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [D项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL
   
8. UNION ALL
   
9. SELECT "E项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [E项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL
   
10. UNION ALL
    
11. SELECT "F项目" AS 项目名称,报销所属月份,发票情况,合计*1 AS [备用金开支（合计）],项目部办公开支*1 AS 项目部办公开支金额,[房租、水电费]*1 AS [房租、水电费金额],自聘人员工资*1 AS 自聘人员工资金额,接待费*1 AS 接待费金额,补贴费*1 AS 补贴费金额,施工用主要材料*1 AS 施工用主要材料金额,施工用零星材料*1 AS 施工用零星材料金额,[日常餐费、伙食费]*1 AS [日常餐费、伙食费金额],加班餐费*1 AS 加班餐费金额,零星人工费*1 AS 零星人工费金额,零星机械台班费*1 AS 零星机械台班费金额,其它费用*1 AS 其它费用金额 FROM [F项目$] WHERE 报销所属月份<>"合计" AND 报销所属月份 IS NOT NULL

复制代码

yanglinzi

因为只有“D项目"有数据，并且合计的第一行有数据，其他表都是空表。

yanglinzi

没有数据的行填入0试试