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

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

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

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

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

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

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

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

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

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

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

附上存储过程，仅供参靠

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