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