Mysql:如何查询具有2个不同条件的1个表，并在单独的列中显示结果

Question

我有一个名为ItemDelivery的表。我想得到每月具有DeliveryDate的项目数和已接收日期的项目数。有些项目deliveryDate月份有不同的receiveDate月份，例如计划在该月晚些时候交付的项目将在下个月的早期收到。有些可能需要几个月才能送达海外。

这是数据：

id  iditem  deliveryDate    receiveDate
1   2       2021-01-03      2021-01-05
2   2       2021-01-03  
3   3       2021-02-05      2021-02-06
4   5       2021-02-05  
5   4       2021-02-20      2021-03-01
6   3       2021-03-15      2021-04-08

我想要

Mo  Delivery Recieve
Jan   2        1
Feb   3        1
Mar   1        1
Apr   0        1

此查询只提供1列

select date_format(deliveryDate,'%b') as mo ,
count(id) as delivery 
from ItemDelivery 
where year(deliveryDate)=2021
group by month(deliveryDate)
union all
select date_format(receiveDate,'%b') as mo ,
count(id) as received 
from ItemDelivery 
where year(receiveDate)=2021
group by month(receiveDate)

输出：

Mo  Delivery
Jan 2
Feb 3
Mar 1
Jan 1
Feb 1
Mar 1
Apr 1

该查询也有不同的输出

SELECT d1.mo, d1.delivery, d2.received
FROM   
(SELECT month(deliveryDate) as mo, count(id) AS delivery
FROM   ItemDelivery
WHERE  year(deliveryDate)=2021 group by month(deliveryDate)) as d1,
(SELECT month(receiveDate) as mo, count(id) AS received
FROM   ItemDelivery
WHERE  year(receiveDate)=2021 group by month(receiveDate)) as d2

输出：

mo  delivery    received
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1
1   2       1
2   3       1
3   1       1

这也有相同的输出，除非我使用条件d1.mo=d2.mo:

select d1.mo, d1.delivery, d2.received
from 
(SELECT month(deliveryDate) as mo, count(id) as delivery
FROM   ItemDelivery
WHERE  year(deliveryDate)=2021 group by month(deliveryDate)) d1

inner join 
(SELECT month(receiveDate) as mo, count(id) as received
FROM   ItemDelivery
WHERE  year(receiveDate)=2021 group by month(receiveDate)) d2

有什么建议吗？

Answer 1

SELECT
date_format(eventDate,'%b') AS mo,
SUM(delivery)               AS delivery,
SUM(receive)                AS receive
FROM
(
SELECT deliveryDate AS eventDate, 1 AS delivery, 0 AS receive FROM ItemDelivery
UNION ALL
SELECT receiveDate  AS eventDate, 0 AS delivery, 1 AS receive FROM ItemDelivery
)
AS rotated
WHERE
eventDate >= '2021-01-01'
AND eventDate <  '2022-01-01'
GROUP BY
month(eventDate)