如何在选择SQL中嵌套大小写?
问题描述:
我有这个选择Case SQL语句来计算给定数量的totalvolume。如何在选择SQL中嵌套大小写?
SELECT
DropshipPackinglist.CaseNumber as 'CASE NO.',
DropshipPackinglist.ItemNumber as 'BOM NO.',
DropshipPackinglist.Quantity as 'QTY',
CASE
WHEN DropshipPackinglist.Quantity >=31 and DropshipPackinglist.Quantity <= 36 then '1090x730x1460'
WHEN DropshipPackinglist.Quantity >=25 and DropshipPackinglist.Quantity <= 30 then '1090x730x1230'
WHEN DropshipPackinglist.Quantity >=19 and DropshipPackinglist.Quantity <= 24 then '1090x730x1000'
WHEN DropshipPackinglist.Quantity >=13 and DropshipPackinglist.Quantity <= 18 then '1090x720x790'
WHEN DropshipPackinglist.Quantity >=7 and DropshipPackinglist.Quantity <= 17 then '1090x720x570'
WHEN DropshipPackinglist.Quantity >=1 and DropshipPackinglist.Quantity <= 6 then '1090x720x350'
ELSE 'Unkown'
end
as 'TOTAL VOLUME (MM3)'
FROM DropshipPackinglist INNER JOIN
HuaweiDescription ON DropshipPackinglist.ItemNumber = HuaweiDescription.ItemNumber
WHERE (DropshipPackinglist.BatchCode LIKE '%0005041007100AHWA11HG')
-------------------------------------------------------------------------------------------
Result:
CaseNumber ItemNumber Quantity TotalVolume
1 52411573 5 1090x720x350
1 52411576 20 1090x730x1000
2 52411576 36 1090x730x1460
-------------------------------------------------------------------------------------------
是现在,我想组casenumber和只有一个totalvolume结果。
而结果将是这一个。
CaseNumber ItemNumber Quantity TotalVolume
1 52411573 5 1090x730x1230 -- sum(casenumber 1)=25
1 52411576 20 1090x730x1230 --
2 52411576 36 1090x730x1460
如何解决这个one..thanks的问候。
答
;with SuperSelect as
(
SELECT dpl.CaseNumber as 'CASE NO.'
,dpl.ItemNumber as 'BOM NO.'
,dpl.Quantity as 'QTY'
,CASE WHEN dpl.Quantity >= 31 and dpl.Quantity <= 36 then '1090x730x1460'
WHEN dpl.Quantity >= 25 and dpl.Quantity <= 30 then '1090x730x1230'
WHEN dpl.Quantity >= 19 and dpl.Quantity <= 24 then '1090x730x1000'
WHEN dpl.Quantity >= 13 and dpl.Quantity <= 18 then '1090x720x790'
WHEN dpl.Quantity >= 7 and dpl.Quantity <= 17 then '1090x720x570'
WHEN dpl.Quantity >= 1 and dpl.Quantity <= 6 then '1090x720x350'
ELSE 'Unkown'
end as 'TOTAL VOLUME (MM3)'
FROM DropshipPackinglist dpl
INNER JOIN HuaweiDescription hd ON dpl.ItemNumber = hd.ItemNumber
WHERE (dpl.BatchCode LIKE '%0005041007100AHWA11HG')
)
select *, sum([QTY]) over (partition by ss.[CASE NO.]) [TotalVolume]
from SuperSelect ss
答
如果你需要每caseNumber只有一行,然后使用
SELECT CaseNumber, Quantity, SUM(ItemNumber) TotalVolume
FROM (...YourOriginalQuery...)
GROUP BY CaseNumber, Quantity
如果你需要的所有行,但要还报告则总每箱数量使用以下查询:
SELECT CaseNumber, ItemNumber, Quantity,
SUM(ItemNumber) OVER(PARTITION BY CaseNumber) TotalVolume
FROM (SELECT DropshipPackinglist.CaseNumber, DropshipPackinglist.ItemNumber,
DropshipPackinglist.Quantity,
CASE
WHEN DropshipPackinglist.Quantity >= 31
AND DropshipPackinglist.Quantity <= 36 THEN
'1090x730x1460'
WHEN DropshipPackinglist.Quantity >= 25
AND DropshipPackinglist.Quantity <= 30 THEN
'1090x730x1230'
WHEN DropshipPackinglist.Quantity >= 19
AND DropshipPackinglist.Quantity <= 24 THEN
'1090x730x1000'
WHEN DropshipPackinglist.Quantity >= 13
AND DropshipPackinglist.Quantity <= 18 THEN
'1090x720x790'
WHEN DropshipPackinglist.Quantity >= 7
AND DropshipPackinglist.Quantity <= 17 THEN
'1090x720x570'
WHEN DropshipPackinglist.Quantity >= 1
AND DropshipPackinglist.Quantity <= 6 THEN
'1090x720x350'
ELSE
'Unkown'
END AS 'TOTAL VOLUME (MM3)'
FROM DropshipPackinglist
INNER JOIN HuaweiDescription
ON DropshipPackinglist.ItemNumber = HuaweiDescription.ItemNumber
WHERE (DropshipPackinglist.BatchCode LIKE '%0005041007100AHWA11HG'))
+0
不正确语法靠近')'。 – Crimsonland 2010-09-10 07:29:51
对于sum运算符,操作数数据类型varchar无效。 – Crimsonland 2010-09-10 07:30:12
@Crimsonland sum([QTY])? – 2010-09-10 07:41:00
Thanks..it works ... – Crimsonland 2010-09-10 07:45:42