SQL 分组求和+行转列

效果图

1.分组求和
统计每一天，不同物料的销售情况。

2.在此基础上，行转列
方便前端折线图赋值

语句

分组求和

注意 一定要加入：top 100 percent， 因为这整段的语句中有 order by 语法， 当它作为子查询时，会提示报错：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

SELECT
top 100 percent
CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ) AS GWTime2,
 MaterialID,
  MaterialDes,
	SUM ( NW ) AS NW,
	BusinessTypeDes
	FROM
	uMsInfo
	LEFT JOIN mdBusinessType on uMsInfo.BusinessTypeID = mdBusinessType.BusinessTypeID
WHERE
	uMsInfo.BusinessTypeID = 2 
	AND GWTime >= '2022-01-18 06:39:35.427' 
	AND GWTime <= '2022-01-20 06:39:35.427' 
	--AND MaterialID ='10011'

GROUP BY CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ),MaterialID,MaterialDes,BusinessTypeDes
ORDER BY CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ),MaterialID,MaterialDes,BusinessTypeDes

行转列

SELECT  
	GWTime2,
	SUM( CASE MaterialDes WHEN '承德铁精粉' THEN NW ELSE 0 END ) '承德铁精粉',
	SUM( CASE MaterialDes WHEN '废渣小料' THEN NW ELSE 0 END ) '废渣小料',
	SUM( CASE MaterialDes WHEN '磷精粉' THEN NW ELSE 0 END ) '磷精粉',
	SUM( CASE MaterialDes WHEN '尾矿渣' THEN NW ELSE 0 END ) '尾矿渣' ,
	BusinessTypeDes
FROM
	(

	//上一步的分组求和语句，作为子查询，记住添加 ：top 100 percent
  SELECT
  top 100 percent
  CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ) AS GWTime2,
  MaterialID,
  MaterialDes,
	SUM ( NW ) AS NW,
	BusinessTypeDes
	FROM
	uMsInfo
	LEFT JOIN mdBusinessType on uMsInfo.BusinessTypeID = mdBusinessType.BusinessTypeID
WHERE
	uMsInfo.BusinessTypeID = 2 
	AND GWTime >= '2022-01-18 06:39:35.427' 
	AND GWTime <= '2022-01-20 06:39:35.427' 
	--AND MaterialID ='10011'

GROUP BY CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ),MaterialID,MaterialDes,BusinessTypeDes
ORDER BY CONVERT( VARCHAR ( 10 ), uMsInfo.GWTime, 121 ),MaterialID,MaterialDes,BusinessTypeDes


	)as A
	 
GROUP BY
	GWTime2,BusinessTypeDes

到此基本实现，可以根据自己的情况进行更改。
这里是井队，天高任鸟飞，海阔凭鱼跃，点个关注不迷路，我们下期再见。