当输入值为空时，多个case语句不起作用

tzdcorbm 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(264)

我有一个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` 作为输入参数。问题是它们可以为空，到目前为止，第三个和第四个 `WHEN` 声明不起作用。
有什么办法可以简化这个小小的疯狂吗？这就是我能用我脆弱的sql技能走多远。

sql mysql

来源：https://stackoverflow.com/questions/50496984/multiple-case-statements-dont-work-when-input-values-are-null

2条答案

按热度按时间

j2cgzkjk1#

假设 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 应该是一个显著的性能改进。

赞(0）回复(0）举报 2021-06-21

o2gm4chl2#

您可以尝试此查询。
在你的情况下，你可以使用 OR 而不是 CASE WHEN 表达

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 (
    (@start_date IS NULL AND @end_date IS NULL)
    OR
    (
        @end_date IS NOT NULL AND @end_date >= u.created_at
    )
    OR
    (   
        @start_date IS NOT NULL AND @start_date <= u.created_at
    )
    OR
    (   
        @start_date IS NOT NULL AND @end_date IS NOT NULL AND u.created_at BETWEEN @start_date AND @end_date
    )
  )

赞(0）回复(0）举报 2021-06-21

我来回答

当输入值为空时，多个case语句不起作用

2条答案

相关问题

热门标签

最新问答