从Where子句中的多个IF条件中选择

发布于 2025-01-13 06:58:05 字数 585 浏览 1 评论 0原文

我有一个以下查询

SELECT *
FROM products a, productImgs b
WHERE a.visible = 1 AND a.Type IN ('Accessories', 'Clothing', 'Electronics')
ORDER BY a.visibleOrder ASC
LIMIT 100

在上面的查询中，我需要添加 IF 条件，即 IF a.Type 是 Accessories，那么我需要从某些 a.Brands 中进行选择，如果 a.Type 是 Clothing，那么我需要从某些 a.Brands 中进行选择

For a.Type 'Accessories' ->  a.Brands IN (ALL)
For a.Type 'Clothing'    ->  a.Brands IN ('A','B','C')
For a.Type 'Electronics' ->  a.Brands IN ('D','E','F')

原文

I have a following query

SELECT *
FROM products a, productImgs b
WHERE a.visible = 1 AND a.Type IN ('Accessories', 'Clothing', 'Electronics')
ORDER BY a.visibleOrder ASC
LIMIT 100

In the above query I need to add IF condition that IF a.Type is Accessories then I need certain a.Brands to select from and if a.Type is Clothing then I need to select from certain a.Brands

For a.Type 'Accessories' ->  a.Brands IN (ALL)
For a.Type 'Clothing'    ->  a.Brands IN ('A','B','C')
For a.Type 'Electronics' ->  a.Brands IN ('D','E','F')

分享到QQ

分享到微博

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

发布评论

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

甜中书 2025-01-20 06:58:05

使用 CASE 表达式，但您还应该为 2 个表编写带有 ON 子句的正确联接：

SELECT * 
FROM products a INNER JOIN productImgs b
ON .....
WHERE a.visible=1 
  AND CASE a.Type 
        WHEN 'Accessories' THEN 1
        WHEN 'Clothing' THEN a.Brands IN ('A','B','C')
        WHEN 'Electronics' THEN a.Brands IN ('D','E','F')
      END 
ORDER BY a.visibleOrder ASC LIMIT 100;

Use a CASE expression, but you should also write a proper join with an ON clause for the 2 tables:

SELECT * 
FROM products a INNER JOIN productImgs b
ON .....
WHERE a.visible=1 
  AND CASE a.Type 
        WHEN 'Accessories' THEN 1
        WHEN 'Clothing' THEN a.Brands IN ('A','B','C')
        WHEN 'Electronics' THEN a.Brands IN ('D','E','F')
      END 
ORDER BY a.visibleOrder ASC LIMIT 100;

回复收藏 0 原文

坐在坟头思考人生 2025-01-20 06:58:05

像使用任何其他 where 子句一样使用括号和 and/or 条件：

where a.visible = 1 and (
  a.Type = 'Accessories' or
  a.Type = 'Clothing'    and a.Brands IN ('A', 'B', 'C') or
  a.Type = 'Electronics' and a.Brands IN ('D', 'E', 'F')
)

这应该让 MySQL 有机会使用索引。

Use parentheses and and/or conditions like you would with any other where clause:

where a.visible = 1 and (
  a.Type = 'Accessories' or
  a.Type = 'Clothing'    and a.Brands IN ('A', 'B', 'C') or
  a.Type = 'Electronics' and a.Brands IN ('D', 'E', 'F')
)

This should give MySQL chance to use indexes.

回复收藏 0 原文

~没有更多了~