一个车辆表Trucks,车辆号为Truck_ID,货物表Goods记录装车的批次,例如:第一批在Truck_ID为1的车辆上装入6斤a类货物。表名:Trucks
Truck_ID
1
2
3表名:Goods
Truck_ID Amount Stuff_type
1 6 a
1 4 a
1 6 b
1 9 b
1 3 b
1 4 b
2 8 a
2 3 b
2 1 b
2 6 c
2 2 c
3 5 a
3 7 a
3 2 a
3 4 c
3 6 c
现需要查询统计出每辆车中所装每种货物的重量。
我刚开始是这样写的:
Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ group by t.Truck_ID order by t.Truck_ID
这样查询的结果是:
Truck_ID a
1 10
2 8
3 14
但如果想同时查各车辆上a和b的重量时:
我写的是
Select t.Truck_ID, sum(g.Amount) as a,sum(g2.Amount) from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ left join Goods g2 on g2.Truck_ID=t.Truck_ID and g2.Stuff_type=’b’ group by t.Truck_ID order by t.Truck_ID
查询结果是:
Truck_ID a b
1 40 44
2 16 4
3 14
该结果与预期结果数据不符,a,b的值都被乘以数倍,高手看看这个sql应该怎么修改?
Truck_ID
1
2
3表名:Goods
Truck_ID Amount Stuff_type
1 6 a
1 4 a
1 6 b
1 9 b
1 3 b
1 4 b
2 8 a
2 3 b
2 1 b
2 6 c
2 2 c
3 5 a
3 7 a
3 2 a
3 4 c
3 6 c
现需要查询统计出每辆车中所装每种货物的重量。
我刚开始是这样写的:
Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ group by t.Truck_ID order by t.Truck_ID
这样查询的结果是:
Truck_ID a
1 10
2 8
3 14
但如果想同时查各车辆上a和b的重量时:
我写的是
Select t.Truck_ID, sum(g.Amount) as a,sum(g2.Amount) from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ left join Goods g2 on g2.Truck_ID=t.Truck_ID and g2.Stuff_type=’b’ group by t.Truck_ID order by t.Truck_ID
查询结果是:
Truck_ID a b
1 40 44
2 16 4
3 14
该结果与预期结果数据不符,a,b的值都被乘以数倍,高手看看这个sql应该怎么修改?
解决方案 »
- 如何将自已用户下的所有表的查询权限授予其他用户,是所有哦
- 向各位oracle大牛们求救一个sql问题
- sqlplus问题求助
- Oracle 通过sql语句实现按照数值区间分组统计求和
- oracle 10g中create function出现Statement ignored错误,大家帮下忙啊!
- SQL> startup mountORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
- oracle安装问题求救?(加载数据库出错 areasQueris)
- Oracle Exp问题?
- sql语句问题,(连接查询)
- 这道题怎么做呀?
- 从sql转oracle
- 请教关于去掉重复数据的oracle的sql问题
t.Truck_ID,
sum(case when g.Stuff_type='a' then g.Amount else 0 end) as a,
sum(case when g.Stuff_type='a' then g.Amount else 0 end) as b
from Trucks t
left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type in('a','b') group by t.Truck_ID
Select
t.Truck_ID,
sum(case when g.Stuff_type='a' then g.Amount else 0 end) as a,
sum(case when g.Stuff_type='b' then g.Amount else 0 end) as b
from Trucks t
left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type in('a','b')
group by t.Truck_ID Select
t.Truck_ID,
sum(decode(g.Stuff_type,'a' , g.Amount , 0 ) as a,
sum(decode(g.Stuff_type,'b' , g.Amount , 0 ) as b
from Trucks t
left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type in('a','b')
group by t.Truck_ID
SELECT TA.Truck_ID,TA.a,TB.a
FROM
(Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ group by t.Truck_ID order by t.Truck_ID) TA,
(Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ group by t.Truck_ID order by t.Truck_ID) TB,
WHERE TA.Truck_ID = TB.Truck_ID;
FROM
(Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’a’ group by t.Truck_ID order by t.Truck_ID) TA,
(Select t.Truck_ID, sum(g.Amount) as a from Trucks t left join Goods g on g.Truck_ID=t.Truck_ID and g.Stuff_type=’b’ group by t.Truck_ID order by t.Truck_ID) TB,
WHERE TA.Truck_ID = TB.Truck_ID;
直接查询goods,就能得到你想要的数据!
select Truck_ID, Stuff_type,sum( mut) from goods group by Truck_ID,Stuff_type;你可以试试。我已经在mysql里试过了。
我的理解是trunks表是用来做左连接的,比如说goods表里没有trunk_id = 2的记录时能够显示出2对数量为0,而不是没有记录。