SQL 查询舍入问题

Question

我正在使用pyodbc根据称为"强度"的特定标准获取产品的总量。对于某些记录，"强度"列还具有字符串值，因此它是varchar列。

用户输入详细信息，例如品牌、产品类型、产品线、日期范围、最小数量(在本例中为 12(和强度范围

这是我的查询：

SELECT SUM(CAST([Qty] AS decimal(10, 2))) AS Qty 
FROM (
SELECT 
[Brand], 
[ProdType], 
[Lot], 
CAST([Strength] AS DECIMAL(10,4)) AS [Strength], 
[ProductLine], 
[Size], 
[Stage], 
[Customer], 
[PackedOn], 
[Qty], 
[RefreshedBy], 
[RefreshedOn] 
FROM SalesData 
WHERE 
(isnumeric([Strength]) = 1) 
AND [Stage]='WIP' 
AND [PackedOn]>='2018-06-03' 
AND [PackedOn]<='2020-06-03' 
AND [Brand]='ABC' 
AND [ProductLine]='DEF' 
AND [Size]='15' 
AND [Qty]>='12.0' 
AND [Strength]>=0.2 
AND [Strength]<=0.4 
AND [ProdType] Is Null
) as outputdata

这是我的表：

ID  Brand   ProdType    Lot   Strength  ProductLine   Size    Stage   Province  PackedOn    Qty  
1   ABC     NULL      XXXXXXX     0.16       DEF       15        WIP    NULL    2018-12-07  1200

这是创建语句

CREATE TABLE [dbo].[SalesData](
[ID] [int] NOT NULL,
[Brand] [varchar](max) NOT NULL,
[ProdType] [varchar](max) NULL,
[Lot] [varchar](max) NOT NULL,
[Strength] [varchar](max) NOT NULL,
[ProductLine] [varchar](max) NOT NULL,
[Size] [varchar](max) NOT NULL,
[Stage] [varchar](max) NOT NULL,
[Province] [varchar](max) NULL,
[PackedOn] [date] NOT NULL,
[Qty] [float] NOT NULL,
[RefreshedBy] [varchar](max) NULL,
[RefreshedOn] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

我的问题是此查询导致数量为 1200，即使它超出了强度范围。我正在使用SQL Server Management Studio V 18.4。我该如何解决这个问题？

Answer 1

在你的 WHERE 子句中，你应该使用。

TRY_CAST([Strength] AS DECIMAL(10,4))>=0.2 AND TRY_CAST([Strength] AS DECIMAL(10,4))<=0.4

因为 sql 查询从 where clauses( 和 joins( 开始工作，然后执行其他部分。SELECT是最不重要的部分，如果您只在选择中使用CAST，则仅对打印数据作为首选格式有用。

SELECT SUM(CAST([Qty] AS decimal(10, 2))) AS Qty FROM 
(SELECT [Brand], [ProdType], [Lot], CAST([Strength] AS DECIMAL(10,4)) AS [Strength], [ProductLine], [Size], [Stage], [Customer], [PackedOn], [Qty], [RefreshedBy], [RefreshedOn] 
FROM SalesData 
WHERE (isnumeric([Strength]) = 1) AND [Stage]='WIP' AND [PackedOn]>='2018-06-03' 
AND [PackedOn]<='2020-06-03' AND [Brand]='ABC' AND [ProductLine]='DEF' 
AND [Size]='15' AND [Qty]>='12.0' AND TRY_CAST([Strength] AS DECIMAL(10,4))>=0.2 AND TRY_CAST([Strength] AS DECIMAL(10,4))<=0.4 AND [ProdType] Is Null) as outputdata

Answer 2

在进行数字比较之前需要CAST()，否则SQL Server比较的是字符串，而不是数字，这会导致意外的结果：例如，字符串方面，'2'大于'12'(因为它以'2'开头，大于'1'(查询中涉及的所有数字比较都是如此(Size也涉及(。

我建议TRY_CAST()，这样可以避免错误并在转换失败时返回null(这将有效地使条件失败，并从查询中删除相应的行(。

此外，子查询是不必要的。

考虑：

SELECT SUM(Qty) Qty
FROM SalesData 
WHERE 
Stage = 'WIP' 
AND PackedOn >= '2018-06-03' 
AND PackedOn <= '2020-06-03' 
AND Brand = 'ABC' 
AND ProductLine = 'DEF' 
AND ProdType Is Null
AND Qty >= 12
AND TRY_CAST(Strength AS DECIMAL(10, 4)) >= 0.2
AND TRY_CAST(Strength AS DECIMAL(10, 4)) <= 0.4 
AND TRY_CAST(Size AS INT) = 15

如果你想将你的float输出投射到decimal，在sum()之后更准确地说，所以：

SELECT CAST(SUM(Qty) AS DECIMAL(10, 2)) Qty
FROM ...