Блог

У меня есть пользователь DE9013 с двумя положительными оценками в SQL-таблице:

 # select * from pref_rep where id='DE9013';
   id   | author | good | fair | nice | about |         last_rated         |   author_ip
-------- -------- ------ ------ ------ ------- ---------------------------- ---------------
 DE9013 | DE9241 | t    | t    | t    |       | 2011-03-06 09:23:00.400518 | 97.33.154.43
 DE9013 | DE9544 | t    | t    | t    |       | 2011-03-06 10:06:37.561277 | 97.33.35.54
(2 rows)
  

И общая сумма оценок fair nice, как и ожидалось, составляет четыре:

 # select
count(nullif(r.fair, false))  
count(nullif(r.nice, false)) -
count(nullif(r.fair, true)) -
count(nullif(r.nice, true))
 from pref_rep r where id='DE9013';
 ?column?
----------
        4
(1 row)
  

Мой вопрос: почему я получаю пользователя 9013 в списке ниже, где я пытаюсь найти всех пользователей, сыгравших более 30 завершенных игр и имеющих рейтинги (справедливый хороший) выше 30?

 # select substring(m.id from 3)::bigint, 3
from pref_match m, pref_rep r
where m.id=r.id and
m.id like 'DE%'
group by m.id
having (sum(m.completed) > 30 and
count(nullif(r.fair, false))  
count(nullif(r.nice, false)) -
count(nullif(r.fair, true)) -
count(nullif(r.nice, true)) > 30) limit 3;
 substring | ?column?
----------- ----------
      9013 |        3
      8692 |        3
      7059 |        3
(3 rows)
  

Использование PostgreSQL 8.4.7 с CentOS 5.7 / 64 бит

В вашем первом запросе вы выбираете только из pref_rep . Во втором запросе вы соединяете pref_rep с pref_match, якобы в отношениях «многие ко многим». Для данного пользователя каждая строка в pref_match будет присоединяться к каждой строке pref_rep . Например, если у пользователя 9013 есть 2 строки в pref_match и 10 строк в pref_rep, вы получите обратно 20 строк! Вот почему значения из pref_match выше с объединением, чем без объединения.

Я предлагаю вам отдельно объединить две таблицы по пользователям, а затем объединить результаты. Что-то вроде этого должно сработать:

 select substring(ma.id from 3)::bigint, 3
from (
   select r.id
   from pref_rep r
   where r.id like 'DE%' --yuck!
   group by r.id
   having (count(nullif(r.fair, false))  
           count(nullif(r.nice, false)) -
           count(nullif(r.fair, true)) -
           count(nullif(r.nice, true)) > 30)
) ra
join (
   select m.id
   from pref_match m
   where m.id like 'DE%' --yuck!
   group by m.id
   having sum(m.completed) > 30
) ma
on ra.id = ma.id 
;