mysql:连接一个表,其中多行是条件的一部分

toe95027  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(325)

尝试从有多个相关行的联接表中选择一些行。
这是一个非常简化的原始查询版本,所以请不要对一般的表结构提出建议。
表a

id, title
------------
1, housea
2, houseb
3, housec
4, housed

表b

id, cid, attrib, val
--------------------
1, 1, bathrooms, 2
2, 1, bedrooms, 1
3, 2, bathrooms, 0
4, 1, pools, 1
5, 2, bedrooms, 1
6, 2, pools, 1
7, 3, bathrooms, 1
8, 4, bathrooms, 1
9, 4, bedrooms, 1

选择至少有一个浴室和一个卧室的所有对象。
因此,只有这两种情况出现:

2, housea
4, housed

这不起作用:

SELECT a.id, title
FROM tablea a
LEFT JOIN tableb b ON b.cid = a.id
WHERE (b.attrib = "bathrooms" AND b.val > 0) AND (b.attrib = "bedrooms" AND b.val > 0)

这也不是:

SELECT a.id, title
FROM tablea a
LEFT JOIN tableb b1 ON b1.cid = a.id AND (b1.attrib = "bathrooms" AND b1.val > 0) 
LEFT JOIN tableb b2 ON b2.cid = a.id AND (b2.attrib = "bedrooms" AND b2.val > 0)

谢谢你的建议!

jjjwad0x

jjjwad0x1#

尝试运行相关子查询,如下所示:

select a.id, a.title from tablea a where 2 >= 
(select count(b.cid) from tableb b where b.cid = a.id and b.attrib in ('bathrooms' , 'bedrooms') and b.val > 0 group by b.cid)
iih3973s

iih3973s2#

您的第二个版本基本上是正确的,只是您需要内部联接:

SELECT a.id, a.title
FROM tablea a JOIN
     tableb b1
     ON b1.cid = a.id AND (b1.attrib = 'bathrooms' AND b1.val > 0) JOIN
     tableb b2
     ON b2.cid = a.id AND (b2.attrib = 'bedrooms' AND b2.val > 0) ;

所有的过滤都在 ON 子句,因此没有实际筛选出任何行。如果您查看 b1 或者 b2 .

相关问题