我有 1 個名為 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
有什么建議 ？
uj5u.com熱心網友回復：
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432314.html