sql查询难题,对行数据统计和列数据统计问题

孙斐 发布于 2012/02/11 20:52
阅读 1K+
收藏 0

现有一个公司信息表,一个公司所属车辆表,现在要做一个统计视图,包括公司的基本信息,如名称,地址等,还包括公司所有的车辆总数,车辆座位总数,车辆吨位总数等,我写了一个sql,

create or replace view  company_view as

select c.name ,c.address,

(select sum(v.id) from vehicle v  where v.companyId = c.id) as sumvehicle,

(select sum(v.ton) from vehicle v  where v.companyId = c.id) as sumvehicleton 

from company c

left join vehicle  v on v.companyid = c.id ;

因为要对视图进行where条件过滤查询,这个执行效率太低,有位朋友帮忙修改了一下,如:

create or replace view  company_view as

select c.name ,c.address,sumvehicle ,sumvehicleton

from company c,(select v.id as id,sum(v.id) as sumvehicle  ,sum(v.ton) as sumvehicleton from vehicle v group by v.id)t

where t.id=c.id;效率提高了;但如果统计字段不在列上加却是在行上统计怎么办?

比如车辆的类型有v_type 的值有大型,小型,中型等,车辆的种类有货车,危货车,普通客运车,卧铺客运车等,要在这个视图中统计大型车有多少?卧铺车有多少,写了一些sql,但效率都不高呀!!!!

加载中
1
hulubo
hulubo
create or replace view  company_view as

select c.name ,c.address,v_type,sumvehicle ,sumvehicleton 

from company c,(select v.id as id,v.v_type as v_type, sum(v.id) as sumvehicle  ,sum(v.ton) as sumvehicleton from vehicle v group by v.id,v.v_type)t
where t.id=c.id ;

就在你的sql中加入type做group by的条件,会得到比原来更明细的数据。

如果要分类型和不分类型的一起统计(你sum(id)是想统计车辆数吗?应该用count):

create or replace view  company_view as

select c.name ,c.address,v_type,sumvehicle ,sumvehicleton 

from company c,(select v.id as id,v.v_type as v_type,
 count(v.id) as count_vehicle_type ,
 count(count(v.id)) over (partition by v.v_id) as count_vehicle ,
sum(v.ton) as sumvehicleton_type,
 sum(sum(v.ton)) over (partition by v.id) as sum_vehicleton 
 from vehicle v group by v.id,v.v_type)t
where t.id=c.id ;

注:以上语句没有验证。以oracle 分析函数为基础。

0
mark35
mark35
但如果统计字段不在列上加却是在行上统计怎么办
那就再写个视图呗~

 

0
hulubo
hulubo

引用来自“hulubo”的答案

create or replace view  company_view as

select c.name ,c.address,v_type,sumvehicle ,sumvehicleton 

from company c,(select v.id as id,v.v_type as v_type, sum(v.id) as sumvehicle  ,sum(v.ton) as sumvehicleton from vehicle v group by v.id,v.v_type)t
where t.id=c.id ;

就在你的sql中加入type做group by的条件,会得到比原来更明细的数据。

如果要分类型和不分类型的一起统计(你sum(id)是想统计车辆数吗?应该用count):

create or replace view  company_view as

select c.name ,c.address,v_type,sumvehicle ,sumvehicleton 

from company c,(select v.id as id,v.v_type as v_type,
 count(v.id) as count_vehicle_type ,
 count(count(v.id)) over (partition by v.v_id) as count_vehicle ,
sum(v.ton) as sumvehicleton_type,
 sum(sum(v.ton)) over (partition by v.id) as sum_vehicleton 
 from vehicle v group by v.id,v.v_type)t
where t.id=c.id ;

注:以上语句没有验证。以oracle 分析函数为基础。

有一个明显错误的地方,count(count(v.id)) 应该是sum(count(v.id))

create or replace view  company_view as

select c.name ,c.address,v_type,sumvehicle ,sumvehicleton 

from company c,(select v.id as id,v.v_type as v_type,
 count(v.id) as count_vehicle_type ,
 sum(count(v.id)) over (partition by v.v_id) as count_vehicle ,
sum(v.ton) as sumvehicleton_type,
 sum(sum(v.ton)) over (partition by v.id) as sum_vehicleton 
 from vehicle v group by v.id,v.v_type)t
where t.id=c.id ;

返回顶部
顶部