TMS托运单和货物一般都是一对多的关系,考虑到要打印托运单,一般市面上TMS运输管理系统最多都只允许填写3个货物信息,更多的是只允许填写一个货物信息,如果实在要填多个货物信息,比如有颜料和电脑2种货物,填写方式是颜料+货物的方式填写货物信息。
我们的TMS通过“/”来分隔货物信息:货物名称、件数、包装、重量、体积、单价、单位。如果没有填写的信息用“-”代替。同样,在计算总件数、总体积、总重量的时候,如果有任意一个货物信息没有填写,则计算结果为0,因为要考虑到以后的费用分摊。其中涉及到的SQL语句如下,我只截取了主要部分。
select OrderId, (select isnull(GoodsName,'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as GoodsName,--货物名称 (select isnull(cast(Numbers as varchar(20)),'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as Numbers,--件数 (select isnull(PacketMode,'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as PacketMode,--包装 (select isnull(cast(WeightShow as varchar(20)),'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as WeightShow,--重量(吨) (select isnull(cast(Volume as varchar(20)),'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as Volume,--体积 (方) (select isnull(cast(UnitPriceShow as varchar(20)),'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as UnitPriceShow,--单价 (select isnull(cast(Unit as varchar(20)),'-')+'/' from t_Bus_Order_Goods where OrderId=a.OrderId for xml path('')) as Unit,--单位 (case when count(1) = count(numbers) then sum(numbers) when count(1) != count(numbers) then 0 end ) as SumNumbers, --总数量 (case when count(1) = count(WeightShow) then sum(WeightShow) when count(1) != count(WeightShow) then 0 end ) as SumWeightShow, --总重量 (case when count(1) = count(volume) then sum(volume) when count(1) != count(volume) then 0 end ) as SumVolume --总体积 from t_Bus_Order_Goods a group by OrderId
通过上面的SQL语句可以发现,为了拼接多个货物信息,用到了SQL中xml的相关知识。