select company _name, sum(decode(product_year,2005,1,0)) "2005产品数", sum(decode(product_year,2004,1,0)) "2004产品数", ... from 表1,表2 where 表1.company_id = 表2.company_id group by 表1.company _name
下面2种方法你看看哪种符合你你要求,还不行估计就很麻烦了: 1. select company _name, sum(case product_year when 2005 then 1 else 0 end)) "2005产品数", sum(case product_year when 2004 then 1 else 0 end)) "2004产品数", ... from 表1,表2 where 表1.company_id = 表2.company_id group by 表1.company _name2. select company _name, sum(case when product_year = 2005 then 1 else 0 end)) "2005产品数", sum(case when product_year = 2004 then 1 else 0 end)) "2004产品数", ... from 表1,表2 where 表1.company_id = 表2.company_id group by 表1.company _name
我是这样做的 select company_iid, product_cnt2005=( select count(*) from re_product where product_year='2005'), ...... from re_company group by company_iid
sum(decode(product_year,2005,1,0)) "2005产品数",
sum(decode(product_year,2004,1,0)) "2004产品数",
...
from 表1,表2
where 表1.company_id = 表2.company_id
group by 表1.company _name
这一行中的列包含各个年份公司名 2005 2004 ...
微软 10个 20个
IBM 8个 12个
你的方案是不是oracle才能用? 能不能写的通用些
1.
select company _name,
sum(case product_year when 2005 then 1 else 0 end)) "2005产品数",
sum(case product_year when 2004 then 1 else 0 end)) "2004产品数",
...
from 表1,表2
where 表1.company_id = 表2.company_id
group by 表1.company _name2.
select company _name,
sum(case when product_year = 2005 then 1 else 0 end)) "2005产品数",
sum(case when product_year = 2004 then 1 else 0 end)) "2004产品数",
...
from 表1,表2
where 表1.company_id = 表2.company_id
group by 表1.company _name
select company_iid,
product_cnt2005=( select count(*) from re_product where product_year='2005'),
......
from re_company
group by company_iid