好的SQL写法

DECLARE @beginTime VARCHAR(20)= '2017-12-20 00:00:00';
DECLARE @endTime VARCHAR(20)= '2017-12-26 00:00:00';
DECLARE @country VARCHAR(20)= '';
DECLARE @OrderFrom VARCHAR(20)= '';
DECLARE @TooWeight VARCHAR(20)= '';
SELECT @beginTime + '~' + @endTime AS '日期+时间' ,
'15号仓' AS '实体仓' ,
@country AS '国家地区' ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportFromMst a WITH ( NOLOCK )
WHERE a.Country = @country
AND a.CreateTime >= @beginTime
AND a.CreateTime < @endTime
AND a.DeleteUser = 0
AND a.TooWeight = CAST(SUBSTRING(@TooWeight, 0,
CHARINDEX('[', @TooWeight)) AS INT)
AND a.OrderFrom = CAST(SUBSTRING(@OrderFrom, 0,
CHARINDEX('[', @OrderFrom)) AS INT)
) AS '一段预报单量' ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportFromMst a WITH ( NOLOCK )
WHERE a.Country = @country
AND a.ReceivedTime >= @beginTime
AND a.ReceivedTime < @endTime
AND a.DeleteUser = 0
AND a.TooWeight = CAST(SUBSTRING(@TooWeight, 0,
CHARINDEX('[', @TooWeight)) AS INT)
AND a.OrderFrom = CAST(SUBSTRING(@OrderFrom, 0,
CHARINDEX('[', @OrderFrom)) AS INT)
) AS '一段签收单量' ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportFromMst a WITH ( NOLOCK )
WHERE a.Country = @country
AND a.RejectTime >= @beginTime
AND a.RejectTime < @endTime
AND a.DeleteUser = 0
AND a.TooWeight = CAST(SUBSTRING(@TooWeight, 0,
CHARINDEX('[', @TooWeight)) AS INT)
AND a.OrderFrom = CAST(SUBSTRING(@OrderFrom, 0,
CHARINDEX('[', @OrderFrom)) AS INT)
) AS '一段签收异常单量' ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportFromMst a WITH ( NOLOCK )
WHERE a.Country = @country
AND a.DomesticWarehouseInTime >= @beginTime
AND a.DomesticWarehouseInTime < @endTime
AND a.DeleteUser = 0
AND a.TooWeight = CAST(SUBSTRING(@TooWeight, 0,
CHARINDEX('[', @TooWeight)) AS INT)
AND a.OrderFrom = CAST(SUBSTRING(@OrderFrom, 0,
CHARINDEX('[', @OrderFrom)) AS INT)
) AS '一段入库单量' ,
( SELECT COUNT(0)
FROM dbo.TWX_TransportFromMst a WITH ( NOLOCK )
WHERE a.Country = @country
AND a.DomesticWarehouseInTime >= @beginTime
AND a.DomesticWarehouseInTime < @endTime
AND a.DeleteUser = 0
AND a.StatusFlag = 11
AND a.TooWeight = CAST(SUBSTRING(@TooWeight, 0,
CHARINDEX('[', @TooWeight)) AS INT)
AND a.OrderFrom = CAST(SUBSTRING(@OrderFrom, 0,
CHARINDEX('[', @OrderFrom)) AS INT)
) AS '一段入库异常单量';

--------------------------------------------------------------

SELECT
mailNo,
CASE
WHEN catalogName IS NULL
OR catalogName LIKE '%其他%'
OR catalogName LIKE '%用品%'
OR catalogName LIKE '%邮费%' THEN
productName
ELSE
catalogName
END catalogName,
itemName,
itemNo,
hsCode,
TRUNCATE (
temp.Weight * temp.productNum / SUM(temp.productNum),
2
) AS weight,
(
TRUNCATE (
temp.Weight * temp.productNum / SUM(temp.productNum),
2
)
) - 0.01 AS netWt,
productNum AS num,
TRUNCATE (TRUNCATE (price, 2) * productNum, 2) AS total,
TRUNCATE (price, 2) AS price,
currency,
CASE temp.unit
WHEN '千克' THEN
temp.Weight * temp.productNum / SUM(temp.productNum)
WHEN '百个' THEN
CONVERT (
temp.productNum / 100.0,
SIGNED
)
ELSE
productNum
END qty,
unit,
unit1,
CASE temp.unit1
WHEN '千克' THEN
temp.Weight * temp.productNum / SUM(temp.productNum)
WHEN '百个' THEN
CONVERT (productNum / 100.0, SIGNED)
ELSE
productNum
END qty1,
unit1 AS unit2,
CASE temp.unit1
WHEN '千克' THEN
temp.Weight * temp.productNum / SUM(temp.productNum)
WHEN '百个' THEN
CONVERT (productNum / 100.0, SIGNED)
ELSE
productNum
END qty2,
'' AS giftFlag,
'' AS note,
'' AS gnum,
'' AS orderNo,
productName
FROM
(
SELECT
b.end_mail_no AS mailNo,
IFNULL(
MAX(
substring_index(
d.item_category_name,
'-' ,- 1
)
),
MAX(d.item_name)
) AS catalogName,
e.item_name AS itemName,
e.item_no AS itemNo,
e.g_code AS hsCode,
CASE
WHEN SUM(c.weight) < 0.02 THEN
0.02
ELSE
SUM(c.weight)
END AS weight,
d.item_quantity AS quantity,
(
SUM(d.total_item_actual_price) * 0.1498
) / SUM(d.item_quantity) AS price,
'美元' AS currency,
SUM(d.item_quantity) AS productNum,
e.unit,
e.unit1,
SUM(d.item_quantity) AS qty1,
e.unit2,
SUM(d.item_quantity) AS qty2,
d.item_name AS productName
FROM
prealert_order_carriage a
LEFT JOIN prealert_end_delivery_info b ON a.id = b.order_carriage_id
LEFT JOIN prealert_package c ON a.id = c.order_carriage_id
LEFT JOIN prealert_order_item d ON c.id = d.package_id
LEFT JOIN sys_category_customs e ON d.second_category_id = e.category_id
WHERE
a.id = 284985127406145536
GROUP BY
b.end_mail_no,
e.item_name,
e.item_no,
e.g_code,
d.item_quantity,
e.unit,
e.unit1,
e.unit2,
d.item_name
) temp
GROUP BY
mailNo,
catalogName,
itemName,
itemNo,
hsCode,
quantity,
unit,
unit1

巴特西

好的SQL写法

最新文章

热门文章