SQL 查询实战

-- 归档表：演示数据修改 CTE 时使用（orders 的"退役"版本，无生成列）
CREATE TABLE orders_archive (
    id         bigint PRIMARY KEY,
    order_no   text NOT NULL,
    user_id    bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity   int NOT NULL,
    unit_price numeric(12,2) NOT NULL,
    status     order_status NOT NULL,
    paid_at    timestamptz,
    created_at timestamptz NOT NULL,
    archived_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (id, email, nickname, password_hash, referrer_id, balance)
OVERRIDING SYSTEM VALUE VALUES
    (1, 'alice@shop.example', '爱丽丝', 'hash_a', NULL, 100.00),
    (2, 'bob@shop.example',   '鲍勃',   'hash_b', 1,    0.00),
    (3, 'carol@shop.example', NULL,     'hash_c', 1,    50.00),
    (4, 'dave@shop.example',  '戴夫',   'hash_d', NULL, 0.00);
-- 注意：referrer_id 这一列存在 NULL（1、4 号为空），后面 NOT IN 的坑全靠它
-- carol 昵称为 NULL 是 LEFT JOIN / 聚合章节的演示素材

INSERT INTO products (id, title, price, stock, tags, attrs) OVERRIDING SYSTEM VALUE VALUES
    (1, '机械键盘',    399.00, 50, ARRAY['外设','输入设备'], '{"color":"黑","switch":"红轴"}'),
    (2, '无线鼠标',     89.90, 120, ARRAY['外设','输入设备'], '{"color":"白"}'),
    (3, '27寸显示器', 1299.00, 8,  ARRAY['显示设备'],         '{"size":"27","res":"4K"}'),
    (4, 'USB-C 扩展坞',199.00, 0,  ARRAY['外设','接口'],      '{}'),
    (5, '电竞椅',      899.00, 5,  ARRAY['家具'],            '{"material":"网布"}');

INSERT INTO orders (id, order_no, user_id, product_id, quantity, unit_price, status, paid_at, created_at)
OVERRIDING SYSTEM VALUE VALUES
    (1, 'ORD20261001001', 1, 1, 1,  399.00, 'paid',      '2026-10-01 10:30+08', '2026-10-01 10:00+08'),
    (2, 'ORD20261002002', 1, 2, 2,   89.90, 'shipped',   '2026-10-02 09:10+08', '2026-10-02 09:00+08'),
    (3, 'ORD20261005003', 2, 3, 1, 1299.00,'paid',       '2026-10-05 15:20+08', '2026-10-05 15:00+08'),
    (4, 'ORD20260815004', 2, 2, 1,   89.90, 'pending',   NULL,                  '2026-08-15 20:00+08'),
    (5, 'ORD20261010005', 3, 5, 1,  899.00, 'pending',   NULL,                  '2026-10-10 11:00+08'),
    (6, 'ORD20261003006', 1, 4, 1,  199.00, 'cancelled', NULL,                  '2026-10-03 18:00+08'),
    (7, 'ORD20260920007', 2, 1, 3,  399.00, 'completed', '2026-09-20 12:00+08', '2026-09-20 11:00+08');
-- 格局：alice 3 单（paid/shipped/cancelled）、bob 3 单（含 8 月 15 日的过期 pending）、
-- carol 只有 1 单且未支付、dave 一单没有 —— LEFT JOIN 与 NOT EXISTS 的素材都在这。
-- 本章多数例子的"当前时间"按 2026-10-20 前后理解。

BEGIN;

-- 插入并拿回主键、生成列算出的金额、落库时间
INSERT INTO orders (order_no, user_id, product_id, quantity, unit_price)
VALUES ('ORD20261020008', 4, 2, 1, 89.90)
RETURNING id, order_no, total_amount, created_at;
-- total_amount 是生成列：不用给值也直接读回（89.90 × 1 = 89.90）

-- 批量改状态，直接知道改了谁
UPDATE orders SET status = 'shipped'
WHERE order_no IN ('ORD20261005003')
RETURNING id, status, updated_at;

-- 删除并留底（先查后删有竞态窗口，DELETE ... RETURNING 删到的是真实被删的行）
DELETE FROM orders WHERE status = 'cancelled' AND quantity = 0
RETURNING id, order_no;
-- 种子数据里 cancelled 的单 quantity 是 1，谓词不匹配——RETURNING 无输出正好说明"没删到东西"

-- 更新时 PG 特有的 UPDATE ... FROM（多表更新没有标准语法，PG 用 FROM 凑）
UPDATE products p
   SET stock = p.stock - t.qty
  FROM (VALUES (1, 1), (3, 2)) AS t(product_id, qty)
 WHERE p.id = t.product_id
RETURNING p.id, p.stock;   -- 1 号变 49，3 号变 6

ROLLBACK;

-- 内连接：只留两边都有的
SELECT u.email, o.order_no FROM users u JOIN orders o ON o.user_id = u.id;

-- 左外连接：左表全保留，右表缺失补 NULL —— dave 没下单也要出现
SELECT u.id, u.email, COUNT(o.id) AS order_cnt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;          -- dave 的 order_cnt = 0

-- 右外连接：LEFT 的镜像，实践中几乎总可以改写成 LEFT，见到能认即可
-- 交叉连接：笛卡尔积，常用于"数据 × 维度"生成报表骨架
SELECT p.title, m.name
FROM products p CROSS JOIN (VALUES ('12期免息'), ('24期免息')) AS m(name);

-- 自连接：邀请人链条（users 引 users）
SELECT u.email AS invitee, r.email AS inviter
FROM users u JOIN users r ON r.id = u.referrer_id;   -- bob→alice, carol→alice

-- 正确：右表过滤条件放在 ON —— 先按条件配对，配不上的右侧补 NULL，左侧全保留
SELECT u.id, u.email, o.id AS paid_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
-- 1 alice | 1 ; 2 bob | 3 ; 3 carol | NULL ; 4 dave | NULL

-- 事故：条件挪进 WHERE —— NULL 行被谓词杀死，LEFT JOIN 退化成 INNER JOIN
... WHERE o.status = 'paid';
-- 只剩 alice、bob 两行；carol/dave 消失

-- 全外连接：两边不匹配的都不丢，对账/比对两套数据用
SELECT a.order_no, b.order_no
FROM orders a
FULL JOIN orders_archive b ON a.order_no = b.order_no;

-- LATERAL：右表可以"引用"左表当前行，相当于逐行执行的相关子查询
SELECT u.id, u.email, latest.order_no, latest.created_at
FROM users u
JOIN LATERAL (
    SELECT o.order_no, o.created_at
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) latest ON true
ORDER BY u.id;

-- 下单超过 2 次的用户
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;        -- alice(3) bob(3)

SELECT user_id,
       COUNT(*) FILTER (WHERE status = 'paid')     AS paid_cnt,
       SUM(total_amount) FILTER (WHERE status <> 'cancelled') AS net_amount
FROM orders
GROUP BY user_id;
-- 旧写法 SUM(CASE WHEN ... THEN total_amount ELSE 0 END) 功能等价，但三四个 CASE 叠起来就没法看了

SELECT user_id,
       string_agg(status, ',' ORDER BY created_at) AS status_flow,
       array_agg(product_id) AS products
FROM orders GROUP BY user_id;

-- 每个用户最早的一单
SELECT DISTINCT ON (user_id) user_id, order_no, created_at
FROM orders
ORDER BY user_id, created_at, id;   -- 前缀必须与 DISTINCT ON 表达式完全一致

SELECT to_char(created_at, 'YYYY-MM') AS mon,      -- timestamptz 按会话时区格式化
       status,
       COUNT(*)          AS cnt,
       SUM(total_amount) AS amt
FROM orders
GROUP BY ROLLUP (mon, status)      -- = GROUPING SETS ((mon,status),(mon),())
ORDER BY mon NULLS LAST, status NULLS LAST;

SELECT email, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt
FROM users u;

SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- EXISTS 只问"有没有"，命中第一行短路返回；优化器通常走半连接（semi join）
-- 老资料说"永远 EXISTS 比 IN 快"，现代优化器下多数场景两者等价；
-- 真正必须用 EXISTS 的理由只有一个：NOT IN 的 NULL 语义。

SELECT id, email FROM users
WHERE id NOT IN (SELECT referrer_id FROM users);
-- 结果：0 行。不是空表，是"全军覆没"。

SELECT u.id, u.email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM users r WHERE r.referrer_id = u.id);
-- 2 bob ; 3 carol ; 4 dave（alice 邀请过 bob/carol 所以出局）。
-- 语义清晰，且不怕子查询将来出现 NULL。

(SELECT id, email FROM users WHERE balance > 0)
UNION ALL
(SELECT id, order_no FROM orders WHERE status = 'pending')
ORDER BY 1;      -- 按结果第一列排序

WITH paid AS (
    SELECT user_id, SUM(total_amount) AS spent
    FROM orders WHERE status IN ('paid','shipped','completed')
    GROUP BY user_id
)
SELECT u.email, p.spent
FROM users u JOIN paid p ON p.user_id = u.id
WHERE p.spent > 500;

-- 把过期未支付订单归档（按种子数据：order 4，2026-08-15 的 pending）
WITH moved AS (
    DELETE FROM orders
    WHERE status = 'pending'
      AND created_at < now() - interval '30 days'
    RETURNING id, order_no, user_id, product_id, quantity,
              unit_price, status, paid_at, created_at
)
INSERT INTO orders_archive
        (id, order_no, user_id, product_id, quantity,
         unit_price, status, paid_at, created_at)
SELECT * FROM moved
RETURNING (SELECT COUNT(*) FROM moved) AS archived_cnt;
-- archived_cnt = 1（order 5 只过期 10 天，留下）

-- 冲突则什么都不做（order_no 幂等落单）
INSERT INTO orders (order_no, user_id, product_id, quantity, unit_price)
VALUES ('ORD20261001001', 1, 1, 1, 399.00)
ON CONFLICT (order_no) DO NOTHING
RETURNING id;   -- 被跳过的行没有 RETURNING 输出，0 行即"已存在"

-- 冲突则更新：EXCLUDED 指"试图插入但因冲突没插成的那行"
INSERT INTO users (email, nickname, password_hash)
VALUES ('alice@shop.example', '爱丽丝A', 'hash_a_new')
ON CONFLICT (email) DO UPDATE
    SET nickname = EXCLUDED.nickname,
        updated_at = now()
    WHERE users.balance < 1000                    -- 部分 upsert：加 WHERE 守卫
RETURNING id, nickname;

MERGE INTO products p
USING (VALUES (1, 49), (6, 20)) AS s(id, stock)
ON p.id = s.id
WHEN MATCHED THEN UPDATE SET stock = s.stock
WHEN NOT MATCHED THEN INSERT (title, price, stock) VALUES ('新品', 99.00, s.stock);
-- NOT MATCHED 分支的 INSERT 不能手工给 id（GENERATED ALWAYS），由数据库自己发号

-- 管理后台第 N 页：LIMIT/OFFSET 简单直白
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 1000;

-- 首页
SELECT id, order_no, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 下一页：把上一页最后一行的 (created_at, id) 作为参数传入
SELECT id, order_no, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)          -- 行值比较，两列同向时可用
ORDER BY created_at DESC, id DESC
LIMIT 20;

SELECT NULL = NULL;      -- NULL，不是 true
SELECT nickname <> '鲍勃' FROM users;   -- carol（nickname 为 NULL）不会出现在结果里

SELECT COALESCE(nickname, email) AS display_name FROM users;  -- carol 显示邮箱
SELECT quantity / NULLIF(stock, 0) FROM products;             -- 除零变 NULL 而不是报错

SELECT * FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';

SELECT * FROM users WHERE email LIKE '%@shop.example';   -- 后缀匹配：btree 索引用不上，必然全扫
SELECT * FROM users WHERE email ILIKE 'a%';              -- ILIKE 大小写不敏感；通配符能不能吃到索引条件很多，见 04
SELECT title FROM products WHERE title ~ '键盘|鼠标';     -- 正则 ~ （区分）/~* （不区分）
SELECT title FROM products WHERE title SIMILAR TO '%(键盘|鼠标)%';