写了如下语句:
select xl as xl,yyhm,sum(yyze) as yyze from yy_ryye 
 where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
 结果是按xl和yyhm分组了,但是想在想取出每组里的最大值和最小值,应该怎么办呢?

解决方案 »

  1.   

    select xl as xl,yyhm,sum(yyze) as yyze,max(yyze) as maxyyze ,min(yyze) as minyyze from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
      

  2.   


    select xl as xl,yyhm,sum(yyze) as yyze,min(yyze) as minvalue,max(yyze) as maxvalue
    from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
      

  3.   

    select max(yyze) as maxyyze,min(yyze) as minyyze  from (select xl as xl,yyhm,sum(yyze) as yyze from yy_ryye 
     where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm) temp
      

  4.   

    select xl as xl,yyhm,MIN(yyze) as MINyyze,MAX(yyzz) AS MAXYYZZ from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
      

  5.   

    select xl,yyhm,max(yyze),min(yyze) from (
    select xl as xl,yyhm,yyze=sum(yyze) as yyze from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
    ) a
    group by xl,yyhm
      

  6.   


    select min(yyze) as minyyze,max(yyze) as maxyyze
    from (
    select xl as xl,yyhm,sum(yyze) as yyze
    from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' 
    group by xl,yyhm 
    ) a
      

  7.   

    非常感谢各位的回答,不过都不是我要的结果
    我现在的结果是这样的:
    xl         yyhm                 yyze                                     
    ---------- -------------------- ---------------------------------------- 
    1          101                  222033.20
    1          102                  231469.20
    1          103                  230595.50
    1          104                  234135.30
    1          105                  171925.40
    1          106                  228460.90
    1          107                  185570.20
    1          108                  236292.10
    1          109                  173551.00
    1          110                  220793.90
    1          111                  233492.20
    1          112                  235181.90
    1          113                  249231.50
    1          114                  168592.40
    1          115                  182825.90
    1          116                  199600.40
    1          117                  226137.30
    1          118                  176750.30
    1          119                  155051.50
    1          120                  169048.40
    1          121                  175803.10
    1          122                  178046.00
    1          123                  121010.90
    1          124                  126104.50
    1          125                  132370.20
    1          126                  135533.30
    3          301                  254249.90
    3          302                  246497.60
    3          303                  258495.60
    3          304                  305017.20
    3          305                  265315.00
    3          306                  268833.80
    3          307                  279154.70
    3          308                  261763.00
    3          309                  233093.10
    3          310                  284402.40
    3          311                  266028.20
    3          312                  264481.70
    3          313                  264044.00
    3          314                  308663.00
    3          315                  272959.80
    3          316                  226189.30
    3          317                  283798.50
    3          318                  209505.20
    3          319                  276265.70
    3          320                  264697.70
    3          321                  242730.70
    3          322                  247843.10
    3          323                  284459.60
    3          324                  294806.80
    3          325                  286332.40
    3          326                  287125.90
    3          327                  253399.60
    3          328                  243762.90
    3          329                  37687.50
    3          330                  32044.00
    3          331                  55632.70
    3          332                  62215.80
    3          333                  28521.50
    3          334                  36187.40
    3          335                  27770.20
    3          336                  31090.20
    4          401                  337153.40
    4          402                  293622.40
    4          403                  331408.50
    4          404                  299781.30
    4          405                  303473.80
    4          406                  308834.80
    4          407                  337082.60
    4          408                  309321.60
    4          409                  307949.40
    4          410                  257903.60
    4          411                  284790.90
    4          412                  334127.40
    4          413                  341916.20
    4          414                  314928.70
    4          415                  314337.20
    4          416                  325985.20
    4          417                  315463.40
    4          418                  331979.90
    4          419                  312224.80
    4          420                  334124.50
    4          421                  358528.30
    4          422                  322983.80
    4          423                  294588.30
    4          424                  299182.20
    4          425                  327436.00
    4          426                  363635.70
    4          427                  359136.20
    4          428                  315256.40
    4          429                  313375.20
    4          430                  318829.80
    4          431                  284101.40
    4          432                  310255.40
    4          433                  275859.30
    4          434                  259523.80
    6          601                  217363.30
    6          602                  232269.90
    6          603                  231547.80
    6          604                  196140.80
    6          605                  219115.80
    6          606                  243060.50
    6          607                  238269.10
    6          608                  236818.70
    6          609                  221592.80
    6          610                  224967.90
    6          611                  233488.80
    6          612                  190343.50
    6          613                  208547.00
    6          614                  166136.40
    6          615                  174860.20
    6          616                  184619.00
    6          617                  143981.40
    6          618                  116290.60
    7          701                  251881.20
    7          702                  286126.60
    7          703                  259690.30
    7          704                  245528.80
    7          705                  242008.10
    7          706                  312459.80
    7          707                  264784.30
    7          708                  278705.00
    7          709                  277200.20
    7          710                  313018.60
    7          711                  248839.90
    7          712                  264972.80
    7          713                  275149.40
    7          714                  276373.30
    7          715                  255521.80
    7          716                  269487.10
    7          717                  264751.90
    7          718                  255585.10
    7          719                  209819.20
    7          720                  302839.50
    7          721                  299936.10
    7          722                  267850.10
    7          723                  289256.10
    7          724                  283111.60
    7          725                  307639.10
    7          726                  309952.90
    7          727                  231239.70
    7          728                  255771.30
    7          729                  52148.90
    7          730                  16605.70(所影响的行数为 144 行)
    我现在想在这里数据中取出最小值
    1          123                  121010.90
    3          335                  27770.20
    4          410                  257903.60
    6          618                  116290.60
    7          730                  16605.70
    我这样写是不是明白了
      

  8.   

    select xl as xl,yyhm,sum(yyze) as yyze,min(yyze) as minvalue,max(yyze) as maxvalue
    from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
      

  9.   


    --@T为10楼的结果数据表
    select a.xl,b.yyhm,a.yyze from 
    (
    select xl,
    yyze=(select min(yyze) from @T where xl=t.xl)
     from @T t  group by xl
    ) a 
    join @T b 
    on a.xl=b.xl and a.yyze=b.yyze
      

  10.   

    是不是这样的结果?
    --@T为10楼的结果数据表
    select a.xl,b.yyhm,a.yyzemin,a.yyzemax from 
    (
    select xl,
    yyzemin=(select min(yyze) from @T where xl=t.xl),
    yyzemax=(select max(yyze) from @T where xl=t.xl)
     from @T t  group by xl
    ) a 
    join @T b 
    on a.xl=b.xl and a.yyzemin=b.yyze 
    /*
    xl          yyhm        yyzemin                                 yyzemax
    ----------- ----------- --------------------------------------- ---------------
    1           123         121010.90                               249231.50
    3           335         27770.20                                308663.00
    4           410         257903.60                               363635.70
    6           618         116290.60                               243060.50
    7           730         16605.70                                313018.60
    */
      

  11.   

    yyhm就没法列了,因为最小值和最大值的yyhm不一样。
    select xl,max(yyze),min(yyze) from (
    select xl as xl,yyhm,sum(yyze) as yyze from yy_ryye 
    where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2 
    ) a
    group by xl