3. 创建计算字段

本节使用的表

vendors

postgres=# \d vendors

                    Table "public.vendors"
    Column    |     Type      | Collation | Nullable | Default
--------------+---------------+-----------+----------+---------
 vend_id      | character(10) |           | not null |
 vend_name    | character(50) |           | not null |
 vend_address | character(50) |           |          |
 vend_city    | character(50) |           |          |
 vend_state   | character(5)  |           |          |
 vend_zip     | character(10) |           |          |
 vend_country | character(50) |           |          |
Indexes:
    "vendors_pkey" PRIMARY KEY, btree (vend_id)
Referenced by:
    TABLE "products" CONSTRAINT "fk_products_vendors" FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)

SELECT
    *
FROM
    vendors;

 vend_id |  vend_name    | vend_address      | vend_city  | vend_state | vend_zip | vend_country
---------+---------------+-------------------+------------+------------+----------+------------------
 BRS01   | Bears R Us    | 123 Main Street   | Bear Town  | MI         | 44444    | USA
 BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA
 DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA
 FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA
 FNG01   | Fun and Games   | 42 Galaxy Road  | London     |            | N16 6PS  | England
 JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      |            | 45678    | France
(6 rows)

orderitems

postgres=# \d orderitems

                  Table "public.orderitems"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_item | integer       |           | not null |
 prod_id    | character(10) |           | not null |
 quantity   | integer       |           | not null |
 item_price | numeric(8,2)  |           | not null |
Indexes:
    "orderitems_pkey" PRIMARY KEY, btree (order_num, order_item)
Foreign-key constraints:
    "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)
    "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)

SELECT
    *
FROM
    orderitems;

 order_num | order_item |  prod_id   | quantity | item_price
-----------+------------+------------+----------+------------
     20005 |          1 | BR01       |      100 |       5.49
     20005 |          2 | BR03       |      100 |      10.99
     20006 |          1 | BR01       |       20 |       5.99
     20006 |          2 | BR02       |       10 |       8.99
     20006 |          3 | BR03       |       10 |      11.99
     20007 |          1 | BR03       |       50 |      11.49
     20007 |          2 | BNBG01     |      100 |       2.99
     20007 |          3 | BNBG02     |      100 |       2.99
     20007 |          4 | BNBG03     |      100 |       2.99
     20007 |          5 | RGAN01     |       50 |       4.49
     20008 |          1 | RGAN01     |        5 |       4.99
     20008 |          2 | BR03       |        5 |      11.99
     20008 |          3 | BNBG01     |       10 |       3.49
     20008 |          4 | BNBG02     |       10 |       3.49
     20008 |          5 | BNBG03     |       10 |       3.49
     20009 |          1 | BNBG01     |      250 |       2.49
     20009 |          2 | BNBG02     |      250 |       2.49
     20009 |          3 | BNBG03     |      250 |       2.49
(18 rows)

拼接字段

SELECT
    vend_name || ' (' || vend_country || ' )' 
FROM
    vendors 
ORDER BY
    vend_name;

         ?column?
--------------------------
 Bear Emporium (USA)
 Bears R Us (USA)
 Doll House Inc. (USA)
 Fun and Games (England)
 Furball Inc. (USA)
 Jouets et ours (France)
(6 rows)

这个新计算列没有名字，它只是一个值。一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

为了解决这个问题，SQL支持列别名。

SELECT
    vend_name || ' (' || vend_country || ' )' AS vendor_title 
FROM
    vendors 
ORDER BY
    vend_name;

      vendor_title
-------------------------
 Bear Emporium (USA)
 Bears R Us (USA)
 Doll House Inc. (USA)
 Fun and Games (England)
 Furball Inc. (USA)
 Jouets et ours (France)
(6 rows)

执行算术计算

SELECT
    prod_id,
    quantity,
    item_price,
    quantity * item_price AS expanded_price 
FROM
    orderitems 
WHERE
    order_num = 20008;

  prod_id   | quantity | item_price | expanded_price
------------+----------+------------+----------------
 RGAN01     |        5 |       4.99 |          24.95
 BR03       |        5 |      11.99 |          59.95
 BNBG01     |       10 |       3.49 |          34.90
 BNBG02     |       10 |       3.49 |          34.90
 BNBG03     |       10 |       3.49 |          34.90
(5 rows)