mysql - MySQL 连接查询问题

Question

我正在尝试加入 2 个不同的查询。每个查询单独工作，但在加入时不工作。

SELECT * 
    FROM (
    
    
    SELECT i.id AS wid, i.start_time, i.end_time, i.drawing_time, i.result, SUM( t.amount ) AS wpAmount, SUM( t.prize ) , COUNT( t.id ) AS cntWinTickets
    FROM issues i
    LEFT JOIN orders o ON o.issue_id = i.id
    LEFT JOIN tickets t ON t.order_id = o.id
    AND t.has_prize =1
    GROUP BY i.id
    ) AS A
    JOIN 
    SELECT * 
    FROM (

SELECT i.id AS lid, SUM( t.amount ) AS lpAmount, COUNT( t.id ) AS cntLoosTickets
FROM issues i
LEFT JOIN orders o ON o.issue_id = i.id
LEFT JOIN tickets t ON t.order_id = o.id
AND t.has_prize !=1
GROUP BY i.id
) AS B ON A.wid = B.lid

内部查询工作正常，但加入后就无法工作。

错误信息如下:

MySQL 说:

#1064 - 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 'SELECT * FROM (
SELECT i.id as lid, SUM( t.amount ) AS lpAmount , COUNT( t.id )' at line 12

Answer 1

试试这个(在 JOIN 之后删除不必要的 SELECT):

SELECT * 
    FROM 
    (
    SELECT i.id AS wid, i.start_time, i.end_time, i.drawing_time, i.result, SUM( t.amount ) AS wpAmount, SUM( t.prize ) , COUNT( t.id ) AS cntWinTickets
    FROM issues i
    LEFT JOIN orders o ON o.issue_id = i.id
    LEFT JOIN tickets t ON t.order_id = o.id
    AND t.has_prize =1
    GROUP BY i.id
    ) AS A
    JOIN
    (
    SELECT i.id AS lid, SUM( t.amount ) AS lpAmount, COUNT( t.id ) AS cntLoosTickets
    FROM issues i
    LEFT JOIN orders o ON o.issue_id = i.id
    LEFT JOIN tickets t ON t.order_id = o.id
    AND t.has_prize !=1
    GROUP BY i.id
    ) AS B ON A.wid = B.lid