我有一个问题，所以我有两个表：
订单：
id supplier_id status
1 2 3
2 4 5
3 2 7
gift_2_order:
order_id gift_id order_price
1 2 4
2 1 5
3 2 6
1 6 1
2 4 9
所以我想得到所有的订单，表中重复了多少次：gift_2_order和total price。对于这个例子，我想得到：
order number_repetitions total_price
1 2 5
2 2 14
3 1 6
我试过了：
SELECT
ord.estimation_date,
ord.order_date,
ord.supplier_id,
ord.status,
count.(g2o.order_id),
sum.(g2o.order_price)
FROM `order` ord
INNER JOIN gift_2_order g2o on ord.id = g2o.order_id
你能帮我吗？提前谢谢。
您必须按ord.id分组，并从count and sum 中删除.
SELECT
ord.id,
count(g2o.order_id),
sum(g2o.order_price)
FROM `order` ord
INNER JOIN gift_2_order g2o on ord.id = g2o.order_id
group by ord.id order by ord.id
尝试：
SELECT
ord.order_id,
count(g2o.order_id) as number_repetitions ,
sum(g2o.order_price) as total_price
FROM `order` ord
INNER JOIN gift_2_order g2o on ord.id = g2o.order_id
group by ord.order_id
顺便说一句，"order"并不是一个很好的表名。
SELECT
t1.supplier_id,
t1.status,
count(t2.order_id) as total_orders,
sum(t2.order_price) as total_amount
FROM `order` t1
join gift_2_order t2 where t1.id = t2.order_id group by t2.order_id
您不需要执行JOIN。如果没有JOIN，查询将更具性能。。。
这个SQL小提琴将为您做三件事：
SELECT
order_id,
count(order_id),
sum(order_price)
FROM gift_2_order
GROUP BY order_id
ORDER BY order_id
如果您不需要联接表，就不要这样做。