mysql - SQL UNION 奇怪错误

Question

我正在开发一个彩票应用程序，我想检查用户彩票上的任何数字是否与任何已抽出的球相匹配，每场比赛得 2 分。到目前为止，我有以下查询:

SELECT firstName, SUM(points) AS totalPoints FROM (
(SELECT userID, firstName, 2 * COUNT(*) AS points
FROM DRAWS NATURAL JOIN TICKETS NATURAL JOIN USERS
WHERE
    number1 = ball1 OR
    number1 = ball2 OR
    number1 = ball3 OR
    number1 = ball4
GROUP BY userID, drawID) AS N1
UNION ALL
(SELECT userID, firstName, 2 * COUNT(*) AS points
FROM DRAWS NATURAL JOIN TICKETS NATURAL JOIN USERS
WHERE
    number2 = ball1 OR
    number2 = ball2 OR
    number2 = ball3 OR
    number2 = ball4
GROUP BY userID, drawID) AS N2
UNION ALL
(SELECT userID, firstName, 2 * COUNT(*) AS points
FROM DRAWS NATURAL JOIN TICKETS NATURAL JOIN USERS
WHERE
    number3 = ball1 OR
    number3 = ball2 OR
    number3 = ball3 OR
    number3 = ball4
GROUP BY userID, drawID) AS N3 ) AS TOT
GROUP BY userID;

MySQL 说:文档1064 - 您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在 'AS N2 附近使用的正确语法联合所有(SELECT userID, firstName, 2 * COUNT(*) AS points来自第 18 行的 DRA'

表的结构如下:

CREATE TABLE USERS (
userID INTEGER PRIMARY KEY,
firstName CHAR(16),
lastName CHAR(16),
email CHAR(32) UNIQUE,
phone CHAR(16),
userName CHAR(20),
passWord CHAR(20),
balance INTEGER );

CREATE TABLE TICKETS (
ticketID INTEGER PRIMARY KEY,
userID INTEGER,
drawID INTEGER,
date DATE,
number1 INTEGER,
number2 INTEGER,
number3 INTEGER,
score INTEGER,
prize INTEGER);

CREATE TABLE DRAWS (
drawID INTEGER PRIMARY KEY,
dateOpen DATE,
dateClose DATE,
gameID INTEGER,
ball1 INTEGER,
ball2 INTEGER,
ball3 INTEGER,
ball4 INTEGER,
bonusBall INTEGER,
dateDrawn DATE,
profit INTEGER, 
charityID INTEGER );

我认为这是我的 UNION 的问题，因为个别查询工作正常。我一直在努力解决这个问题，但我做不到，所以我想我会问问专业人士。有什么建议吗？

Answer 1

删除括号和别名，因为 UNION ALL 与 join 不同，它将多个 SELECT 语句的结果合并到一个结果集中:

SELECT firstName, SUM(points) AS totalPoints FROM
(SELECT userID, firstName, 2 * COUNT(*) AS points
FROM DRAWS NATURAL JOIN TICKETS NATURAL JOIN USERS
WHERE
    number1 = ball1 OR
...
GROUP BY userID, drawID
UNION ALL
SELECT userID, firstName, 2 * COUNT(*) AS points
FROM DRAWS NATURAL JOIN TICKETS NATURAL JOIN USERS
WHERE
    number2
...
) AS Tot