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语句

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，即使它超出强度范围。我正在使用SQLServerManagementStudio v18.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子句（和联接）开始工作，然后执行其他部分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 ...