mysql - 尝试构建类似选择的 MySQL 查询失败

Question

背景信息

系统

操作系统:

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'

Answer 1

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

查看此内容，我相信您可以使用 SUM(CASE) 聚合模式简化 UNION:

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