Mysql 使用嵌套 Select 进行连接

Question

我试图使用此 SQL 获取每个卖家每年、每月和每天的所有订单总数。我仍然在思考连接，但据我所知，我认为这应该可行

SELECT sellers.username, sellers.registerDate, sellers.sellerid,
orders.orderPrice, orders.orderDate, orders.sellerid,
count(orders.orderPrice) AS products, SUM( orders.orderPrice ) AS total,
FROM  `sellers` 
JOIN
`orders`,
(SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE YEAR( orderDate ) = YEAR( CURDATE( ) ) ) AS year,
(SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE MONTH( orderDate ) = MONTH( CURDATE( ) ) ) AS month,
(SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE orderDate = CURDATE() ) AS day
ON orders.sellerid = sellers.sellerid
GROUP BY sellers.username
HAVING total > 0
ORDER BY total desc
LIMIT 0 , 4

但它给了我一个错误(#1064 - 第 9 行“ONorders.sellerid = sellers.sellerid GROUP BY sellers.username HAVING”附近)

Answer 1

You need to set ON clause on every join table not only the lastone. 

问候

JOIN
    `orders` ON ??? = ???,
    (SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE YEAR( orderDate ) = YEAR(   CURDATE( ) ) ) AS year ON orders.sellerid = sellers.sellerid,
    (SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE MONTH( orderDate ) = MONTH( CURDATE( ) ) ) AS month ON orders.sellerid = sellers.sellerid,
    (SELECT SUM( orders.orderPrice ) FROM  `orders` WHERE orderDate = CURDATE() ) AS day 
         ON orders.sellerid = sellers.sellerid