如果用一条SQL语句实现不太可能,可以先横向求和,再纵向求和:
SELECT 部门,岗位工资,技能工资,岗位工资+技能工资 AS 应付工资 FROM 表名 SELECT SUM(岗位工资) AS 岗位工资,SUM(技能工资) AS 技能工资,SUM(岗位工资+技能工资) AS 应付工资 FROM 表名
SELECT 部门,岗位工资,技能工资,岗位工资+技能工资 AS 应付工资 FROM 表名 SELECT SUM(岗位工资) AS 岗位工资,SUM(技能工资) AS 技能工资,SUM(岗位工资+技能工资) AS 应付工资 FROM 表名
insert into table1 select '合计' as 部门,sum(a.岗位工资) as 岗位工资,sum(a.技能工资) as 技能工资,sum(a.应付工资) as 应付工资 from table1 as a
务器: 消息 213,级别 16,状态 4,行 4
插入错误: 列名或所提供值的数目与表定义不匹配。
SELECT 部门,岗位工资,技能工资,岗位工资+技能工资 应付工资 FROM 表名
union all
select '合计',sum(岗位工资),sum(技能工资),sum(岗位工资+技能工资) from 表名
ALTER table table1 add 应付工资 int
UPDATE table1 SET 应付工资 = 岗位工资+技能工资;
insert into table1 select '合计' as 部门,sum(a.岗位工资) as 岗位工资,sum(a.技能工资) as 技能工资,sum(a.应付工资) as 应付工资 from table1 as a
ALTER TABLE table1 DROP COLUMN 应付工资
go
UPDATE table1 SET 应付工资 = 岗位工资+技能工资
bo
insert into table1 select '合计' as 部门,sum(a.岗位工资) as 岗位工资,sum(a.技能工资) as 技能工资,sum(a.应付工资) as 应付工资 from table1 as a
go
select * from table1
go
ALTER TABLE table1 DROP COLUMN 应付工资
新增的应付工资改变了基表的结构,最后应该删除这个字段
水1 1000 2000
水2 2000 3000
水3 3000 4000
以上是原始表,需生成以下汇总表部门 岗位工资 技能工资 应付工资
水1 1000 2000 ?
水2 2000 3000 ?
水3 3000 4000 ?
?合计 ? ? ?可以这样写:
SELECT a.部门 as 部门,a.岗位工资 as 岗位工资,a.技能工资 as 技能工资,岗位工资+技能工资 AS 应付工资 FROM 表名 a
union
(
SELECT '合计' as 部门,SUM(岗位工资) AS 岗位工资,SUM(技能工资) AS 技能工资,SUM(岗位工资+技能工资) AS 应付工资 FROM 表名 b)