mySQL 两个日期之间的平均差异

Question

有人可以帮我解决这个问题吗？我正在查看 Accommodation_offer.created_at 和 booking.requested_at，了解第一个结果与第二个找到的最小结果之间的差异。

我在“字段列表”中收到未知列“minbooking”错误

我创建了 minbooking，因为我最初相信嵌套选择将有助于我最初的 AVG(DATEDIFF(MIN( 查询)。从那时起，我删除了嵌套选择，并一直在尝试这种方式，但没有成功.

顺便说一句，一个优惠可以有多个预订。我希望将结果按城市分组，然后按优惠创建日期分组，然后显示创建优惠与每个优惠首次预订之间的平均时间差。

SELECT
accommodation_offer.created_at_month AS "Month of Offer Created",
property.address_city_code AS "City",
MIN(booking.requested_at) AS minbooking,
AVG(DATEDIFF(minbooking,accommodation_offer.created_at)) AS "Time Span in     Days"
FROM booking
INNER JOIN accommodation_offer
ON accommodation_offer.id=booking.offer_id
INNER JOIN property
ON accommodation_offer.property_id=property.id
GROUP BY property.address_city_code, accommodation_offer.created_at_month
ORDER BY property.address_city_code, accommodation_offer.created_at_month ASC

Answer 1

试试这个

SELECT
accommodation_offer.created_at_month AS "Month of Offer Created",
property.address_city_code AS "City",
MIN(booking.requested_at) AS minbooking,
AVG(DATEDIFF(MIN(booking.requested_at),accommodation_offer.created_at_month)) AS  
"Time Span in     Days"
FROM booking
INNER JOIN accommodation_offer
ON accommodation_offer.id=booking.offer_id
INNER JOIN property
ON accommodation_offer.property_id=property.id
GROUP BY property.address_city_code, accommodation_offer.created_at_month
ORDER BY property.address_city_code, accommodation_offer.created_at_month 
ASC