排序分页与子查询

-- 单列排序(默认 ASC 升序)
SELECT * FROM products ORDER BY price;

-- 降序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序:先按分类升序,同分类内按价格降序
SELECT name, category_id, price
FROM products
ORDER BY category_id ASC, price DESC;

-- NULL 放最前
SELECT * FROM users ORDER BY city ASC NULLS FIRST;

-- NULL 放最后(DESC 时默认 NULL 在前,用 NULLS LAST 改变)
SELECT * FROM users ORDER BY balance DESC NULLS LAST;

-- 按用户名长度排序
SELECT username, LENGTH(username) AS name_len
FROM users
ORDER BY LENGTH(username) DESC;

-- 按列的序号排序(SELECT 中第 2 列)
SELECT username, balance FROM users ORDER BY 2 DESC;

-- 按 CASE 表达式自定义排序优先级
SELECT id, status, total FROM orders
ORDER BY
  CASE status
    WHEN 'pending'   THEN 1
    WHEN 'paid'      THEN 2
    WHEN 'shipped'   THEN 3
    WHEN 'done'      THEN 4
    WHEN 'cancelled' THEN 5
  END;

-- 取前 3 条(最贵的 3 个商品)
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

-- 分页:第 2 页,每页 5 条(跳过前 5 条)
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 5;

-- 只取第一条(等价于 LIMIT 1)
SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 1 ROW ONLY;

-- 游标分页:基于上一页最后一条的 id 继续查
-- 第 1 页
SELECT * FROM orders ORDER BY id LIMIT 10;

-- 第 2 页(假设上一页最后一条 id = 10)
SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;

-- 优势:无论第几页,性能恒定(利用 id 索引直接定位)

-- 在 SELECT 中:每个商品价格与平均价格的差值
SELECT name, price,
       price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

-- 在 WHERE 中:查询价格高于平均价的商品
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 在 HAVING 中:哪些分类的平均价格高于全局平均
SELECT c.name, ROUND(AVG(p.price), 2) AS avg_price
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.name
HAVING AVG(p.price) > (SELECT AVG(price) FROM products);

-- 先聚合,再筛选(等价于 HAVING,但有时更清晰)
SELECT * FROM (
  SELECT user_id, COUNT(*) AS cnt, SUM(total) AS total_spent
  FROM orders
  WHERE status = 'done'
  GROUP BY user_id
) AS user_stats
WHERE total_spent > 5000
ORDER BY total_spent DESC;

-- 查询购买过"电子产品"的用户
SELECT username FROM users
WHERE id IN (
  SELECT DISTINCT o.user_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.product_id = oi.product_id
  WHERE p.category_id = 1
);

-- NOT IN:查询从未下过单的用户
SELECT username FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

-- 查询至少下过一单的用户(EXISTS 写法)
SELECT u.username
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 查询从未写过评价的用户(NOT EXISTS)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM reviews r WHERE r.user_id = u.id
);
-- frank, henry(这两个用户没有评价记录)

-- 相关子查询:查询"订单金额超过该用户平均订单金额"的订单
SELECT o.id, o.user_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(o2.total)
  FROM orders o2
  WHERE o2.user_id = o.user_id
)
ORDER BY o.user_id, o.total DESC;

-- 基础 CTE:先算每用户的订单统计,再筛选
WITH user_orders AS (
  SELECT user_id,
         COUNT(*) AS order_count,
         SUM(total) AS total_spent
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY user_id
)
SELECT u.username, u.city, uo.order_count, uo.total_spent
FROM users u
JOIN user_orders uo ON uo.user_id = u.id
WHERE uo.total_spent > 5000
ORDER BY uo.total_spent DESC;

-- 多个 CTE 组合:用户消费排名 + 评价活跃度
WITH spending AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders WHERE status = 'done'
  GROUP BY user_id
),
reviewing AS (
  SELECT user_id, COUNT(*) AS review_count, ROUND(AVG(rating), 2) AS avg_given
  FROM reviews
  GROUP BY user_id
)
SELECT u.username,
       COALESCE(s.total_spent, 0) AS total_spent,
       COALESCE(r.review_count, 0) AS review_count,
       r.avg_given
FROM users u
LEFT JOIN spending s ON s.user_id = u.id
LEFT JOIN reviewing r ON r.user_id = u.id
ORDER BY total_spent DESC;

-- CTE 中引用前面的 CTE(链式)
WITH monthly_sales AS (
  SELECT TO_CHAR(created_at, 'YYYY-MM') AS month,
         SUM(total) AS revenue
  FROM orders WHERE status = 'done'
  GROUP BY TO_CHAR(created_at, 'YYYY-MM')
),
avg_monthly AS (
  SELECT ROUND(AVG(revenue), 2) AS avg_revenue FROM monthly_sales
)
SELECT ms.month, ms.revenue,
       am.avg_revenue,
       CASE WHEN ms.revenue > am.avg_revenue THEN 'above' ELSE 'below' END AS vs_avg
FROM monthly_sales ms, avg_monthly am
ORDER BY ms.month;