我正在尝试获取一个订单报告,该报告不仅返回客户的订单总数,而且还返回另外三个字段,其中包含第一个和最后一个订单的日期,以及第一个和最后一个订单之间的范围(以天为单位)
以下查询
SELECT customers.name,
customers.customer_id,
COUNT(orders.order_id) AS Orderscount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name,
customers.customer_id
HAVING Orderscount >= 2
ORDER BY Orderscount DESC
返回名称,customer_id和订单总数的表。
问题是,如何才能添加到此查询中以同时获取first_order,last_order的日期以及两者之间的日期范围?
我正在使用SQLite。
使用MIN
和MAX
获取第一个和最后一个订购日期并julianday()
计算范围:
SELECT customers.name,
customers.customer_id,
COUNT(orders.order_id) AS Orderscount,
MIN(col_name) AS firstOrder,
MAX(col_name) AS lastOrder,
ROUND(julianday(MAX(col_name)) - julianday(MIN(col_name))) AS range
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name,
customers.customer_id
HAVING Orderscount >= 2
ORDER BY Orderscount DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句