我无法加载该表。我希望所有<500的数量值加总,其中NameCode相同。如果总和仍然小于500,请不要插入。这可能吗?
CREATE TABLE [dbo].[GovQuantity](
[NameCode] [varchar](40) NULL,
[Quantity] [decimal](18,6) NULL,
)
INSERT INTO GovQuantity
SELECT d.NumNameCode
, Quantity = CASE WHEN QUANTITY < 500 THEN SUM(Quantity) ELSE Quantity END
FROM [...]
GROUP BY NumNameCode, Quantity
目前有这个:
NameCode Quantity
ArmyGuard 125
ArmyGuard 605
ArmyGuard 65
Boeing 420
Boeing 750
Boeing 100
需要:
NameCode Quantity
ArmyGuard 605
Boeing 520
Boeing 750
最安全的写法是union all
:
select NameCode, sum(Quantity) as Quantity
from govquantity
where Quantity < 500
group by NameCode
having sum(Quantity) >= 500
union all
select NameCode, Quantity
from govquantity
where Quantity >= 500;
使用单个聚合编写此代码很诱人:
我想您只是想要:
SELECT d.NumNameCode, SUM(Quantity)
FROM [...]
WHERE QUANTITY < 500
GROUP BY NumNameCode,
(CASE WHEN Quantity >= 500 THEN Quantity END)
HAVING SUM(QUANTITY) >= 500;
但是,如果两个quantity
值相同且大于500 ,则会造成混乱。
如果您有一个唯一的ID(或每个名称至少一个唯一),则将是:
SELECT d.NumNameCode, SUM(Quantity)
FROM [...]
WHERE QUANTITY < 500
GROUP BY NumNameCode,
(CASE WHEN Quantity >= 500 THEN Id END)
HAVING SUM(QUANTITY) >= 500;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句