mysql - 当输入值为 NULL 时，多个 CASE 语句不起作用

Question

我有这个 SQL 查询:

SET @start_date = 'a timestamp at x in time';
SET @end_date = 'a timestamp at x + y in time';

SELECT DISTINCT u.user_id
FROM user_plan u
  JOIN user_feature uf ON uf.user_plan_id = u.id
WHERE uf.status IN ('PENDING_ACTIVE', 'PENDING_INACTIVE')
  AND (
    CASE
      WHEN @start_date IS NULL AND @end_date IS NULL THEN true
      WHEN @end_date IS NOT NULL AND @end_date >= u.created_at AND @start_date IS NULL THEN true
      WHEN @start_date IS NOT NULL AND @start_date <= u.created_at AND @end_date IS NULL THEN true
      WHEN @start_date IS NOT NULL AND @end_date IS NOT NULL AND u.created_at BETWEEN @start_date AND @end_date THEN true
      ELSE false
    END
  ) = true;

start_date 和 end_date 作为输入参数出现。问题是它们可以为 null，到目前为止，第三个和第四个 WHEN 语句不起作用。

有什么方法可以简化这种小疯狂吗？这就是我的 SQL 技能可以达到的程度。

Answer 1

假设 user_id 在 user_plan 中是唯一的，我会选择:

SELECT u.user_id
FROM user_plan u
WHERE EXISTS (SELECT 1
              FROM user_feature uf 
              WHERE uf.user_plan_id = u.id AND
                    uf.status IN ('PENDING_ACTIVE', 'PENDING_INACTIVE')
             ) AND
      (@end_date IS NULL OR @end_date >= u.created_at) AND
      (@start_date IS NULL OR @start_date <= u.created_at);

即使 user_plan.user_id 不为空，您仍然可以将此公式与 select distinct 一起使用，但删除 distinct 应该是显着的性能改进。