sql - 将多行压缩为一个总和行
问题描述
我正在尝试将 Quantity <=500 的所有行压缩为一个行条目,将它们加在一起
SELECT c.CompanyName
, d.NumNameCode
--, CAST((d.Quantity*10) as INT) as Quantityx10
, SUM(CASE
WHEN Quantity <=500 THEN CAST((Quantity*10) as INT)
ELSE ''
END) as '<=500'
, CASE
WHEN Quantity > 500 THEN CAST((Quantity*10) as INT)
ELSE ''
END as '>500'
FROM [TFBO7].[dbo].[CustInv] ci
inner join [TFBO7].[dbo].[Cust] c
on c.CustomerID=ci.CustomerID
inner join [TFBO7].[dbo].[CustIDet] d
on ci.TransactionID=d.TransactionID
WHERE ci.TransactionDate between '2010-07-01' and '2010-07-30' and d.TransactionTypeID='1' and NumNameCode LIKE '%gov%'
group by NumNameCode, CompanyName, Quantity
order by NumNameCode, '<=500'
GO
我的桌子看起来像这样
CompanyName NumNameCode <=500 >500
DESC JET A*Gov Army 0 5030
DESC JET A*Gov Army 0 7140
DESC Jet A*Gov Army 0 7940
DESC Jet A*Gov Army 680 0
DESC Jet A*Gov Army 710 0
DESC Jet A*Gov Army 860 0
我需要将 <=500 的条目压缩为一个求和行
CompanyName NumNameCode <=500 >500
DESC JET A*Gov Army 0 5030
DESC JET A*Gov Army 0 7140
DESC Jet A*Gov Army 0 7940
DESC Jet A*Gov Army 0 2250
解决方案
正如您在代码中看到的那样,您已经group by
打开了quantity
,因此它将导致您的问题的所有唯一数量分组。您可以使用前面评论中建议的子查询,也可以Union
像下面这样简单:
SELECT CompanyName
, NumNameCode
, SUM(CASE WHEN quantity <= 500 THEN CAST(( quantity * 10 ) AS INT)
ELSE ''
END) AS '<=500'
, NULL AS '>500'
FROM #temp2
WHERE quantity < 500
GROUP BY NumNameCode , CompanyName
UNION ALL
SELECT CompanyName
, NumNameCode
, NULL '<=500'
, CAST(( quantity * 10 ) AS INT) '>500'
FROM #temp2
WHERE quantity >= 500
GO
推荐阅读
- javascript - 为 3D 数组赋值,但它填充了太多的值
- python - 输入包含 NaN
- python - 根据时间列中两个值之间的差异,将数据框中的每一行重复 N 次
- html - Bootstrap 4:无法对齐列内的项目
- python - 如何使用 Selenium 识别此元素并将 element.click() 与数字变量一起使用
- django - 在 Django Crispy Forms 中创建 if 语句,条件表单布局
- xero-api - Xero 与 Web 系统的集成
- python - 将动画 matplotlib 绘图转换为绘图破折号
- python - 将多个数据集输入到 tensorflow 模型
- ethereum - Solidity 中的切片数字(例如从 uint 中提取 2 个第一个数字)