postgres：在 WHERE 子句中使用 CASE 和 ANY()

发布于 2024-10-18 05:52:57 字数 343 浏览 0 评论 0原文

有什么方法可以让这项工作发挥作用吗？

SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t.tid =
CASE
    WHEN t2.someboolval THEN ANY(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16])
    ELSE ANY(ARRAY[77,66])
END

不幸的是，我不能只执行 t.tid = CASE WHEN t2.someboolval THEN 1 ELSE 2 END 因为我需要与数组进行匹配。这可行吗？

原文

Is there some way to make this work?

SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t.tid =
CASE
    WHEN t2.someboolval THEN ANY(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16])
    ELSE ANY(ARRAY[77,66])
END

Unfortunately I can't just do t.tid = CASE WHEN t2.someboolval THEN 1 ELSE 2 END because I need to match against an array. Is this doable?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

日裸衫吸 2024-10-25 05:52:58

使用“与”/“或”。类似于：

  SELECT
  *
  FROM table t
  INNER JOIN othertable t2 USING (tid)
  WHERE
     t2.someboolval AND t.tid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) 
     OR NOT (t2.someboolval) and t.id IN (77,66)

编辑：格式化

Use AND/OR. Something like:

  SELECT
  *
  FROM table t
  INNER JOIN othertable t2 USING (tid)
  WHERE
     t2.someboolval AND t.tid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) 
     OR NOT (t2.someboolval) and t.id IN (77,66)

Edit: formatted

回复收藏 0 原文

尘曦 2024-10-25 05:52:58

您必须更改 ANY 的位置：

SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
  t.tid =
  ANY(CASE
    WHEN t2.someboolval THEN ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
    ELSE ARRAY[77,66]
  END)

You have to change place of ANY:

SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
  t.tid =
  ANY(CASE
    WHEN t2.someboolval THEN ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
    ELSE ARRAY[77,66]
  END)

回复收藏 0 原文

~没有更多了~