我有一张table items
在商店里,如果同一件商品后来以不同的价格再次购买(这里的价格是一件商品花了商店多少钱),那么一件商品可能有相同序列号(sn)的不同条目(但ID不同)
id | sn | amount | price
----+------+--------+-------
1 | AP01 | 100 | 7
2 | AP01 | 50 | 8
3 | X2P0 | 200 | 12
4 | X2P0 | 30 | 18
5 | STT0 | 20 | 20
6 | PLX1 | 200 | 10
还有一张table transactions
```
id | item_id | price
----+---------+-------
1 | 1 | 10
2 | 1 | 9
3 | 1 | 10
4 | 2 | 11
5 | 3 | 15
6 | 3 | 15
7 | 3 | 15
8 | 4 | 18
9 | 5 | 22
10 | 5 | 22
11 | 5 | 22
12 | 5 | 22
以及 `transaction.item_id references items(id)` 我想按序列号(sn)对项目进行分组,得到它们的总和(amount)和平均值(price),并用一个 `sold` 统计具有引用id的事务数的列
我第一次和你在一起
select i.sn, sum(i.amount), avg(i.price) from items i group by i.sn;
sn | sum | avg
------+-----+---------------------
STT0 | 20 | 20.0000000000000000
PLX1 | 200 | 10.0000000000000000
AP01 | 150 | 7.5000000000000000
X2P0 | 230 | 15.0000000000000000
然后,当我尝试将它加入到事务中时,我得到了奇怪的结果
select i.sn, sum(i.amount), avg(i.price) avg_cost, count(t.item_id) sold, sum(t.price) profit from items i left join transactions t on (i.id=t.item_id) group by i.sn;
sn | sum | avg_cost | sold | profit
------+-----+---------------------+------+--------
STT0 | 80 | 20.0000000000000000 | 4 | 88
PLX1 | 200 | 10.0000000000000000 | 0 | (null)
AP01 | 350 | 7.2500000000000000 | 4 | 40
X2P0 | 630 | 13.5000000000000000 | 4 | 63
如你所见,只有 `sold` 以及 `profit` 列显示正确的结果,sum和avg显示与预期不同的结果
我无法分隔语句,因为我不确定如何将计数添加到以item\u id作为其id的sn组中?
select
j.sn,
j.sum,
j.avg,
count(item_id)
from (
select
i.sn,
sum(i.amount),
avg(i.price)
from items i
group by i.sn
) j
left join transactions t
on (j.id???=t.item_id);
1条答案
按热度按时间a14dhokn1#
两个表中都有多个匹配项,因此
join
将行相乘(最终生成wron结果)。我建议先加入,然后聚合: