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

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

评论

35 楼 tommy402 2008-09-10  

可以这样，把同一个表起不同的别名，和你的列个数一样，然后where中的条件都有一样的，即：
select a.id,b.name,c.birthday from employee a,employee b,employee c
where X and Y and Z;

X、Y、Z是同一个where条件，用不同的别名代替就可以

34 楼 anweixiao 2008-04-11  

这种问题我觉得比较好的做法是先统计出行数据需要的信息，然后分行得到统计数据的结果比较好

33 楼 Ben.Sin 2008-04-10  

可以考虑这样做
select sum(decode(t1.degree_title, '初级', 1, 0) n,
       sum(decode(t1.duty,'管理人员', 1, 0)) o
from t1

或者用case when方式代替decode

32 楼 jewels007 2008-04-09  

    可以考虑用临时表，先把人员按人员类别insert到某张表，当然可以是临时表。表定义就是人员类别和人员代码。然后用人员类别（管理人员等）来循环。当循环到某种类别的人（比如是管理人员）时，再对临时表中符合条件的人员进行统计，再插入到另一张临时表当中。这样人员条件只要写一次，而且比较清楚。我们对多单位多种信息人数的汇总还有金额的汇总就是用这个方法。当然这就不光光是语句了，要用存储过程了。大体思路是这样，具体表设计当然还要看你的具体要求。

31 楼 luxiaoan 2008-04-07  

附上存储过程，仅供参靠

CREATE proc LoadFB(@cDateStr varchar(100) ,@cDepartment varchar(100),@cGoodsName varchar(100),@siKind Char(10))
as
begin

declare @MainSQL varchar(1000)
declare @CASESQl nvarchar(4000)
declare @VerItemName varchar(50)
declare @uppSQl  varchar(1000)
declare @lastItems varchar(500)
declare @itemN int

-- 汇总
set @MainSQL = '   FROM (SELECT cGoodName, SUM(fQuantity) AS ITEM0 ,cDepartment
FROM (SELECT TVerVatCheck.cGoodName, TVerVatItem.fQuantity,
              TVerVatCheck.dVerifyDate, TVerVatItem.cDepartment
        FROM TVerVatCheck WITH (NOLOCK) INNER JOIN
              TVerVatItem WITH (NOLOCK) ON
              TVerVatCheck.iVerVatCheckSn = TVerVatItem.iVerVatCheckSn INNER JOIN
             TVerItem ON TVerVatItem.siVerItemSn = TVerItem.siVerItemSn
        WHERE (TVerItem.siKind = '+@siKind+') AND (TVerItem.siGoodCheck = 1)    '+@cDateStr+'
  '+@cDepartment+'    '+@cGoodsName+'
) A GROUP BY cGoodName,cDepartment ) A   '


--明晰

declare @addSQl   varchar(2000) 
   set @addSQl = '  FROM (SELECT cGoodName, SUM(fQuantity) AS fTotD, cVerItemName,cDepartment
FROM (SELECT TVerVatCheck.cGoodName, TVerVatItem.fQuantity,
              TVerItem.cVerItemName, TVerVatCheck.dVerifyDate,
              TVerVatItem.cDepartment
        FROM TVerVatCheck WITH (NOLOCK) INNER JOIN
              TVerVatItem WITH (NOLOCK) ON
              TVerVatCheck.iVerVatCheckSn = TVerVatItem.iVerVatCheckSn INNER JOIN
              TVerItem ON TVerVatItem.siVerItemSn = TVerItem.siVerItemSn
where  (TVerItem.siKind ='+@siKind+') AND (TVerItem.siGoodCheck = 1)  and  1=1  '+@cDateStr+'   '+@cDepartment+'  ) S
GROUP BY cGoodName, cVerItemName,cDepartment) A  '



declare curItem cursor for
select cVerItemName from TVerItem  where siKind = @siKind and siGoodCheck = 1 ORDER BY siVerItemSn

open curItem
set @itemN = 0
set @CASESQl = ''
set @uppSQl = ''
set @lastItems = 'ITEM0'
fetch next from curItem into @VerItemName
while @@fetch_status = 0
begin
   set @itemN = @itemN +1
   if(@itemN = 1)
   begin
       set @CASESQl  = @CASESQl + ' , CASE WHEN cVerItemName ='+char(39)+ @VerItemName+char(39)+'  THEN fTotD ELSE 0 END AS ITEM'+Cast(@itemN As varchar(3))
       set @uppSQl = @uppSQl +', SUM(ITEM'+Cast(@itemN As varchar(3))+') as ITEM'+Cast(@itemN As varchar(3))
    end
    else
    begin
       set @CASESQl  = @CASESQl + ' ,CASE WHEN cVerItemName ='+ char(39)+@VerItemName+char(39)+'  THEN fTotD ELSE 0 END AS ITEM'+Cast(@itemN As varchar(3))
       set @uppSQl = @uppSQl +', SUM(ITEM'+Cast(@itemN As varchar(3))+') as ITEM'+Cast(@itemN As varchar(3))
    end
    set @lastItems = @lastItems+',ITEM'+Cast(@itemN As varchar(3))
   fetch next from curItem into @VerItemName
end

close curItem
deallocate curItem

--drop table #tmpTable
--print @CASESQl
set @CASESQl = 'SELECT cGoodName  '+@CASESQl+@addSQl

set @CASESQl = 'select cGoodName      '+@uppSQl + '   FROM ( '+ @CASESQl+') B  GROUP By cGoodName '

set @CASESQl  = ' select A.cGoodName, cDepartment, '+@lastItems+@MainSQL+ 'LEFT OUTER JOIN  ( '+@CASESQl+' ) B ON A.cGoodName = B.cGoodName ' 

set @CASESQl = 'select cGoodName,cDepartment,' +@lastItems +' FROM ( '+ @CASESQl+ ') C ORDER BY cGoodName '

--print @CASESQl


exec sp_executesql @CASESQl
end

--set @itemN= 0  exec LoadFB   

--while @itemN < 125
--begin
--  set @itemN = @itemN + 1   
--  print char(@itemN)+':'+cast(@itemN as varchar(3))
--end

GO

30 楼 luxiaoan 2008-04-07  

根据实际的开发实践，当表中的记录小于20万时  用这种方法做是比较有效率的
而且如果是动态的二维表的话 ，这种做法应该是首选了（就要用存储过程动态生成所要的sql语句在执行）

29 楼 daydayup 2008-04-03  

luxiaoan 的思路是对的，sql没仔细看，
To angeltping,
用这种吐血的SQL，应该比多连几次数据库要快得多吧

28 楼 applefeng_52 2008-03-26  

一条sql语句就可以，用split来分隔语句

27 楼 angeltping 2008-03-25  

这种吐血sql不要用。
可以用尽量简单的多个sql返回数据。在程序里组装数据机构。
这种性能的大部分开销是执行sql。在程序中花费的时间不会太多

26 楼 luxiaoan 2008-03-24  

select * from ( select count(*),duty from employee with (nolock)group by duty ) A left outer join (
select duty,sum(birthday1) as birthday1 ,sum(birthday1) as birthday1 ,sum(birthday1) as birthday1 from ( select case when getdate()-birthday)
<=35 then 1 else 0 end as birthday1,case when getdate()-birthday >35 and getdate()-birthday <=55 then 1 else 0 end as birthday2 ,case when
getdate()-birthday) > 55 then 1 else 0 end as birthday3,duty from  employee ) tbirthday group by duty ) B  on a.duty = b.duty
left outer join (select sum(ibs) as ibs .... ,duty from (  select case when degree = '博士' when 1 else 0 end as ibs.... from  employee )
tuty group by )  c duty on a.duty = c.duty  ..其他2个 title,degree_title

25 楼 luxiaoan 2008-03-24  

。

24 楼 rabbitbug 2008-03-24  

找找中间产品
或是干脆一项项算好再填上去
当然这样维护性差了

23 楼 meikefu 2008-03-19  

javaTo 写道

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

oracle查询是没有锁的,除非你用for update

22 楼 demo_yang 2008-03-18  

使用decode
count(null)是不计数的，即count(decode(...))

21 楼 gaoran2008 2008-03-15  

jonakang 写道

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

----------------------------------------------------------------------

这样的报表我做过了，呵呵。当时也是也楼主一样的去解决。可是在性能并不是很理想的。
后来想到一个办法：
    建立一个快照，你把你的每个数值的点的SQL语句，都写到快照里面去。每天晚上或者凌晨更新，最后的报表查询基于这张快照来查询。这个报表不是很复杂，应该没什么问题。
    如果要时时的数据，可以通过提出出业务的数据写function,然后你要去建立视图，不过SQL语句要优化。

20 楼 lumi 2008-03-14  

这是典型的中国式报表(复杂表头,不定列,数据跨行等)

个人认为sql语言的窗口函数+行列转换是个比较好的方案,不过写的sql比较多,但是效率绝对不差!

我不建议把sql写到程序里,整个存储过程吧,嘿嘿!

楼主如果乐意讨论,把表结构和数据贴出来大家讨论!

19 楼 jonakang 2008-03-14  

javaTo 写道

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

现在我采用的是这个方法，目前暂时没有考虑线程问题。

18 楼 jonakang 2008-03-14  

首先，谢谢大家的关注。
这里我大概的说下我遇到的问题：在一家企业内，我们对人力资源进行分类（管理人员，专业技术人员，生产技能人员，其他人员），这个分类被存放在一个基础数据表中（命名为BasicDataMaster=存放该分类的代码，BasicDataDetail=存放该代码对应的明细，如上述中的人员类别），在职员表中有个Field与之对应（并非关联，而是在做insert employee的时候把相应的类型存放在该字段中，只是个value）。与此同时，还有其他的企业内基础数据对employee进行描述，比如职称，学历，年龄（birthday），技术等级等等。报表要求如图所示。

17 楼 pig345 2008-03-13  

窗口函数，似乎是解决此类问题的东西。（听说而已，给您提个醒）

16 楼 lumi 2008-03-10  

楼主的方案太复杂了!

19个表连接,数据库会崩溃的!

用行转列,应该只要4个表的连接!