可选地将多个表连接到多个表

Question

我有下面的桌子

用户

user_id  name
101      Tony
102      Skyle
103      Kenne

凹凸不平

intrest_id intrest_name
201          Eating
202          Sleeping
203          Drinking

业余爱好

hobby_id  hobby_name
301         Smoking
302         Hiking
303         Browsing

User_Intrest

user_id  intrest_id
101      201
102      201
102      202
103      201
103      202
103      203

User_Hobby

user_id  hobby_id
101      301
102      301
102      302
103      301
103      302
103      303

现在，要查找同时感兴趣的用户I( Eating和Sleeping )，我已经编写了

select u.user_id
from user u, intrest i, user_intrest ui
where u.user_id = ui.user_id 
and i.intrest_id = ui.intrest_id and i.intrest_name in ('Eating', 'Sleeping')
group by u.user_id
having count(i.intrest_name) = 2

输出

user_id
102
103

与上述相同，我还可以找到用户I与业余爱好Smoking，Hiking，Browsing如下

select u.user_id
from user u, hobby h, user_hobby uh
where u.user_id = uh.user_id 
and h.hobby_id = uh.hobby_id and h.hobby_name in ('Smoking', 'Hiking', 'Browsing')
group by u.user_id
having count(i.intrest_name) = 3

输出

user_id
    103

现在，我想把这两种兴趣结合在一起，如果只有兴趣被传递，那么就会发现那些兴趣爱好的用户，或者如果只有兴趣爱好被传递，那么就可以找到具有这些兴趣爱好的用户，或者如果同时传递兴趣和兴趣爱好，那么就可以找到具有这些兴趣和兴趣的用户。

Update:这是spring数据rest的一部分，它带有本地查询，其中查询参数是可选的。下面是为intrests查找用户的一个工作示例。现在，我想将其扩展到包括业余爱好，如果两者都在请求中传递以查找用户，则两者都将被使用。

@RestResource(path = "getUserByIntrestsAndHobbies", rel = "getUserByIntrestsAndHobbies")
    @Query(value = "SELECT u FROM User u WHERE (COALESCE(:intrests, NULL) IS NOT NULL AND u.userId IN (SELECT u.userId " +
            "FROM User u, Intrest i, UserIntrest ui " +
            "WHERE u.userId = ui.userId AND i.intrestId = ui.intrestId " +
            "AND i.intrestName IN (:intrests) " +
            "GROUP BY u.userId " +
            "HAVING (:intrestsSize IS NULL OR :intrestsSize = count(i.intrestName))))"
    )
    Page<User> getUsersByIntrestsAndHobbies(@Param("intrests") List<String> intrests,
                                                                           @Param("intrestsSize") Long intrestsSize,
                                                                           @Param("hobbies") List<String> hobbies,
                                                                           @Param("hobbiesSize") Long hobbiesSize,
                                                                           Pageable pageable);

Answer 1

好的，当您使用显式联接(而不是隐式、折旧式、逗号分隔的联接)时，问题变得更清楚了。然后，您将看到需要使用left联接来允许在有匹配时返回记录。

select u.[user_id]
from [user] u
left join user_intrest ui on ui.[user_id] = u.[user_id]
left join intrest i on i.intrest_id = ui.intrest_id and i.intrest_name in ('Eating', 'Sleeping')
left join user_hobby uh on uh.[user_id] = u.[user_id]
left join hobby h on h.hobby_id = uh.hobby_id and h.hobby_name in ('Smoking', 'Hiking', 'Browsing')
group by u.[user_id]
having count(distinct i.intrest_name) = 2 and count(distinct h.hobby_name) = 3;

• 的最佳做法是不要使用保留的单词，如user或user_id，因为您总是需要将它们转义。

• 除了id列之外，没有必要将表名放在列名前面，例如hobby_name应该是name --您只是按原样给自己打了更多的字。

• 的兴趣不是拼写出来的:)

• 如果您为示例数据提供了DDL/DML，那么我们就更容易测试.

Answer 2

我建议：

select i.userid
from (select ui.userid
      from user_interest ui join
           interest i
           on i.interest_id = ui.interest_id 
      where i.interest_name in ('Eating', 'Sleeping')
      group by ui.userid
      having count(*) = 2
     ) i join
     (select uh.userid
      from user_hobby uh join
           hobby h
           on uh.hobby_id = i.hobby_id 
      where h.hobby_name in ('Smoking', 'Hiking', 'Browsing')
      group by uh.userid
      having count(*) = 3
     ) h
     on i.userid = h.userid;

这将沿每个维度分别计数，因此计数是准确的。