一个零件出库表,每条记录包括时间和零件编号、名称等,最多可有5个零件
现在想统计某个零件的总出库量,该怎么写?谢谢!SELECT FinishTime,[PartNo-a],[Qty-a],[Description-a],[PartNo-b],[Qty-b],[Description-b],[PartNo-c],[Qty-c],[Description-c],[PartNo-d],[Qty-d],[Description-d],[PartNo-e],[Qty-e],[Description-e] FROM [Work Report] WHERE [PartNo-a] LIKE '%"&PartNo&"' OR [PartNo-b] LIKE '%"&PartNo&"' OR [PartNo-c] LIKE '%"&PartNo&"' OR [PartNo-d] LIKE '%"&PartNo&"' OR [PartNo-e] LIKE '%"&PartNo&"' ORDER BY FinishTime DESC
现在想统计某个零件的总出库量,该怎么写?谢谢!SELECT FinishTime,[PartNo-a],[Qty-a],[Description-a],[PartNo-b],[Qty-b],[Description-b],[PartNo-c],[Qty-c],[Description-c],[PartNo-d],[Qty-d],[Description-d],[PartNo-e],[Qty-e],[Description-e] FROM [Work Report] WHERE [PartNo-a] LIKE '%"&PartNo&"' OR [PartNo-b] LIKE '%"&PartNo&"' OR [PartNo-c] LIKE '%"&PartNo&"' OR [PartNo-d] LIKE '%"&PartNo&"' OR [PartNo-e] LIKE '%"&PartNo&"' ORDER BY FinishTime DESC
select [PartNo-a],sum([Qty-a]) as total
from [Work Report]
where [PartNo-a] LIKE '%"&PartNo&"'
group by PartNo-a]
union all
select [PartNo-b],sum([Qty-b]) as total
from [Work Report]
where [PartNo-b] LIKE '%"&PartNo&"'
group by PartNo-b]
union all
select [PartNo-c],sum([Qty-c]) as total
from [Work Report]
where [PartNo-c] LIKE '%"&PartNo&"'
group by PartNo-c]
union all
select [PartNo-d],sum([Qty-d]) as total
from [Work Report]
where [PartNo-d] LIKE '%"&PartNo&"'
group by PartNo-d]
union all
select [PartNo-e],sum([Qty-e]) as total
from [Work Report]
where [PartNo-e] LIKE '%"&PartNo&"'
group by PartNo-e]
(select sum(Qty-a) from [Work Report] WHERE [PartNo-a]=@partno)
+(select sum(Qty-b) from [Work Report] WHERE [PartNo-b]=@partno)
+(select sum(Qty-c) from [Work Report] WHERE [PartNo-c]=@partno)
+(select sum(Qty-d) from [Work Report] WHERE [PartNo-d]=@partno)
+(select sum(Qty-e) from [Work Report] WHERE [PartNo-e]=@partno)
select sumqta =
(select sum(Qty-a) from [Work Report] WHERE [PartNo-a]=@partno)
+(select sum(Qty-b) from [Work Report] WHERE [PartNo-b]=@partno)
+(select sum(Qty-c) from [Work Report] WHERE [PartNo-c]=@partno)
+(select sum(Qty-d) from [Work Report] WHERE [PartNo-d]=@partno)
+(select sum(Qty-e) from [Work Report] WHERE [PartNo-e]=@partno)
发现结果是NULL。怎么回事?该怎么解决?
改成
sum(isNull([Qty-a],0))