什么是 SQL 计算字段,如何创建和使用 SQL 计算字段

本文介绍什么是计算字段,如何创建计算字段,我们用例子说明了计算字段在字符串拼接和算术计算中的用途。以及如何从应用程序中使用别名引用它们。

一、计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式,下面举几个例子。

  • 需要显示公司名,同时还需要显示公司的地址,但这两个信息存储在不同的表列中。
  • 城市、州和邮政编码存储在不同的列中(应该这样),但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
  • 列数据是大小写混合的,但报表程序需要把所有数据按大写表示出来。
  • 物品订单表存储物品的价格和数量,不存储每个物品的总价格(用价格乘以数量即可)。但为打印发票,需要物品的总价格。
  • 需要根据表数据进行诸如总数、平均数的计算。

在上述每个例子中,存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户端应用程序中重新格式化。

这就是计算字段可以派上用场的地方了。计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。

需要特别注意,只有数据库知道 SELECT 语句中哪些列是实际的表列,哪些列是计算字段。从客户端(如应用程序)来看,计算字段的数据与其他列的数据的返回方式相同。

二、拼接字段

为了说明如何使用计算字段,我们来举一个简单例子,创建由两列组成的标题。

Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表,需要在格式化的名称(位置)中列出供应商的位置。

此报表需要一个值,而表中数据存储在两个列 vend_namevend_country 中。此外,需要用括号将 vend_country 括起来,这些东西都没有存储在数据库表中。

这个返回供应商名称和地址的 SELECT 语句很简单,但我们是如何创建这个组合值的呢?

解决办法是把两个列拼接起来。在 SQL 中的 SELECT 语句中,可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS,此操作符可用加号(+)或两个竖杠(||)表示。

在 MySQL 和 MariaDB 中,必须使用特殊的函数。

下面是使用加号的例子(多数 DBMS 使用这种语法):

1
2
3
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

输出:

1
2
3
4
5
6
7
-----------------------------------------------------------
Bear Emporium                                (USA        )
Bears R Us                                   (USA        )
Doll House Inc.                              (USA        )
Fun and Games                                (England    )
Furball Inc.                                 (USA        )
Jouets et ours                               (France     )

下面是相同的语句,但使用的是 || 语法:

1
2
3
SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

输出:

1
2
3
4
5
6
7
-----------------------------------------------------------
Bear Emporium                                (USA        )
Bears R Us                                   (USA        )
Doll House Inc.                              (USA        )
Fun and Games                                (England    )
Furball Inc.                                 (USA        )
Jouets et ours                               (France     )

下面是使用 MySQL 或 MariaDB 时需要使用的语句:

1
2
3
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

上面两个 SELECT 语句拼接以下元素:

  • 存储在 vend_name 列中的名字;
  • 包含一个空格和一个左圆括号的字符串;
  • 存储在 vend_country 列中的国家;
  • 包含一个右圆括号的字符串。

从上述输出中可以看到,SELECT 语句返回包含上述四个元素的一个列(计算字段)。

再看看上述 SELECT 语句返回的输出。结合成一个计算字段的两个列用空格填充。许多数据库(不是所有)保存填充为列宽的文本值,而实际上你要的结果不需要这些空格。

为正确返回格式化的数据,必须去掉这些空格。这可以使用 SQL 的 RTRIM() 函数来完成,如下所示:

1
2
3
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

输出:

1
2
3
4
5
6
7
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

下面是相同的语句,但使用的是 ||

1
2
3
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name;

输出:

1
2
3
4
5
6
7
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

RTRIM() 函数去掉值右边的所有空格。通过使用 RTRIM(),各个列都进行了整理。

2.1 使用别名

从前面的输出可以看到,SELECT 语句可以很好地拼接地址字段。但是,这个新计算列的名字是什么呢?实际上它没有名字,它只是一个值。

如果仅在 SQL 查询工具中查看一下结果,这样没有什么不好。但是,一个未命名的列不能用于客户端应用中,因为客户端没有办法引用它。

为了解决这个问题,SQL 支持列别名。别名(alias)是一个字段或值的替换名。别名用 AS 关键字赋予。请看下面的 SELECT 语句:

1
2
3
4
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
 AS vend_title
FROM Vendors
ORDER BY vend_name;

输出:

1
2
3
4
5
6
7
8
vend_title
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

下面是相同的语句,但使用的是 || 语法:

1
2
3
4
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
 AS vend_title
FROM Vendors
ORDER BY vend_name;

下面是 MySQL 和 MariaDB 中使用的语句:

1
2
3
4
SELECT Concat(RTrim(vend_name), ' (',
       RTrim(vend_country), ')') AS vend_title
FROM Vendors
ORDER BY vend_name;

SELECT 语句本身与以前使用的相同,只不过这里的计算字段之后跟了文本 AS vend_title

它指示 SQL 创建一个包含指定计算结果的名为 vend_title 的计算字段。

从输出可以看到,结果与以前的相同,但现在列名为 vend_title,任何客户端应用都可以按名称引用这个列,就像它是一个实际的表列一样。

三、执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算。举个例子,Orders 表包含收到的所有订单,OrderItems 表包含每个订单中的各项物品。

下面的 SQL 语句检索订单号 20008 中的所有物品:

1
2
3
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

输出:

1
2
3
4
5
6
7
prod_id        quantity        item_price
----------     -----------     ---------------------
RGAN01         5               4.9900
BR03           5               11.9900
BNBG01         10              3.4900
BNBG02         10              3.4900
BNBG03         10              3.4900

item_price 列包含订单中每项物品的单价。如下汇总物品的价格(单价乘以订购数量):

1
2
3
4
5
6
SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

输出:

1
2
3
4
5
6
7
prod_id        quantity        item_price       expanded_price
----------     -----------     ------------     -----------------
RGAN01         5               4.9900           24.9500
BR03           5               11.9900          59.9500
BNBG01         10              3.4900           34.9000
BNBG02         10              3.4900           34.9000
BNBG03         10              3.4900           34.9000

输出中显示的 expanded_price 列是一个计算字段,此计算为 quantity*item_price。客户端应用现在可以使用这个新计算列,就像使用其他列一样。

SQL 支持表 1 中列出的基本算术操作符。此外,圆括号可用来区分优先顺序。关于优先顺序的介绍,请参阅 如何使用 SQL AND、OR、IN 和 NOT 过滤返回的数据

表 1 SQL 算术操作符

操作符说明
+
-
*
/

四、小结

本文介绍了计算字段以及如何创建计算字段。我们用例子说明了计算字段在字符串拼接和算术计算中的用途。此外,还讲述了如何创建和使用别名,以便应用程序能引用计算字段。

(完)