mysql - 复杂的sql查询: properly joining four different tables

Question

我有 4 个表:users、userpreference、userinfo、useredu

最后三个表使用“id”作为引用表“users”的外键:

要制定的查询:

我需要找到“所有就读密歇根州立大学的单例女性中 HitTest 门的音乐”

注意到 MSU 也可以是“明尼苏达州立大学”

到目前为止我有这个查询，但它没有产生正确的结果？

select userpreference.preferencevalue as 'Music', COUNT(*) as 'SingleFemaleCount'from users, userpreference, userinformation
where users.Id = userinformation.Id
and users.Id = userpreference.Id
and userpreference.Id = userinformation.Id
and users.Gender = 'female'
and userinformation.informationvalue = 'single'
and usereducation.school like 'msu%' OR like 'minnesota state%'
and userpreference.preferencetype = 'music' GROUP BY preferencevalue ORDER BY      COUNT(distinct users.Id) DESC limit 10

Answer 1

这可能很简单，就像您在 where 子句中需要一些括号一样:

(usereducation.school，如“msu%”或“明尼苏达州%”)

否则，OR 的优先级将低于相邻 AND 的优先级。

编辑:2011-03-06

下面，我对代码进行了格式化，使其更易于阅读，并将 userinformation 和 usereducation 检查移至 exists()子句。我这样做的原因是，如果用户有超过 1 个 userinformation 或 userreductionat 行与您的条件匹配，它将影响 count() 聚合。

select
    userpreference.preferencevalue as 'Music',
    COUNT(*) as 'SingleFemaleCount'

from users, userpreference
where users.Gender = 'female'
  and userpreference.Id = users.Id
  and userpreference.preferencetype = 'music'

  and exists
    (select *
    from userinformation
    where userinformation.Id = users.Id
      and userinformation.informationvalue = 'single')

  and exists
    (select *
    from usereducation
    where usereducation.Id = users.Id
      and (usereducation.school like 'msu%' OR like 'minnesota state%'))

GROUP BY userpreference.preferencevalue
ORDER BY COUNT(*) DESC limit 10

另一件需要检查的事情是 (usereducation.school like 'msu%' OR like 'minnesota state%') 确实找到了所有 MSU 记录。如果结果集不是太大，将运行select different school from usereducation来检查并确保您获得了所有记录。

最后，我更喜欢使用连接语法，如下所示:

select
    userpreference.preferencevalue as 'Music',
    COUNT(*) as 'SingleFemaleCount'

from users
inner join userpreference on userpreference.Id = users.Id
where users.Gender = 'female'
  and userpreference.preferencetype = 'music'

  and exists
    (select *
    from userinformation
    where userinformation.Id = users.Id
      and userinformation.informationvalue = 'single')

  and exists
    (select *
    from usereducation
    where usereducation.Id = users.Id
      and (usereducation.school like 'msu%' OR like 'minnesota state%'))

GROUP BY userpreference.preferencevalue
ORDER BY COUNT(*) DESC limit 10

我意识到我完全改变了你的查询，但是嘿，这是家庭作业，对吧:)