又一有难度的select语句

Question

要求取出表中以icode分组，anndate最小时间与最大时间的unitval之差，并除以icode的记录数-1，
如icode为1的结果是:(1.0166-1.0038)/(3-1)
  icode为2的结果是:(21.0900-23.5200)/(4-1)
 
icode  unitval   anndate
---------------------------
1      1.0038    2008-04-30
1      1.0074    2008-05-31
1      1.0166    2008-08-31
2      23.5200   2008-01-31
2      24.8400   2008-02-29
2      23.0600   2008-03-31 
2      21.0900   2008-06-30 

Answer 1

select t1.icode , (t1.max_unitval - t2.min_unitval) / (t3.cnt - 1) from
(select icode , max(unitval) max_unitval from tb group by icode) t1,
(select icode , min(unitval) min_unitval from tb group by icode) t2,
(select icode , count(*) cnt from tb group by icode) t3
where t1.icode = t2.icode and t1.icode = t3.icode

Answer 2

DECLARE @TB TABLE(icode INT,  unitval DECIMAL(10,4),  anndate SMALLDATETIME)
INSERT @TB
SELECT 1,  1.0038,  '2008-04-30' UNION ALL 
SELECT 1,  1.0074,  '2008-05-31' UNION ALL 
SELECT 1,  1.0166,  '2008-08-31' UNION ALL 
SELECT 2,  23.5200,  '2008-01-31' UNION ALL 
SELECT 2,  24.8400,  '2008-02-29' UNION ALL 
SELECT 2,  23.0600,  '2008-03-31' UNION ALL 
SELECT 2,  21.0900,  '2008-06-30'SELECT icode,(MAXVAL-MINVAL)/(C-1) AS VAL
FROM (
SELECT icode,MINVAL=(SELECT TOP 1 unitval FROM @TB WHERE icode=A.icode ORDER BY  anndate),
      MAXVAL=(SELECT TOP 1 unitval FROM @TB WHERE icode=A.icode ORDER BY  anndate DESC),
                         COUNT(*) AS C
FROM @TB AS A
GROUP BY icode
) A
/*
icode       VAL                      
----------- ------------------------ 
1           .006400000000000
2           -.810000000000000
*/

Answer 3

declare @t table(icode int,unitval numeric(8,4),anndate datetime)
insert into @t values(1,1.0038 ,'2008-04-30') 
insert into @t values(1,1.0074 ,'2008-05-31') 
insert into @t values(1,1.0166 ,'2008-08-31') 
insert into @t values(2,23.5200,'2008-01-31') 
insert into @t values(2,24.8400,'2008-02-29') 
insert into @t values(2,23.0600,'2008-03-31') 
insert into @t values(2,21.0900,'2008-06-30') 
select
    t.icode,
    ( (select top 1 unitval from @t where icode=t.icode order by anndate desc)
     -(select top 1 unitval from @t where icode=t.icode order by anndate))/
      (select count(*)-1 from @t where icode=t.icode) as value
from
    @t t
group by
    t.icode
    

Answer 4

--或者select icode , (max_unitval - min_unitval) / (cnt - 1) from
(select icode , max(unitval) max_unitval , min(unitval) min_unitval , count(*) cnt from tb group by icode) t

Answer 5

select icode , (max(unitval)- min(unitval)) / case when (count(*) - 1)< 1 then 1 else count(*) - 1 end  as kkkk
from tb 
group by icode

Answer 6

DECLARE @TB TABLE(icode INT,  unitval DECIMAL(10,4),  anndate SMALLDATETIME)
INSERT @TB
SELECT 1,  1.0038,  '2008-04-30' UNION ALL 
SELECT 1,  1.0074,  '2008-05-31' UNION ALL 
SELECT 1,  1.0166,  '2008-08-31' UNION ALL 
SELECT 2,  23.5200,  '2008-01-31' UNION ALL 
SELECT 2,  24.8400,  '2008-02-29' UNION ALL 
SELECT 2,  23.0600,  '2008-03-31' UNION ALL 
SELECT 2,  21.0900,  '2008-06-30'
select icode, value=(maxu-minu)/(cvalue-1) from
(
select icode,
maxu=(select unitval from @TB where anndate=a.maxdate and icode=a.icode),
minu=(select unitval from @TB where anndate=a.mindate and icode=a.icode),
cvalue=(select count(icode) from @TB where icode=a.icode) 
from
(select icode,maxdate=max(anndate),mindate=min(anndate) from @TB group by icode) a
) bicode       value                    
----------- ------------------------ 
1           .006400000000000
2           -.810000000000000