oracle9i单表分组问题，有点搞人！

Database: Oracle9iU2
有这么个表:employee
id,name,birthday,duty,degree,title,degree_title
报表格式如图:

评论

15 楼 lumi 2008-03-10  

感觉没什么难的,我做的报表比你的复杂多了!

用4次行转列,然后再进行连接操作,一个sql搞定!

14 楼 javaTo 2008-03-10  

为什么非要在查询语句中费那么大劲呢！你这种sql，如果并发量稍大一点，造成死锁的几率就会很高。
给你说说我们的方案，一句简单的sql把需要的数据查出来，在程序中予以分类或计算，然后写入报表，如果对并发有要求，那么就来他几个线程

13 楼 gstripe 2008-03-08  

多累啊， 存储过程+临时表写下不就好了，非要搞这么负责的强劲SQL

12 楼 seele 2008-03-06  

多个单条SQL的效率 和 单条长SQL的效率 哪一个好?(得出相同的结果)
不可能没有区别的吧..

望有人解释一下...

11 楼 jonakang 2008-03-05  

把它拆成N个简单的sql，问题基本上解决了-_-!

10 楼 jonakang 2008-03-05  

有个查询结果，但是希望能把age1,age2,age3,age4合并为一个column，该如何做呢？
查询结果如图

9 楼 jonakang 2008-03-04  

employee表:
id,name,duty,birthday,degree,title,degree_title

其中duty,degree,title,degree_title是被设计在BasicData表中的，如下2个父子表：basic_data_master(id,code,name),
basic_data_detail(id,master_id,col_two)
basic_data_detail表中的col_two对应的就是duty,degree,title,degree_title的值

我到现在还没有找到一个比较简洁的语句来搞定这个问题。总之，我写出来的语句像是一个得了肥胖症的要死不活的家伙，超级郁闷ing !

8 楼 jonakang 2008-03-04  

主要是在年龄，职称，学历上有个多值判断，这个该怎么弄啊，case when出来的不是我想要的效果。decode的用法不熟，尝试了几把以失败告终，郁闷!

7 楼 LucasLee 2008-03-04  

看了你的SQL，太夸张了点吧，这么多SQL,不应该用一条Group by的SQL搞么？
另外，对这种报表，你可以看看琴棋报表

6 楼 jonakang 2008-03-04  

如果把类型，职称，学位放到另外的表中的话就是多表的交叉表查询了，除了采用临时表的解决方案以外还有别的出路吗？

5 楼 jonakang 2008-03-04  

不知道用什么方法能够使得最后的查询结果按照如图所示显示

4 楼 jonakang 2008-03-04  

修改了下

with  
  a as (select count(t1.id) as lessthan35 from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 <= 35 and t1.duty = '管理人员'),--35> x的   
  b as (select count(t1.id) as morethan36 from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 between 36 and 45 and t1.duty = '管理人员'),--36< x <45的   
  c as (select count(t1.id) as morethan46 from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 between 46 and 55 and t1.duty = '管理人员'),--46< x <55的   
  d as (select count(t1.id) as morethan56 from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 >= 56 and t1.duty = '管理人员'),--56< x的   
  e as (select count(t1.id) as doctor from jdpj_waf_employee t1 where t1.degree = '博士' and t1.duty = '管理人员'),--博士研究生   
  f as (select count(t1.id) as mastor from jdpj_waf_employee t1 where t1.degree = '硕士' and t1.duty = '管理人员'),--硕士研究生   
  g as (select count(t1.id) as bachelor from jdpj_waf_employee t1 where t1.degree = '本科' and t1.duty = '管理人员'),--本科   
  h as (select count(t1.id) as grad from jdpj_waf_employee t1 where t1.degree = '专科' and t1.duty = '管理人员'),--专科   
  i as (select count(t1.id) as vocational from jdpj_waf_employee t1 where t1.degree = '中等职业教育' and t1.duty = '管理人员'),--中等职业教育   
  j as (select count(t1.id) as hight from jdpj_waf_employee t1 where t1.degree = '高中' and t1.duty = '管理人员'),--高中   
  k as (select count(t1.id) as middle from jdpj_waf_employee t1 where t1.degree = '初中及以下' and t1.duty = '管理人员'),--初中及以下   
  l as (select count(t1.id) as senior from jdpj_waf_employee t1 where t1.degree_title = '高级' and t1.duty = '管理人员'),--高级   
  m as (select count(t1.id) as secondary from jdpj_waf_employee t1 where t1.degree_title = '中级' and t1.duty = '管理人员'),--中级   
  n as (select count(t1.id) as junior from jdpj_waf_employee t1 where t1.degree_title = '初级' and t1.duty = '管理人员'),--初级   
  o as (select count(t1.id) as seniortech from jdpj_waf_employee t1 where t1.degree_title = '高级技师' and t1.duty = '管理人员'),--高级技师   
  p as (select count(t1.id) as tech from jdpj_waf_employee t1 where t1.degree_title = '技师' and t1.duty = '管理人员'),--技师   
  q as (select count(t1.id) as worker1 from jdpj_waf_employee t1 where t1.degree_title = '高级工' and t1.duty = '管理人员'),--高级工   
  r as (select count(t1.id) as worker2 from jdpj_waf_employee t1 where t1.degree_title = '中级工' and t1.duty = '管理人员'),--中级工   
  s as (select count(t1.id) as worker3 from jdpj_waf_employee t1 where t1.degree_title = '初级及以下' and t1.duty = '管理人员')--初级及以下   
  select a.lessthan35, b.morethan36, c.morethan46, d.morethan56, 
  e.doctor, f.mastor, g.bachelor, h.grad, i.vocational, j.hight, k.middle, 
  l.senior, m.secondary, n.junior, 
  o.seniortech, p.tech, 
  q.worker1, r.worker2, s.worker3   
  from a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s   

3 楼 jonakang 2008-03-04  

这个是最早用with语句的解决方法，后来觉得语句臃肿想换个间练点的

with
  a as (select t1.duty, count(t1.id) as cnt from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 <= 35 group by t1.duty),--35> x的
  b as (select count(t1.id) as cnt from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 between 36 and 45),--36< x <45的
  c as (select count(t1.id) as cnt from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 between 46 and 55),--46< x <55的
  d as (select count(t1.id) as cnt from jdpj_waf_employee t1 where months_between(sysdate, t1.birthday)/12 >= 56),--56< x的
  e as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '博士'),--博士研究生
  f as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '硕士'),--硕士研究生
  g as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '本科'),--本科
  h as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '专科'),--专科
  i as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '中等职业教育'),--中等职业教育
  j as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '高中'),--高中
  k as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree = '初中及以下'),--初中及以下
  l as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '高级'),--高级
  m as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '中级'),--中级
  n as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '初级'),--初级
  o as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '高级技师'),--高级技师
  p as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '技师'),--技师
  q as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '高级工'),--高级工
  r as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '中级工'),--中级工
  s as (select count(t1.id) as cnt from jdpj_waf_employee t1 where t1.degree_title = '初级及以下')--初级及以下
  select a.cnt, b.cnt, c.cnt, d.cnt, e.cnt, f.cnt, g.cnt, h.cnt, i.cnt, j.cnt, k.cnt, l.cnt, m.cnt, m.cnt, o.cnt, p.cnt, q.cnt, r.cnt, s.cnt
  from a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s
  where a.duty='管理人员'

2 楼 jonakang 2008-03-04  

先前有这么个做法，但是如何把结果摆成横的呢？

--年龄结构
select count(t.id_card_no) as num1, t.duty
from jdpj_waf_employee t
where months_between(sysdate, t.birthday)/12 < 35
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num2, t.duty
from jdpj_waf_employee t
where months_between(sysdate, t.birthday)/12 between 36 and 45
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num3, t.duty
from jdpj_waf_employee t
where months_between(sysdate, t.birthday)/12 between 46 and 55
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num4, t.duty
from jdpj_waf_employee t
where months_between(sysdate, t.birthday)/12 > 56
and t.duty is not null
group by t.duty

union 
--文化结构

select count(t.id_card_no) as num6, t.duty
from jdpj_waf_employee t
where t.degree = '博士'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num7, t.duty
from jdpj_waf_employee t
where t.degree = '硕士'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num8, t.duty
from jdpj_waf_employee t
where t.degree = '本科'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num9, t.duty
from jdpj_waf_employee t
where t.degree = '专科'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num10, t.duty
from jdpj_waf_employee t
where t.degree = '中等职业教育'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num11, t.duty
from jdpj_waf_employee t
where t.degree = '高中'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num12, t.duty
from jdpj_waf_employee t
where t.degree = '初中及以下'
and t.duty is not null
group by t.duty

union

--职称结构
select count(t.id_card_no) as num13, t.duty
from jdpj_waf_employee t
where t.degree_title = '高级'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num14, t.duty
from jdpj_waf_employee t
where t.degree_title = '中级'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num15, t.duty
from jdpj_waf_employee t
where t.degree_title = '初级'
and t.duty is not null
group by t.duty

union

--技术等级结构
select count(t.id_card_no) as num16, t.duty
from jdpj_waf_employee t
where t.title = '高级技师'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num17, t.duty
from jdpj_waf_employee t
where t.title = '技师'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num18, t.duty
from jdpj_waf_employee t
where t.title = '高级工'
and t.duty is not null
group by t.duty

union 

select count(t.id_card_no) as num19, t.duty
from jdpj_waf_employee t
where t.title = '中级工'
and t.duty is not null
group by t.duty

union

select count(t.id_card_no) as num20, t.duty
from jdpj_waf_employee t
where t.title = '初级及以下'
and t.duty is not null
group by t.duty

1 楼 jonakang 2008-03-04  

中间橙色的不用管他