gpt4 book ai didi

mysql - 将两个查询与计数函数合并为一个结果集

转载 作者:行者123 更新时间:2023-11-29 08:28:23 24 4
gpt4 key购买 nike

我的 mySQL 数据库中有两个表

Table 1 [Orders]
DateOpened (date),
revenue

Table 2 [Movements]
DateStarted (date),
DateStopped (date)

对于任何给定日期,表 1 中可能有 0 个或多个条目,而对于表 2,DateStartedDateStopped 可能有 0 个或多个条目( DateStarted 和 DateStopped 可以是同一天,也可以是不同)

我想查询这些表的日期范围(例如:一个月),并获取包含以下形式的一些数据的每个日期的结果集:

date, sum(revenue), num_orders (count of orders for the date), num_movements (count of movements for the date)

我创建了两个单独的查询来完成每个部分,但我不知道如何组合它们,以便我可以实现单个有效的查询,而不必在代码中合并结果集。我似乎很困惑的是 count() 函数是否正确应用。

以下是现有的有效查询:

select count(*) as total_movements, movement_date
from (
select dropoffdate as movement_date
from bb_movement
where (dropoffdate >= '2013-01-01' and dropoffdate <= '2013-06-30')
union all
select pickupdate as movement_date
from bb_movement
where (pickupdate >= '2013-01-01' and pickupdate <= '2013-06-30')
) as t3
group by movement_date
order by movement_date asc

select count(*) as num_orders, sum(order_amount) as revenue, dateopened as date_opened
from bb_order
where (dateopened >= '2013-01-01' and dateopened <= '2013-06-30')
group by dateopened
order by dateopened asc

如果不存在则创建表bb_movement ( movementid int(10) 无符号 NOT NULL AUTO_INCRMENT, orderid int(10) 无符号非空注释 dropoffdate 日期不为空, pickupdate 日期 NOT NULL )

如果不存在则创建表bb_order ( orderid int(10) 无符号 NOT NULL AUTO_INCRMENT, movementid int(10) 无符号 NOT NULL, dateopened 日期不为空, order_amount float NOT NULL DEFAULT '0'}

最佳答案

已解决:它需要将源表与主选择中的适当聚合函数进行联合

 SELECT mv_date, sum(mv_count), sum(order_count), sum(order_revenue)
FROM (
select dateopened as mv_date, 0 as mv_count, count(*) as order_count, sum(order_amount) as order_revenue
from bb_order
where (dateopened >= '2013-01-01' and dateopened <= '2013-06-30')
group by mv_date
union all
SELECT dropoffdate AS mv_date, COUNT( * ) AS mv_count, 0 as order_count, 0 as order_revenue
FROM bb_movement
WHERE (dropoffdate >= '2013-01-01' AND dropoffdate <= '2013-06-30')
group by mv_date
UNION all
SELECT pickupdate AS mv_date, COUNT( * ) AS mv_count, 0 as order_count, 0 as order_revenue
FROM bb_movement
WHERE (pickupdate >= '2013-01-01' AND pickupdate <= '2013-06-30')
group by mv_date
) mv
group by mv_date
order by mv_date

关于mysql - 将两个查询与计数函数合并为一个结果集,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17344199/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com