请问用SQL如何编制以下报表
发布网友
发布时间：2022-04-23 16:37
共2个回答
热心网友
时间：2023-07-06 23:43
SQL Server 2008及以上的话可以这样：
with 表A (仓库,物料代码,物料名称,规格型号,单位,单价,收入数量,收入金额) as (
select '原料仓','01.01','原料1','注塑','PCS',2,100,200 union all
select '原料仓','01.02','原料2','','PCS',3,100,300 union all
select '原料仓','01.03','原料3','喷油','PCS',4,100,400 union all
select '原料仓','01.15','原料6','','PCS',1.2,1000,1200 union all
select '成品仓','02.00001','等级品测试A','','PCS',23.37,2,46.74 union all
select '成品仓','02.00004','等级品测试B','','PCS',46.34,2,92.67 union all
select '成品仓','02.03','成品3','','PCS',16.54,30,496.16 union all
select '成品仓','02.04','成品4','','PCS',2.13,200,426.15 union all
select '成品仓','02.06','替代测试0911','','PCS',0.4,100,40
)
--按仓库分类汇总，并与原始数据一起展示
select 仓库,物料代码,物料名称,规格型号,单位,单价,收入数量,收入金额 from(
select 0 as ordercode,* from 表A
union all
select grouping_id(仓库),isnull(仓库+'(小计)','合计'),null,null,null,null,null,sum(收入数量),sum(收入金额) from 表A
group by cube(仓库)
) t
order by ordercode,仓库
结果：
热心网友
时间：2023-07-06 23:43
以下语句所有SQL版本通用，先按物料汇总，再按仓库小计，最后合计：
select * from(select top 100000 * from(select 仓库,物料代码,物料名称,规格型号,单位,单价,sum(收入数量) as 收入数量,sum(收入金额) as 收入金额 from 表A group by 仓库,物料代码,物料名称,规格型号,单位,单价 union all select 仓库+'(小计)' as 仓库,null as 物料代码,null as 物料名称,null as 规格型号,null as 单位,null as 单价,收入数量,收入金额 from(select 仓库,sum(收入数量) as 收入数量,sum(收入金额) as 收入金额 from 表A group by 仓库)a )b order by 仓库 union all select '合计' as 仓库,null as 物料代码,null as 物料名称,null as 规格型号,null as 单位,null as 单价,收入数量,收入金额 from(select sum(收入数量) as 收入数量,sum(收入金额) as 收入金额 from 表A)c )d追问你的方法，我测试了，数据是没问题，只是没有按照我要的格式进行排序。你看我的效果图片，我希望是每个仓库的小计是紧跟着该仓库的明细显示。合计显示在最下面。
追答我专门建了个一样的表测试，下面这段是有排序的：
SELECT *
FROM (SELECT TOP 100000 *
FROM (SELECT 仓库, 物料代码, 物料名称, 规格型号, 单位, 单价, SUM(收入数量)
AS 收入数量, SUM(收入金额) AS 收入金额
FROM 表A
GROUP BY 仓库, 物料代码, 物料名称, 规格型号, 单位, 单价
UNION ALL
SELECT 仓库 + '(小计)' AS 仓库, NULL AS 物料代码, NULL
AS 物料名称, NULL AS 规格型号, NULL AS 单位, NULL AS 单价,
收入数量, 收入金额
FROM (SELECT 仓库, SUM(收入数量) AS 收入数量, SUM(收入金额)
AS 收入金额
FROM 表A
GROUP BY 仓库) a) b
ORDER BY 仓库) c
UNION ALL
SELECT '合计' AS 仓库, NULL AS 物料代码, NULL AS 物料名称, NULL AS 规格型号, NULL
AS 单位, NULL AS 单价, 收入数量, 收入金额
FROM (SELECT SUM(收入数量) AS 收入数量, SUM(收入金额) AS 收入金额
FROM 表A) d
