Попытка построить запрос MySQL с подобным выбором не удалась

Справочная информация

Система

OS:

bash$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 6.0.5 (squeeze)
Release:        6.0.5
Codename:       squeeze

MySQL:

bash$ mysql --version
mysql  Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (x86_64) using readline 6.1

База данных

Двигатель: MyISAM

Таблица: post_votes

mysql> describe post_votes;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_uuid | binary(16)          | YES  |     | NULL    |                |
| user_uuid | binary(16)          | YES  |     | NULL    |                |
| vote      | tinyint(4)          | YES  |     | 0       |                |
+-----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Таблица: посты

mysql> describe posts;
+-------------+---------------------+------+-----+----------------------+----------------+
| Field       | Type                | Null | Key | Default              | Extra          |
+-------------+---------------------+------+-----+----------------------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL                 | auto_increment |
| post_uuid   | binary(16)          | YES  | UNI | NULL                 |                |
| owner_uuid  | binary(16)          | YES  |     | NULL                 |                |
| created     | int(10) unsigned    | YES  |     | NULL                 |                |
| edited      | int(10) unsigned    | YES  |     | NULL                 |                |
| title       | varchar(50)         | YES  |     | NULL                 |                |
| description | varchar(150)        | YES  |     | NULL                 |                |
| link        | varchar(100)        | YES  |     | NULL                 |                |
| properties  | varchar(20)         | YES  |     | 00000000000000000000 |                |
+-------------+---------------------+------+-----+----------------------+----------------+
9 rows in set (0.00 sec)

Эта проблема

Я хочу выбирать сообщения в зависимости от их соотношения голосов за/против. Я придумал запрос, который подсчитывает голоса за и против, но, похоже, не может присоединить его к фактическим сообщениям.

Рабочий (неполный) запрос

(
    SELECT COUNT(*), post_uuid, 'upvotes' AS 'mode'
    FROM post_votes a
    WHERE a.vote = 1
    GROUP BY post_uuid
)
UNION
(
    SELECT COUNT(*), post_uuid, 'downvotes' AS 'mode'
    FROM post_votes a
    WHERE a.vote = -1
    GROUP BY post_uuid
)

Сломанный запрос

(
    (
        SELECT COUNT(*), post_uuid, 'upvotes' AS 'mode'
        FROM post_votes a
        WHERE a.vote = 1
        GROUP BY post_uuid
    )
    UNION
    (
        SELECT COUNT(*), post_uuid, 'downvotes' AS 'mode'
        FROM post_votes a
        WHERE a.vote = -1
        GROUP BY post_uuid
    )
)
a
LEFT JOIN posts b ON a.post_uuid = b.post_uuid

Что я делаю не так? сообщение об ошибке, которое я получаю:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'UNION
(
SELECT COUNT(*), post_uuid, 'downvotes' AS 'mode'

mysql voting-system
person Axel Latvala    schedule 26.11.2012    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Пара UNION фактически является производной таблицей и должна появиться в предложении FROM, чтобы участвовать в JOIN. Поскольку сейчас он находится в предложении FROM, вы должны SELECT его столбцы, иначе в вашем запросе вообще не будет предложения SELECT. Синтаксис MySQL снисходителен, но не настолько, чтобы допускать отсутствующее предложение SELECT.

/* SELECT the cols produced by the UNION */
SELECT 
   a.`num`, 
   a.`post_uuid`,
   a.`mode`,
  /* And other columns from `posts` if needed */
   b.`something`,
   b.`something_else`,
   b.`some_other_thing`
/* UNIONs are a derived table, belonging in FROM */
FROM (
    (
        SELECT COUNT(*) as num, post_uuid, 'upvotes' AS 'mode'
        FROM post_votes a
        WHERE a.vote = 1
        GROUP BY post_uuid
    )
    UNION
    (
        SELECT COUNT(*) num, post_uuid, 'downvotes' AS 'mode'
        FROM post_votes a
        WHERE a.vote = 1
        GROUP BY post_uuid
    )
) a
LEFT JOIN posts b ON a.post_uuid = b.post_uuid

Глядя на это, я считаю, что вы можете упростить UNION, используя совокупный шаблон SUM(CASE):

SELECT 
  b.post_uuid,
  SUM(CASE WHEN a.vote = 1 THEN 1 ELSE 0 END) AS upvotes,
  /* Assume -1 for downvotes? */
  SUM(CASE WHEN a.vote = -1 THEN 1 ELSE 0 END) AS downvotes
FROM
  posts b
  LEFT JOIN post_votes a ON a.post_uuid = b.post_uuid
GROUP BY b.post_uuid
person Michael Berkowski    schedule 26.11.2012
comment
Спасибо, это работает очень хорошо! Тоже очень хорошее объяснение! Есть идеи, как сделать переменную mode заголовком для COUNT(*)? потому что простое выполнение COUNT(*) as 'upvotes' бросает мне в лицо большую жирную ошибку - person Axel Latvala; 26.11.2012
comment
COUNT(*) AS upvotes должно работать нормально, однако, как только вы UNION их объедините, будет трудно отслеживать, что есть что, а без UNION ALL вы можете потерять некоторые строки. Я назвал COUNT() псевдонимом num выше. - person Michael Berkowski; 26.11.2012
comment
Если вы опубликуете, какую ошибку вы получили при попытке использовать псевдоним upvotes, возможно, я смогу устранить неполадки. - person Michael Berkowski; 26.11.2012
comment
Похоже, я не могу воспроизвести ошибку, скорее, я теряю либо плюс, либо минус. - person Axel Latvala; 26.11.2012
comment
@Akke В любом случае, просмотрите только что добавленный фрагмент, потому что я думаю, что он решает все без UNION. В вашем запросе у вас есть a.vote = 1 как вверх, так и вниз, но должно ли вниз быть -1? - person Michael Berkowski; 26.11.2012
comment
Получение ошибки здесь: ОШИБКА SQL (1052): столбец «post_uuid» в списке полей неоднозначен. - person Axel Latvala; 26.11.2012
comment
@Micael: SELECT и GROUP BY b.post_uuid (не a.) - person ypercubeᵀᴹ; 26.11.2012
comment
Кажется, работает отлично, однако я получаю нулевую строку в начале - person Axel Latvala; 26.11.2012
comment
@ypercube Да, я поменял стороны соединения и пропустил исправление группы. - person Michael Berkowski; 26.11.2012
comment
@Akke Измените выбор на b.post_uuid, и вы не должны получить строку NULL. - person Michael Berkowski; 26.11.2012
comment
Еще один быстрый вопрос. Как я могу ссылаться на выбранные cols upvotes и downvotes при попытке выбрать upvotes-downvotes в качестве рейтинга? - person Axel Latvala; 26.11.2012
comment
@Akke в том же запросе вам придется использовать полные вычисления, но вы можете обернуть все это в подзапрос, и можно использовать имена положительных и отрицательных голосов. - person Michael Berkowski; 26.11.2012