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

发布于 2025-01-09 05:54:29 字数 2078 浏览 3 评论 0原文

我有 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

有什么建议吗?

I have 1 table named ItemDelivery. I wanted to get the count of items that has DeliveryDate and the items that has been receivedDate per month. Some items deliveryDate month have different receiveDate month such as items scheduled for delivery on the later part of the month would be received on early days of succeeding month. Some may take months to be delivered for overseas.

This is the data:

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

I would like to have

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

This query gives 1 columns only

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)

Output:

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

This query also have different output

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

Output:

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

This has also the same output except if I use condition 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

Any suggestions ?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

你列表最软的妹 2025-01-16 05:54:29
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)
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)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文