比较好的sql写法

DECLARE @beginTime VARCHAR(20)= '2017-11-13 00:00:00';
DECLARE @endTime VARCHAR(20)= '2017-11-13 23:00:00';
DECLARE @country VARCHAR(20)= '中国香港';
DECLARE @OrderFrom VARCHAR(20)= '0[4PX]';
DECLARE @TooWeight VARCHAR(20)= '0[小件]';

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 '一段入库异常单量';

转载于:https://www.cnblogs.com/chengjun/p/7842955.html