使用分组依据的MySQL查询
使用分组依据的MySQL查询
提问于 2012-02-09 03:19:33
我尝试过许多不同的查询，但没有找到这个项目的正确语法。我真的希望有人能理解我的烂摊子。
我有一个包含订单的表，每个订单一行，每行包含客户编号、PO#和订单日期等信息。PO编号可以绑定到一个或多个订单，这就是我迷路的地方。我正在尝试创建一个查询，该查询将生成一行，其中包含客户编号、PO#、最早订单的日期和最后订单的日期，并按PO#进行分组。
任何指向正确方向的指针都将不胜感激！
回答 3
Stack Overflow用户
回答已采纳
发布于 2012-02-09 03:24:09
这将会得到顺序和范围的东西。
select
o.PONumber,
o.CustomerID,
count(*) as TotalOrders,
min( o.OrderID ) as FirstOrder,
min( o.OrderDate) as FirstOrderDate,
max( o.OrderID ) as LastOrder,
max( o.OrderDate) as LastOrderDate
from
Orders o
group by
o.PONumber,
o.CustomerIDStack Overflow用户
发布于 2012-02-09 03:24:47
试试这个：
SELECT
o1.`customer_number` as 'customer',
o1.`po` as 'po_number',
(
SELECT
MIN(`date`)
FROM
`orders`
WHERE
`id` = o1.`id`
) as 'earliest_date'
FROM
`orders` o1
GROUP BY
o1.`po`希望这能有所帮助。
Stack Overflow用户
发布于 2012-02-09 03:39:57
SELECT o.custumer, o.po_number, o.order_count, max.max_date, min.min_date
FROM
(
SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', count('order_number') as 'order_count'
FROM 'orders' o
GROUP BY o.'costumer_number', o.'po'
) o
INNER JOIN (
SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', MAX('order_date') as 'max_date'
FROM 'orders' o
GROUP BY o.'costumer_number', o.'po'
) max ON o.'customrer' = max.'costumer' and o.'po_number' = max.'po_number'
INNER JOIN (
SELECT o.'costumer_number' as 'custumer', o.'po' as 'po_number', min('order_date') as 'min_date'
FROM 'orders' o
GROUP BY o.'costumer_number', o.'po'
) min ON o.'customrer' = min.'costumer' and o.'po_number' = min.'po_number'
ORDER BY 1,2这应该能把它弄清楚
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9199945
复制相关文章
相似问题
腾讯云开发者
