与需要满足多种条件的 MySQL 连接有问题

我有两个表 rooms和 rooms facilities，我必须选择与所需设施的房间。

如果我选择一个有一个设施的房间(id = 4-id _ fu-的设施)。使用以下查询:

SELECT u.* FROM rooms u
JOIN facilities_r fu
ON fu.id_uc = u.id_uc
AND fu.id_fu = '4'
WHERE 1
AND vizibility='1'
GROUP BY id_uc
ORDER BY u_premium desc, id_uc DESC

But if I want to select the room with more facilities, let's say facilities with id=4, and id=3 ..using the following query it doesn't work:

SELECT u.* FROM room u
JOIN facilities_r fu
ON fu.id_uc=u.id_uc
AND fu.id_fu = '4'
AND fu.id_fu = '3'
WHERE 1
AND vizibility = '1'
GROUP BY id_uc
ORDER BY u_premium DESC, id_uc DESC

我不明白为什么它不工作，但我不知道如何把条件。

181254 次浏览
小开 
SELECT
u . *
FROM
room u
JOIN
facilities_r fu ON fu.id_uc = u.id_uc
AND (fu.id_fu = '4' OR fu.id_fu = '3')
WHERE
1 and vizibility = '1'
GROUP BY id_uc
ORDER BY u_premium desc , id_uc desc

这里必须使用 OR，而不是 AND。

因为 id _ fu 不能同时等于4和3。

小开

可以用括号对条件进行分组。在检查一个字段是否等于另一个字段时，需要使用 OR。例如 WHERE a='1' AND (b='123' OR b='234')。

SELECT u.*
FROM rooms AS u
JOIN facilities_r AS fu
ON fu.id_uc = u.id_uc AND (fu.id_fu='4' OR fu.id_fu='3')
WHERE vizibility='1'
GROUP BY id_uc
ORDER BY u_premium desc, id_uc desc
小开

如果你加入设施表两次，你就会得到你想要的:

select u.*
from room u
JOIN facilities_r fu1 on fu1.id_uc = u.id_uc and fu1.id_fu = '4'
JOIN facilities_r fu2 on fu2.id_uc = u.id_uc and fu2.id_fu = '3'
where 1 and vizibility='1'
group by id_uc
order by u_premium desc, id_uc desc
小开

这也应该有效(未经测试) :

SELECT u.*
FROM room u
JOIN facilities_r fu ON fu.id_uc = u.id_uc AND u.id_fu IN(4,3)
WHERE 1 AND vizibility = 1
GROUP BY id_uc
ORDER BY u_premium desc , id_uc desc

如果 u.id_fu是一个数字字段，那么您可以删除它们周围的 '。 vizibility也一样。只有当字段是一个文本字段(数据类型 char、 varchar 或文本数据类型之一，例如 longtext)时，该值才必须由 '或甚至 "封装。

此外，我和 Oracle 也建议在反勾中附上表名和字段名。 因此，如果字段名包含关键字，就不会遇到麻烦。


提交答案