使用AND条件MYsql [英] using AND condition MYsql

问题描述

我需要的是：当我给training_id 172 AND training_id 174它只返回用户150

What I need is: when I give training_id 172 AND training_id 174 it have to return user 150 only

我试过了，但它不工作

SELECT user_id FROM Training_users WHERE training_id = 172 AND training_id = 174

b $ b

大多数时候training_id可能大于2

Most of the times training_id might be more than 2

推荐答案

What about this query:

SELECT user_id, COUNT(*) as nbTrainings FROM Training_users
WHERE training_id = 172 OR training_id = 174
GROUP BY user_id
HAVING nbTrainings = 2

它将过滤具有训练172或174的用户。

It will filter the users having the training 172 or 174. Then it will count how many trainings each user returned has and only keep the one(s) with two trainings.

这可能是因为用户不能有多个培训172和/或一个用户返回的训练。 174

This supposed that a user cannot have multiple trainings 172 and/or 174

这对多个自联接的优点是，如果你想过滤超过2个，你不必为每个训练id多次连接训练ID。

The advantage of this against the multiple self-joins is that you don't have to join multiple times for each training id if you want to filter with more than 2 training id.

有关 GROUP BY 和的更多信息HAVING 这里

编辑：在sqlfiddle测试后，这似乎工作，不返回错误的值如果一些用户有多个training_id和一个是172或174

EDIT : After testing in sqlfiddle this seems to work and does not return wrong values if some users have multiple training_id and one being 172 or 174

工作 sqlfiddle

这篇关于使用AND条件MYsql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！