SQL 常见业务场景查询实现

用row_number()查最新订单：partition by user_id按order_time desc编号，取rn=1；统计需注意时区转换、NULL值用left join及count(字段)与count(*)区别；模糊搜索优先用全文索引或case加权。

查最新订单但不重复用户：用 ROW_NUMBER() 而不是 GROUP BY

直接 GROUP BY user_id 拿 MAX(order_time) 只能拿到时间，拿不到对应那条订单的 order_id 或 amount —— 这是新手最常掉的坑。

正确做法是用窗口函数给每个用户的订单按时间倒序编号，再筛出编号为 1 的记录：

select user_id, order_id, amount, order_time FROM (   SELECT *,          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn   FROM orders ) t WHERE rn = 1;

• 必须写 PARTITION BY user_id，否则整个表只排一次序
• ORDER BY order_time DESC 决定“最新”的定义，别漏 DESC
• mysql 8.0+、postgresql、SQL Server 都支持；MySQL 5.7 不行，得用自连接或变量模拟

统计某天各小时下单量：小心时区和 date_TRUNC / DATE_FORMAT 差异

业务要的是“北京时间 9 月 1 日每小时单量”，但数据库存的是 UTC 时间，DATE_FORMAT(created_at, '%Y-%m-%d %H')（MySQL）或 DATE_TRUNC('hour', created_at)（PostgreSQL）默认按库时区算，结果可能偏移一两小时。

• MySQL 推荐先转时区：DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '%Y-%m-%d %H')
• PostgreSQL 用：DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai')
• 如果字段是 DATE 类型（无时间部分），就不存在这个问题，但通常不是

多条件模糊搜索还带权重：别硬拼 LIKE + OR

比如搜“苹果手机”，想让 product_name 匹配到的排前面，description 匹配到的靠后。纯 WHERE name LIKE '%苹果%' OR desc LIKE '%苹果%' 无法排序，也容易慢。

• MySQL 5.7+ 可用全文索引：MATCH(name, description) AGAINST('苹果手机' IN NATURAL LANGUAGE MODE)，但要求字段建了 FULLTEXT 索引
• 更通用的做法是用 CASE 手动加权：ORDER BY (CASE WHEN name LIKE '%苹果%' THEN 2 WHEN description LIKE '%苹果%' THEN 1 ELSE 0 END) DESC
• LIKE '%关键词%' 无法走 B-tree 索引，数据量大时务必加 EXPLAIN 看是否用了索引

连表查订单+用户+地址，NULL 值导致统计不准

写 SELECT COUNT(*) FROM orders o JOIN users u ON o.user_id = u.id JOIN addresses a ON u.id = a.user_id，发现订单数变少了——因为有些用户没填收货地址，JOIN 直接把整行过滤掉了。

• 该用 LEFT JOIN：地址信息允许为空，但订单必须存在
• 聚合时注意 COUNT(addresses.id) 和 COUNT(*) 区别：COUNT(字段) 自动忽略 NULL，COUNT(*) 统计所有行
• 如果要统计“有地址的订单占比”，得写：COUNT(a.id) * 1.0 / COUNT(*)，否则整数除法结果为 0（尤其在 PostgreSQL/SQL Server 中）

真实业务查询里，时区、NULL 处理、索引失效这三块最容易在线上突然暴露问题，而且往往只在特定数据分布下才触发。