使用DeepSeek辅助优化SQL关联查询ON条件字段为空问题的实践

SELECT 
    u.user_id,
    u.username,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.create_time >= '2023-01-01'
GROUP BY u.user_id, u.username
ORDER BY total_amount DESC;

-- 用户表结构
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    create_time DATETIME,
    INDEX idx_create_time (create_time)
);

-- 订单表结构  
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NULL,  -- 注意：这里允许NULL值
    amount DECIMAL(10,2),
    order_time DATETIME,
    INDEX idx_user_id (user_id)
);

SELECT 
    u.user_id,
    u.username,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = COALESCE(o.user_id, -1)
WHERE u.create_time >= '2023-01-01'
  AND (o.user_id IS NOT NULL OR o.order_id IS NULL)
GROUP BY u.user_id, u.username
ORDER BY total_amount DESC;

-- 处理有user_id的订单
SELECT 
    u.user_id,
    u.username,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.create_time >= '2023-01-01'
  AND o.user_id IS NOT NULL
GROUP BY u.user_id, u.username

UNION ALL

-- 处理没有user_id的订单（统计到虚拟用户）
SELECT 
    -1 as user_id,
    '未知用户' as username,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_amount
FROM orders o
WHERE o.user_id IS NULL
  AND EXISTS (SELECT 1 FROM users u WHERE u.create_time >= '2023-01-01')
ORDER BY total_amount DESC;

-- 为非空user_id订单创建条件索引
CREATE INDEX idx_orders_user_id_not_null ON orders(user_id) 
WHERE user_id IS NOT NULL;

-- 为空user_id订单创建条件索引
CREATE INDEX idx_orders_user_id_null ON orders(order_id) 
WHERE user_id IS NULL;

-- 监控查询性能的检查语句
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements 
WHERE query LIKE '%JOIN%ON%NULL%' 
   OR query LIKE '%COALESCE%JOIN%'
ORDER BY mean_time DESC
LIMIT 10;