mssql 函数STUFF 的用法
DECLARE @limitDay INT;
SET @limitDay = 92;
IF DATEDIFF(DAY, '2017-12-13 00:00:00', '2017-12-13 18:00:00') > @limitDay
BEGIN
RETURN;
END;
SELECT TransportOrderCode AS '转运单号' ,
'taobao' AS '发货人' ,
'G/F.NO.167-169 HOI BUN ROAD, KWUN TONG KOWLOON, HONGKONG' AS '发货人地址' ,
TrueName AS '收人货人姓名' ,
District + ' ' + Adrress AS '收货人地址' ,
ZipCode AS '收货人邮编' ,
Mobile AS '收货人电话' ,
dbo.Fun_GetLogisticsTransferDtlCount(a.logisticsTransferDtlCodeEx) AS 包裹数 ,
a.TotalWeight + a.TotalWeightX AS '重量' ,
dbo.Fun_GetTransportDeclareProductCatalogNameEn(a.TransportOrderID) AS '品名' ,
STUFF(( SELECT ' | ' + ProductName
FROM TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TransportFormMstID IN (
SELECT TransportFormMstID
FROM TWX_TransportFromMst WITH ( NOLOCK )
WHERE TransportOrderID = a.TransportOrderID )
FOR
XML PATH('')
), 1, 1, '') AS 商品名称 ,
STUFF(( SELECT ' | ' + ProductCatalogName
FROM TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TransportFormMstID IN (
SELECT TransportFormMstID
FROM TWX_TransportFromMst WITH ( NOLOCK )
WHERE TransportOrderID = a.TransportOrderID )
FOR
XML PATH('')
), 1, 1, '') AS 商品类目 ,
a.DeclareValue AS '价格' ,
( SELECT LogisticsTransferDtlCode
FROM TWX_LogisticsTransferDtl WITH ( NOLOCK )
WHERE LogisticsTransferDtlID = a.LogisticsTransferDtlID
) AS '袋号' ,
'PP' AS 'CC/PP' ,
0 AS 'CC CHARGE (USD)'
FROM TWX_TransportOrder AS a WITH ( NOLOCK )
LEFT JOIN dbo.TWX_LogisticsTransferMst b WITH ( NOLOCK ) ON a.LogisticsTransferMstID = b.LogisticsTransferMstID
WHERE Country = '新加坡'
AND a.InterDeliveryID = 85
AND a.WarehouseOutTime >= '2017-12-10 00:00:00'
AND a.WarehouseOutTime <= '2017-12-13 18:00:00';
posted on 2017-12-14 10:05 chengjunde 阅读(1909) 评论(0) 编辑 收藏 举报