写了如下语句:
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分组了,但是想在想取出每组里的最大值和最小值,应该怎么办呢?
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分组了,但是想在想取出每组里的最大值和最小值,应该怎么办呢?
where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,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
where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm) temp
where rq>='2008-01-01' and rq <='2008-09-25' and xl <>'31' and xl <>'41' group by xl,yyhm order by 1,2
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
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
我现在的结果是这样的:
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
我这样写是不是明白了
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
--@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
--@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
*/
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