当前位置：文江博客话题详情

是否有像 IN 这样的多个 and 语句的 mysql 子句？

发布于 2024-12-20 06:12:36 字数 45 浏览 2 评论 0原文

有没有一种简单的方法可以像 IN 子句对 OR 那样执行多个 AND 语句？

原文

分享到QQ

分享到微博

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

发布评论

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

爱她像谁 2024-12-27 06:12:36

一般来说是不可能的，
一列只能存储一个值，
当 status = 1 AND status =2 时，不会出现这种情况。

例外情况是 设置数据类型

回复收藏 0 原文

独守阴晴ぅ圆缺 2024-12-27 06:12:36

您想使用 ALL 关键字吗？

http://dev.mysql.com/doc/refman/5.5 /en/all-subqueries.html

回复收藏 0 原文

陪我终i 2024-12-27 06:12:36

这是 Stack Overflow 上一个很常见的问题，我正在为其创建一个新标签，sql-match-all。我会回去尝试标记一些与此问题相关的旧答案。

根据您的简短描述，我假设您的意思是您想要返回一组与所有一组值匹配的行，例如标记的文章（php、mysql、性能）。 IN() 谓词不要求所有三个标记都匹配，但给定的一行数据只有一个标记，并且 WHERE 子句一次只能测试一行。

对于这个问题有两种流行的解决方案。第一个是计算行组中有多少个不同的标签，如果它等于您要匹配的标签列表，则通过测试。

SELECT t.article
FROM Tags t
WHERE t.tag IN ('php', 'mysql', 'performance')
GROUP BY t.article
HAVING COUNT(DISTINCT tag) = 3;

另一种解决方案是自连接次数与您要查找的标签数量相同。通过使用 INNER JOIN，您可以将结果集限制为仅匹配的组。

SELECT t1.article
FROM Tags t1
INNER JOIN Tags t2 USING (article)
INNER JOIN Tags t3 USING (article)
WHERE (t1.tag, t2.tag, t3.tag) = ('php', 'mysql', 'performance');

哪种解决方案更好取决于您想要匹配的标签数量、在应用程序中构建此查询的方便程度，以及您的 RDBMS 品牌擅长的具体优化。例如，后一种解决方案在 MySQL 中通常要快得多，因为 MySQL 倾向于为 GROUP BY 查询写入临时表。但是您应该在数据库中测试这两种查询类型以确保正确。

This is a common enough question on Stack Overflow that I'm creating a new tag for it, sql-match-all. I'll go back and try to tag some of my old answers related to this problem.

Based on your brief description, I assume you mean you want to return a group of rows that match all of a set of values, for example articles that are tagged (php, mysql, performance). The IN() predicate does not require that all three tags are matched, but a given row of data only has one tag, and a WHERE clause can only test one row at a time.

There are two popular solutions for this problem. The first is to count how many distinct tags in the group of rows, and if it's equal to the list of tags you're looking to match, then this passes the test.

SELECT t.article
FROM Tags t
WHERE t.tag IN ('php', 'mysql', 'performance')
GROUP BY t.article
HAVING COUNT(DISTINCT tag) = 3;

The other solution is to self-join as many times as the number of tags you're looking for. By using INNER JOIN, you restrict the result set to only those groups that match.

SELECT t1.article
FROM Tags t1
INNER JOIN Tags t2 USING (article)
INNER JOIN Tags t3 USING (article)
WHERE (t1.tag, t2.tag, t3.tag) = ('php', 'mysql', 'performance');

Which solution is better can depend on the number of tags you want to match, how convenient it is to build this query in your application, and also specific optimizations that your brand of RDBMS is good at. The latter solution is usually much faster in MySQL, for example, because MySQL tends to write temp tables for GROUP BY queries. But you should test both query types in your database to make sure.

回复收藏 0 原文

~没有更多了~