27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
IF OBJECT_ID('#tab') IS NOT NULL
DROP TABLE #tab
CREATE TABLE #tab (
序号 int,
物品 varchar(20),
单价 NUMERIC(4,2),
公司领导 int,
经理办 int,
党办 int,
财务 int
)
INSERT INTO #tab
SELECT 1,'稿纸',2,1,2,3,4
UNION ALL
SELECT 2,'工资表',5,1,2,3,4
UNION ALL
SELECT 3,'信封',0.2,1,2,3,4
SELECT * FROM #tab
--测试数据结束
--解决方案
SELECT *,公司领导+经理办+党办+财务 as cnt,
(公司领导+经理办+党办+财务)* 单价 as sumPrice
FROM #tab
UNION
SELECT '99' AS 序号,'总量' as 物品,null as 单价 ,
sum(公司领导) as 公司领导,sum(经理办)as 经理办,sum(党办) as 党办,sum(财务) as 财务,
sum(公司领导)+sum(经理办)+sum(党办)+sum(财务) as cnt,
NULL AS sumPrice
FROM #tab
UNION
SELECT '999' AS 序号,'总价' as 物品,null as 单价 ,
sum(公司领导*单价) as 公司领导,sum(经理办*单价)as 经理办,sum(党办*单价) as 党办,sum(财务*单价) as 财务,
NULL AS cnt,
sum(公司领导*单价)+sum(经理办*单价)+sum(党办*单价)+sum(财务*单价)as sumPrice
from #tab