MySQL 子查询返回多行

Question

SELECT CONCAT(MONTH(revenue_date),"/ ", YEAR(revenue_date)) as month_year, 

    (SELECT COUNT(DISTINCT DATE(delivery_date)) as operating_days
    FROM table2 where weekday(delivery_date) <> 5 and     
    weekday(delivery_date) <> 6 and delivery_date < current_time and     
    year(delivery_date) > 2015 GROUP BY YEAR(delivery_date), MONTH(delivery_date)), 
sum(revenue) as total_revenue,
from table1 
where revenue_date < current_time and year(revenue_date) > 2015
group by year(revenue_date), month(revenue_date);

这显示错误“子查询返回超过 1 个值”。如果我不在子查询中包含 group by ，它只会返回所有月份的总计，但我需要它单独返回所有月份。

Answer 1

试试这个:

SELECT CONCAT(MONTH(revenue_date),"/ ", YEAR(revenue_date)) as month_year, a.operating_days
sum(revenue) as total_revenue,
from table2 JOIN
(SELECT YEAR(delivery_date) as `year`, MONTH(delivery_date) as `month`, COUNT(DISTINCT DATE(delivery_date)) as operating_days
    FROM table2 where weekday(delivery_date) <> 5 and     
    weekday(delivery_date) <> 6 and delivery_date < current_time and     
    year(delivery_date) > 2015 GROUP BY YEAR(delivery_date), MONTH(delivery_date)) a
ON year(revenue_date) = a.year AND month(revenue_date) = a.month
where revenue_date < current_time and year(revenue_date) > 2015
group by year(revenue_date), month(revenue_date);