SqlServer增加合计行以及行转列，两表连接 -清零世界

bubuko 2022/1/25 19:02:04 sqlserver 字数 11564 阅读 958 0 来源 http://www.bubuko.com/infolist-5-1.html

SELECT a.[日期],
MAX(CASE WHEN a.Years = ‘2019-01‘ THEN a.TotalStock ELSE 0 END) AS ‘Jan‘,
MAX(CASE WHEN a.Years = ‘2019-02‘ THEN a.TotalStock ELSE 0 END) AS ‘Feb‘,
MAX(CASE WHEN a.Years = ‘2019-03‘ THEN a.TotalStock ELSE 0 END) AS ‘Mar‘,
MAX(CASE WHEN a.Years = ‘2019-04‘ THEN a.TotalStock ELSE 0 END) AS ‘Apr‘,
MAX(CASE WHEN a.Years = ‘2019-05‘ THEN a.TotalStock ELSE 0 END) AS ‘May‘,
MAX(CASE WHEN a.Years = ‘2019-06‘ THEN a.TotalStock ELSE 0 END) AS ‘Jun‘,
MAX(CASE WHEN a.Years = ‘2019-07‘ THEN a.TotalStock ELSE 0 END) AS ‘Jul‘,
MAX(CASE WHEN a.Years = ‘2019-08‘ THEN a.TotalStock ELSE 0 END) AS ‘Aug‘,
MAX(CASE WHEN a.Years = ‘2019-09‘ THEN a.TotalStock ELSE 0 END) AS ‘Sept‘,
MAX(CASE WHEN a.Years = ‘2019-10‘ THEN a.TotalStock ELSE 0 END) AS ‘Oct‘,
MAX(CASE WHEN a.Years = ‘2019-11‘ THEN a.TotalStock ELSE 0 END) AS ‘Nov‘,
MAX(CASE WHEN a.Years = ‘2019-12‘ THEN a.TotalStock ELSE 0 END) AS ‘Dec‘,
MAX(CASE WHEN a.Years IS NULL THEN a.TotalStock ELSE 0 END) AS ‘合计‘
 FROM (
SELECT  CASE WHEN GROUPING(a.[日期]) = ‘1‘ THEN ‘总计‘
                        WHEN GROUPING(a.[日期]) = ‘0‘ AND GROUPING(a.Years) = 1 THEN a.[日期] ELSE a.[日期] END ‘日期‘,
SUM(a.TotalStock) AS TotalStock, a.Years 
FROM(
SELECT  CONVERT(varchar(7) ,b.DeliveryDate, 120) AS Years, COUNT (b.Qrcode) AS TotalStock, ‘退货数量‘ AS 日期
FROM App_AfterSalesOrder a LEFT JOIN App_AfterSalesOrderDetail b ON a.OrderNo = b.OrderNo 
LEFT JOIN Base_GoodsInfo c ON b.ProductID = replace(ltrim(replace(c.ProductID, ‘0‘, ‘ ‘)    ),    ‘ ‘,    ‘0‘)
WHERE b.DeliveryDate >= ‘2019-01-01‘ AND b.DeliveryDate < ‘2020-01-01‘ AND a.IsExam = 1 AND b.Status = 1 AND c.Standard = ‘6-QW-48(430)‘
GROUP BY    CONVERT(varchar(7) ,b.DeliveryDate, 120)
UNION 
SELECT   CONVERT(varchar(7) ,a.AccountDate, 120) AS Years, SUM(b.ArrivalAmount) AS TotalStock, ‘进货数量‘ AS 日期
FROM Sap_DelivRecode a 
--LEFT JOIN Sap_DelivRecodeDetail b ON a.OrderNo = b.OrderNo
LEFT JOIN App_PurchaseOrderDetail b ON a.OrderNo = b.DelivNo
LEFT JOIN Base_GoodsInfo c ON b.ProductID = c.ProductID
WHERE a.AccountDate >= ‘2019-01-01‘ AND a.AccountDate < ‘2020-01-01‘ AND c.Brand IN (‘超威‘, ‘象王‘, ‘莫尔‘)
AND c.Standard = ‘6-QW-48(430)‘
GROUP BY    CONVERT(varchar(7) ,a.AccountDate, 120) )a
GROUP BY a.[日期], a.Years with rollup
) a
GROUP BY a.[日期]

1.增加小计总结时，with rollup 与 case when 一起使用，分组SUM求和

2.行转列使用case when

3.单独增加一列，直接赋值并 AS 新的字段名

4.连接表使用union，两表字段数量类型排序须一致

技术分享图片

SqlServer增加合计行以及行转列，两表连接

原文：https://www.cnblogs.com/2533-23348/p/14933608.html

如果您也喜欢它,动动您的小指点个赞吧

SqlServer增加合计行以及行转列，两表连接

分类汇总

您的鼓励是对我最大的支持