SQL 算术运算符

SQL Server 算术运算符用于对给定数据（操作数）执行算术运算，例如加法、减法、乘法和除法。因此，您可以使用这些算术运算符在关系数据库中的两个表达式或表列上执行数学运算。

通常，您可以在 SELECT 语句中使用这些 SQL 算术运算符对列执行操作以生成新列。此外，您可以在 WHERE 子句中使用它们来限制或过滤数据。

本文介绍了如何使用算术运算符对简单变量和表列执行数学运算。它还涵盖了提高性能、处理 NULL 值和除零错误的最佳实践。

SQL 算术运算符

以下是 SQL Server 中用于执行数学运算的五种可用算术运算符的列表。

  • 加法 (+)： 将两个数字相加
  • 减法 (-)：一个数减去另一个数。
  • 乘法 (*)：将两个数字相乘。
  • 除法 (/)：一个值除以另一个值。
  • 模数 (%)：返回除法后的余数。

从上述内容来看，+ 和 – 运算符都可用于 datetime 和 smalldatetime 数据类型，以将天数加到或从给定日期减去。

SQL 算术运算符语法

算术运算符的基本语法如下所示。

SELECT <expression> <arithmetic operator> <expression>
FROM Source
WHERE <condition>

上述语法的简化版本如下所示。

SELECT 
Col1 + Col2 AS Sum
Col1 – Col2 AS Difference
Col1 * Col2 AS Product
Col1 / Col2 AS Quotient
Col1 – Col2 AS Remainder
FROM TableName

SQL 算术运算符示例

在我们开始示例之前，下表显示了服务器中可用的算术运算符列表。

SQL 算术运算符示例
+ 加法DECLARE @A INT, @B INT
SET @A = 10
SET @B = 2
SELECT @A + @B AS Total = 12
– 减法SELECT @A – @B AS Total (10 – 2 = 8)
* 乘法SELECT @A * @B AS Total (10 * 2 = 20)
/ 除法SELECT @A / @B AS Total (10 / 2 = 5)
% 模数SELECT @A % @B AS Total =0 因为 10%2 = 0（此处余数为零）。如果为 10 % 3，则为 1。

在使用 SQL Server 算术运算符时，您必须记住一些事项，以避免可能遇到的一些罕见错误。

  • 在相乘大数值时，结果可能会超出数据类型的最大限制。因此，请始终使用 BIGINT 或 DECIMAL 来存储这些大数值。您可以使用 CAST 函数更改数据类型。
  • 使用浮点数可能会导致不准确的结果，因为输出会自动四舍五入。为了控制这一点，您可以使用 ROUND 或 CAST 函数将小数四舍五入到一位、两位或三位。

在此 SQL Server 算术运算符演示中，我们将使用一个简单的随机产品表，该表来自 AdventureWorksDW，包含 10 条订单和销售记录。

+（加法）运算符

加法在多种方式下都很有用，本节涵盖了所有可能性。如果我们对单个或多个列中的数值数据使用 SQL Server 算术运算符的加法，它将添加这些值并提供整数输出。例如，如果我们想增加每种产品的成本，那么我们可以使用此加法来添加一个常量值。您可以编写以下查询，通过在原始价格上加 50 来更新 TotalProductCost 的新值。

SELECT ProductName, TotalProductCost, TotalProductCost + 50 AS NewPrice
FROM ProductSale

上面的查询将为表中的每件产品增加 50 美元，并显示新的价格列表。算术运算符允许您添加两个或多个列，而不是添加静态值。

数值列上的算术运算符 (+)

让我们看看如何使用 + 运算符在 SQL Server 中对数值数据或整数列执行算术加法。例如，对两个或多个整数列执行加法以获得最终结果。

以下查询将相加销售金额和税款金额，以显示客户在购买时必须支付（或已支付）的总账单价格。

SELECT [ProductName], [Color], [DealerPrice]
   ,[SalesAmount] + [TaxAmt] AS [Total Amount]
FROM [ProductSale]

使用 SQL 算术运算符 (+) 连接两个字符串列

如果我们使用 +（加法）运算符在两个字符串或文本列之间，服务器将执行字符串连接并提供字符串输出。

例如，以下加法查询将连接 [EnglishProductName] 和 [Color] 字符串列以生成单个文本列作为输出。这里，我们在两个列之间使用了空格来分隔它们。但是，您可以尝试任何文本或符号来分隔产品名称和颜色。

SELECT [ProductName] + '  ' + [Color] AS [Product Name]
   ,[TotalProductCost],[DealerPrice], [SalesAmount], [TaxAmt]
FROM ProductSale

使用 + 运算符将天数加到日期和时间

除了上述优点，您还可以使用 SQL 算术 + 运算符将所需天数加到日期和时间值或列。通过添加标准的 7 天来估算订单日期的送货日期非常有用。

以下查询将 ProductSale 表中的每件产品增加 20 天，并显示新日期。

SELECT Color, OrderDate, OrderDate + 20 
FROM ProductSale

文本和整数数据类型的加法

与某些编程语言不同，SQL Server 无法执行隐式数据类型转换。因此，当您尝试将文本（字符）值和整数（数字）列或值相加时，SQL 算术运算符 (+) 将引发错误。

在以下查询中，两个 SELECT 语句将引发错误。

SELECT '100.50' + 200

SELECT 'Hello' + 200

–（减法）运算符

SQL Server 中的算术减法运算符类似于数学中的减号，用于一个数减去另一个数。我们可以将减法运算符应用于数值和日期，以从现有日期中减去给定天数。与加法运算符类似，您可以使用静态常量值或两列来执行减法。例如，下面的查询从每个产品价格中减去 1 美元。

SELECT ProductName, SalesAmount, SalesAmount - 1 AS DiscountPrice
FROM ProductSale

为产品应用折扣

当节日季临近时，商店愿意为所有产品提供从 10% 到 50% 甚至更高的折扣。假设我们想对表中所有产品应用 20% 的标准折扣。为此，您需要编写以下 SQL Server 算术运算符查询，其中包含乘法和减法。

SELECT ProductName, Color, SalesAmount, 
   SalesAmount - (SalesAmount * 0.20) AS DiscountPrice
FROM ProductSale

例如，以下查询将通过从经销商价格中减去总产品成本来计算所有者和经销商的利润率。

SELECT [ProductName],[Color],[TotalProductCost] ,[DealerPrice]
      ,[DealerPrice] - [TotalProductCost] AS [Owner Profit]
      ,[SalesAmount] - DealerPrice AS [Dealer Profit]
FROM [ProductSale]

日期减法

与加法类似，您可以使用此 SQL 算术减法运算符从现有日期中减去所需天数。例如，下面的查询将从订单日期列中减去 10 天。

SELECT ProductName, Color, OrderDate, 
   OrderDate - 10 AS EstimatedDate
FROM ProductSale

除了以上之外，您还可以使用减法运算符来检查自上次销售以来经过的天数。理解业务非常重要。为了使查询尽可能简单，我们计算年份差异。

SELECT ProductName, Color, OrderDate, 
   YEAR(GETDATE()) - YEAR(OrderDate) AS Years
FROM ProductSale
SQL Arithmetic Addition and Subtraction Operators

*（乘法）运算符

SQL 算术乘法运算符用于将一个值乘以另一个值，它可以是两列或任何常量值。例如，如果我们想使成功项目（如 AI）的员工的薪水翻倍，您只需将其年薪乘以 2 即可。

SELECT Name, Age, Email, Salary, Salary * 2 AS NewSalary
FROM employees

如果我们查看表格，我们会发现每种产品的销售金额和总订单数量。要计算每种产品的总销售额，您只需将 SalesAmount 和 OrderQuantity 相乘即可得到结果。

SELECT ProductName, Color, [OrderQuantity],[SalesAmount], 
   [OrderQuantity] * [SalesAmount] AS TotalSale
FROM ProductSale

在此 SQL Server 示例中，我们将原始销售额乘以订单量。

有些情况下，我们需要提高一些表现良好的产品的价格以增加利润。在这种情况下，您可以使用以下查询

SELECT ProductName, Color, OrderQuantity, TotalProductCost, 
   TotalProductCost * 1.5 AS NewPrice
FROM ProductSale
WHERE OrderQuantity > 50

/（除法）运算符

SQL Server 算术除法运算符用于对两个操作数执行数学除法并返回商。如果被除数和除数都是整数值，即使有任何小数位，结果也将成为整数，并且会被自动截断。

例如，以下除法运算符查询将计算我们为每种产品支付的税款百分比。为此，我们将销售金额除以税款金额。

SELECT [ProductName],[Color],[TotalProductCost] 
      ,[DealerPrice],[SalesAmount],[TaxAmt]
      ,[SalesAmount] / [TaxAmt] AS [Tax Percent]
  FROM [ProductSale]

在此示例中，标准可能是 12.50，但实际上，税率因州而异。

%（模数）运算符

SQL Server 算术模数运算符用于执行涉及将一个值除以另一个值并获得余数的数学运算。例如，您可以使用模数运算符检查列值是偶数还是奇数。任何数值除以 2 的余数如果是偶数则为零；否则为奇数。

如果您有带有 ID 值的 Employee 或 Customer 表，您可以使用以下查询打印 CustID 为偶数的客户。

SELECT CustID, Name, Email FROM customers
WHERE CustID % 2 = 0;

我将使用这个 % 模数运算符来检查产品订单是发生在周末还是工作日。利用这些信息，我们可以将更多资源集中在那些特定的日子以增加销售额。

以下查询使用 CASE 语句，并且在第一个 CASE 中

  • DATEPART(WEEKDAY, OrderDate)：DATEPART 函数将检索从 1 到 7 的日期编号，其中 1 是星期日。
  • 接下来，我们将该数字除以 7。
  • IN 运算符检查余数是否为 6 或 7。
  • 如果为 True，则为周末。否则，为工作日。
SELECT [ProductName],[Color], SalesAmount, OrderDate,
CASE 
	WHEN DATEPART(WEEKDAY, OrderDate) % 7 IN (6, 7) THEN 'Weekend'
	ELSE 'Weekday'
END AS OrderDay
FROM ProductSale;
SQL Arithmetic Multiplication, Division, and Modulus Operators

SQL 算术运算符如何处理 NULL 值？

当您使用上述任何算术运算符对 NULL 值执行算术运算时，结果将为 NULL。请记住，NULL 是唯一的空值，它们是空白或零。例如，以下所有查询都将返回 NULL 作为结果。

SELECT NULL + 10;
SELECT 10 - NULL;
SELECT 20 * NULL;
SELECT 30 % NULL;
SELECT 40 / NULL;

在实际情况中，可能有一些记录包含 NULL 值，在执行算术运算时，您必须妥善处理它们。否则，结果集将为 NULL，而它们是正确的值。SQL Server 提供了多种选项来处理算术运算中使用这些运算符的 NULL 值。

COALESCE 函数

COALESCE 函数将从给定参数中选择非 NULL 值并使用它来执行计算。在下面的查询中，我们使用 COALESCE 函数将 NULL 值替换为零或一。

SELECT COALESCE(NULL, 0) + 10;
SELECT 10 - COALESCE(NULL, 0);
SELECT 20 * COALESCE(NULL, 1);
SELECT 30 / COALESCE(NULL, 1);

输出为

10
10
20
30

ISNULL 函数

与上述函数类似，您可以根据需要使用 ISNULL 函数将 NULL 值替换为零或一。

SELECT ISNULL(NULL, 0) + 70;
SELECT 50 - ISNULL(NULL, 0);
SELECT 20 * ISNULL(NULL, 1);
SELECT 90 / ISNULL(NULL, 1);

输出为

70
50
20
90

SQL 算术运算符：除以零错误

在使用 SQL 算术运算符时，尤其是在执行除法或模运算时，可能会遇到的一个常见问题是除以零。在某些情况下，我们可能会遇到零（错误输入）或没有销售。当我们将总销售额除以包含某些产品零的订单时，它将引发错误。要解决此问题，您可以使用 CASE 语句或 NULLIF() 函数来避免这些错误。

我们声明了两个整数类型的变量，@b 为零。当您用 @a 除它时，它会抛出错误。因此，我们使用 CASE 检查 @b 是否为 NULL。如果为真，则将其替换为 NULL，任何值除以 NULL 都为 NULL。

DECLARE @a INT, @b INT
   SET @a = 10
  SET @b = 0
SELECT 
 CASE WHEN @b = 0 THEN NULL
   ELSE @a / @b
 END AS Output

另一个选项是 NULLIF 函数，以下查询将显示相同的内容。

SELECT 10 / NULLIF(0, 0);

SQL 算术运算符的优先级

Microsoft SQL Server 在使用算术运算符执行数学计算时遵循某些优先级规则。如果语句包含多个运算符，则评估过程遵循一组规则。例如，（+ 和 * 组合）先乘法，然后加法或减法。

提示：我们可以使用括号来覆盖评估过程，方法是预先定义它们。

以下是执行顺序的列表。

  1. 括号：其中的表达式首先执行。
  2. 乘法和除法：两者具有相同的优先级。如果没有括号，这些运算符将首先执行。但是，如果同时存在 (* 和 /)，它将遵循从左到右的顺序。
  3. 加法和减法：两者具有相同的优先级，并在上述两个之后执行。如果同时存在 + 和 –，它将遵循从左到右的顺序。

下表通过简单示例向您展示了 SQL Server 算术运算符在实际操作中的执行顺序。

代码结果解释
SELECT 10 + 5 * 325第一步：乘法 (5 * 3)。第二步：加法 10 + 15
SELECT 10 + 20 / 415第一步：除法 (20 / 4 = 5) 第二步：10 + 5 = 15
SELECT 5 * 3 + 20 / 420两者具有相同的优先级。因此，从左到右：第一步：乘法 (5 * 3 = 15) 第二步：除法 (20 / 4 = 5) 第三步：15 + 5 = 20
SELECT (10 + 5) * 460因为括号先执行，所以 10 + 5 = 15。第二步：15 * 4 = 60。
SELECT (20 + 10) * 4 / 5 – 420第一步：(20 + 10) = 30 第二步：30 * 4 = 120 第三步：120 / 5 = 24 第四步：24 – 4 = 20