如何在oracle中获取组查询的最小值和最大值> [英] How to get min and max of group query in oracle>
问题描述
我有这个查询，它将每个div_f，PROC_NO，SERIAL_NO的CT相加。
我想得到每个proc_no和div_f的最小值和最大值。
例如
通过div_f从Table1组中选择sum（ct），div_f，PROC_NO，SERIAL_NO， PROC_NO，SERIAL_NO;
此回报：
I have this query that sum the CT of each div_f,PROC_NO,SERIAL_NO.
I want to get the min and max of each proc_no and div_f.
for example
select sum(ct),div_f,PROC_NO,SERIAL_NO from Table1 group by div_f,PROC_NO,SERIAL_NO;
this return:
sum(ct) div_f proc_no serial_no
559 1 1 1121
586 1 1 1122
568 1 1 1123
322 1 1 1124
223 1 1 16523
1180 1 1 16534
1375 1 1 16535
875 1 1 16536
202 1 1 16559
249 1 1 16560
139 1 1 16564
135 1 1 16565
134 1 1 16566
106 1 1 16567
125 1 1 16568
1297 1 1 17723
1291 1 1 17724
1248 1 1 17725
1199 1 1 17726
1080 1 1 17727
1233 1 1 17728
1281 1 1 17729
1150 1 1 17730
1308 1 1 17731
1110 1 1 17732
1434 1 1 17733
1176 1 1 17734
1996 1 1 17750
2003 1 1 17751
1895 1 1 17752
2170 1 1 17753
2252 1 1 17754
1972 1 1 17755
2087 1 1 17756
1700 1 1 17757
1777 1 1 17758
2027 1 1 17759
2419 1 1 17760
1928 1 1 17761
1850 1 1 17762
1296 1 1 17905
1188 1 1 17906
1220 1 1 17907
1048 1 1 17908
1319 1 1 17909
1330 1 1 17910
1366 1 1 17911
1436 1 1 17912
209 1 1 2424
182 1 1 2425
247 1 1 2426
184 1 1 2427
201 1 1 2428
178 1 1 2429
267 1 1 2430
225 1 1 2431
206 1 1 2432
167 1 1 2433
194 1 1 2434
256 1 1 2435
296 1 1 2437
160 1 1 2438
208 1 1 2439
168 1 1 2440
176 1 1 2441
196 1 1 2442
191 1 1 2443
187 1 1 2444
254 1 1 2445
197 1 1 2446
173 1 1 2447
282 1 1 2448
176 1 1 2449
324 1 1 2450
195 1 1 2451
269 1 1 2452
211 1 1 2453
206 1 1 2454
192 1 1 2455
235 1 1 2456
212 1 1 2457
215 1 1 2458
243 1 1 2459
279 1 1 2460
202 1 1 2461
101 1 1 25
516 1 1 2945
762 1 1 2966
371 1 1 2967
623 1 1 2968
716 1 1 2969
599 1 1 2970
694 1 1 2971
470 1 1 2972
539 1 1 2973
2928 1 1 3187
2545 1 1 3188
2353 1 1 3189
2609 1 1 3190
2367 1 1 3191
2532 1 1 3192
1199 1 1 3330
1199 1 1 3331
1275 1 1 3332
624 1 1 3662
957 1 1 3663
852 1 1 3664
506 1 1 3665
849 1 1 3666
953 1 1 3667
996 1 1 3668
941 1 1 3669
281 1 1 3670
968 1 1 3671
976 1 1 3672
1019 1 1 3673
946 1 1 3674
355 1 1 3675
3026 1 1 3975
2724 1 1 3976
2534 1 1 3977
2376 1 1 3978
1748 1 1 3979
1082 1 1 7631
1090 1 1 7632
1139 1 1 7633
1045 1 1 7634
1351 1 1 7635
1313 1 1 7636
1078 1 1 7637
1373 1 1 7638
1296 1 1 7639
2239 1 1 97
3395 1 1 98
1 4 1 17718
154 1 10 1038
301 1 10 1226
267 1 10 1227
271 1 10 1228
229 1 10 1229
272 1 10 1230
220 1 10 1231
228 1 10 1232
198 1 10 1233
231 1 10 1234
127 1 10 1391
126 1 10 1431
125 1 10 1433
139 1 10 1440
146 1 10 1509
139 1 10 1514
150 1 10 1518
228 1 10 1556
2537 1 10 17751
2512 1 10 17753
1274 1 10 17904
1256 1 10 17905
1408 1 10 17906
934 1 10 17907
1210 1 10 17908
884 1 10 17909
128 1 10 2327
117 1 10 2373
78 1 10 2391
99 1 10 2400
227 1 10 2423
216 1 10 2424
205 1 10 2425
187 1 10 2426
233 1 10 2427
203 1 10 2428
214 1 10 2429
228 1 10 2430
209 1 10 2431
280 1 10 2432
213 1 10 2433
207 1 10 2434
191 1 10 2435
228 1 10 2436
234 1 10 2437
221 1 10 2438
220 1 10 2439
217 1 10 2440
215 1 10 2441
249 1 10 2442
224 1 10 2443
247 1 10 2444
207 1 10 2445
218 1 10 2446
216 1 10 2447
214 1 10 2448
197 1 10 2449
257 1 10 2450
225 1 10 2451
238 1 10 2452
226 1 10 2453
238 1 10 2454
250 1 10 2455
224 1 10 2456
218 1 10 2457
239 1 10 2458
269 1 10 2459
260 1 10 2460
233 1 10 2461
630 1 10 3329
659 1 10 3330
692 1 10 3331
523 1 10 3973
91 1 10 4189
118 1 10 4198
116 1 10 4200
133 1 10 4201
106 1 10 4202
100 1 10 4203
574 1 10 7628
609 1 10 7629
713 1 10 7630
706 1 10 7631
688 1 10 7632
671 1 10 7633
645 1 10 7634
651 1 10 7635
I want to show only
min max div_f proc_no
101 3395 1 1
78 2537 1 10
1 1 4 1
is this possible in query only ?
or another way?
What I have tried:
I have tried queries and loop it but it takes time to get what I want . and its too slow if its on web. so I’m trying if its possible to get it in one query only. Thanks
I want to show only
min max div_f proc_no
101 3395 1 1
78 2537 1 10
1 1 4 1
is this possible in query only?
or another way?
What I have tried:
I have tried queries and loop it but it takes time to get what I want . and its too slow if its on web. so I'm trying if its possible to get it in one query only. Thanks
推荐答案
Try:
Try:
SELECT MIN(ct),MAX(ct), div_f,PROC_NO,SERIAL_NO FROM Table1 GROUP BY div_f,PROC_NO,SERIAL_NO; I already try that before. but not working .
but I already solved it . thanks :)
I used :
SELECT DIV_F AS Division,PROC_NO AS Process_Number, MIN(sumCT) as min_CT, MAX(sumCT) as max_CT , round(avg(sumCT),2) as AVERAGE,count(sumCT) as trial_QTY from (select sum(ct) as sumCT,div_f,PROC_NO,SIRIAL_NO from TL_HISTORY_CHECKSHEET where To_Char(To_Date(INPUT_DATE,’mm-dd-yyyy hh:mi:ss am’),’mm/yyyy’) = ’07/2016’ group by div_f,PROC_NO,SIRIAL_NO) group by div_f,PROC_NO;
I already try that before. but not working .
but I already solved it . thanks :)
I used :
SELECT DIV_F AS Division,PROC_NO AS Process_Number, MIN(sumCT) as min_CT, MAX(sumCT) as max_CT , round(avg(sumCT),2) as AVERAGE,count(sumCT) as trial_QTY from (select sum(ct) as sumCT,div_f,PROC_NO,SIRIAL_NO from TL_HISTORY_CHECKSHEET where To_Char(To_Date(INPUT_DATE,'mm-dd-yyyy hh:mi:ss am'),'mm/yyyy') = '07/2016' group by div_f,PROC_NO,SIRIAL_NO) group by div_f,PROC_NO;
这篇关于如何在oracle中获取组查询的最小值和最大值>的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
