在 PostgreSQL 中使用 CASE

Shihab Sikder 2024年2月16日
  1. 在 PostgreSQL 中如何使用 CASE 语句
  2. PostgreSQL 中 CASE 语句的一般示例
  3. 带有聚合函数的 PostgreSQL CASE 语句
在 PostgreSQL 中使用 CASE

本文展示了如何在 PostgreSQL 中使用 CASE 语句。

在 PostgreSQL 中如何使用 CASE 语句

case 语句类似于通用编程语言中的 if-else。但是在 SQL 中,如果你想写 IF-ELSE,你可能需要 PL/SQL

在 PostgreSQL 中,有使用 case 表达式的内置功能。

例如,假设你有以下数据库。

postgres=# \d accounts;
                                          Table "public.accounts"
  Column   |            Type             | Collation | Nullable |                  Default
-----------+-----------------------------+-----------+----------+-------------------------------------------
 user_id   | integer                     |           | not null | nextval('accounts_user_id_seq'::regclass)
 username  | character varying(50)       |           | not null |
 password  | character varying(50)       |           | not null |
 email     | character varying(255)      |           | not null |
 contact   | character varying(20)       |           |          |
 postcode  | integer                     |           |          |
 age       | integer                     |           |          |
 height    | integer                     |           |          |
 timestamp | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (user_id)
    "accounts_email_key" UNIQUE CONSTRAINT, btree (email)
    "accounts_username_key" UNIQUE CONSTRAINT, btree (username)

如果要创建上面的表,下面是 SQL 命令。

CREATE TABLE accounts (
 user_id serial PRIMARY KEY,
 username VARCHAR ( 50 ) UNIQUE NOT NULL,
 password VARCHAR ( 50 ) NOT NULL,
 email VARCHAR ( 255 ) UNIQUE NOT NULL,
 contact VARCHAR (20),
 postcode INT,
 age INT,
 height INT,
 timestamp timestamp default current_timestamp
);

之后,使用脚本填充表格;你可以使用插入命令。

PostgreSQL 中 CASE 语句的一般示例

例如,你被要求根据高度来划分账户。如果身高小于 140 厘米,则为;如果介于 140 和 160 之间,则平均和 160 或更高将被标记为更高

PostgreSQL 的 case 表达式的一般结构如下所示。

SELECT <col1>...
    CASE
        WHEN <condition_1> THEN <result_1>
        WHEN <condition_2> THEN <result_2>
        ....
        ...
        WHEN <condition> THEN <result>
        ELSE <else_result>
    END <column_name_of_result>
FROM <table_name>

WHEN 中,我们可以使用 ANDOR 的组合来使用通常的条件。因此,SQL 命令将在下面。

SELECT username, email, height,
CASE
    WHEN height < 140 THEN 'SHORT'
    WHEN height >140 AND height<160 THEN 'AVERAGE'
    ELSE 'TALLER'
END height_group
FROM accounts;

输出:

 username |      email       | height | height_group
----------+------------------+--------+--------------
 RNYAvQR  | xgsgla@gmail.com |    150 | AVERAGE
 djIlNbP  | gfrqiy@gmail.com |    188 | TALLER
 vKUEtyK  | lffemk@gmail.com |    160 | TALLER
 OkGRtRA  | niwjri@gmail.com |    190 | TALLER
 jRnoRDI  | kofdcl@gmail.com |    163 | TALLER
 czkarSx  | qbqhyh@gmail.com |    186 | TALLER
 GGFcCrz  | tcbkip@gmail.com |    193 | TALLER
 cfVgeZE  | vpodqb@gmail.com |    161 | TALLER
 XRivfYx  | ldnnfg@gmail.com |    134 | SHORT
 WUKNQYe  | kvirum@gmail.com |    200 | TALLER
 lwQODTo  | apqvgc@gmail.com |    160 | TALLER
 eYyvVNu  | tsrioo@gmail.com |    190 | TALLER
 CLFFGcF  | nlpiuu@gmail.com |    131 | SHORT
 VhxqxTl  | hwrzao@gmail.com |    173 | TALLER
 ---- more ---

带有聚合函数的 PostgreSQL CASE 语句

我们还可以使用 CASE 表达式编写更复杂的查询。例如,我们希望将帐户划分为这样的组,其中:

  1. 年龄 13-19 岁,身高 140-160 之间,就是 Teens with average height
  2. 年龄 20-40 岁,身高 165-175 之间,就是 Adults with average height

我们要计算这两个类别下的帐户总数。SQL 命令将如下所示。

SELECT
    SUM(
        CASE
            WHEN age>=13 AND age<=19 THEN 1
            ELSE 0
            END
    ) AS "Teens with average height",
    SUM(
        CASE
            WHEN age>=20 AND age<=40 THEN 1
            ELSE 0
            END
    ) AS "Adults with average height",
    COUNT(*) as total_accounts
FROM
accounts;

输出:

 Teens with average height | Adults with average height | total_accounts
---------------------------+----------------------------+----------------
                        27 |                         84 |            200
(1 row)

在每种情况下,如果满足条件,则返回 1,否则返回 0。然后聚合函数 SUM 将所有 1 或 true 条件相加。

Count(*) 对账户表中的总行进行计数。

这些类型的表达式将帮助你在将来构建自定义聚合函数,你可以在其中相应地放置你的条件。你可以从此处了解更多信息。

作者: Shihab Sikder
Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website