mysql - 复杂的SQL查询:正确连接四个不同的表

我有4个表：users，userpreference，userinfo，useredu

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

要制定的查询：

我需要找到“去MSU的所有单身女性中的顶级音乐”

指出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

最佳答案

它可能很简单，因为您需要在where子句中添加一些括号：

(usereducation.school like 'msu%' OR like 'minnesota state%')

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

编辑：2011-03-06

下面，我对代码进行了格式化，以使其更易于阅读，并且还将userinformation和usereducation检查移到了exists()子句中。我这样做的原因是，如果一个用户有多个符合您条件的userinformation或usereductionat行，则会影响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 distinct 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

我知道我完全改变了您的查询，但是嘿，这是功课，对:)