Oracle 分组小计 , 每个分组内排序问题 ..~~~~~~

SELECT A.ZTADAT_0,A.ZTATYP_0,A.ZTAORIO_0,A.ZTAORIN_0,B.ZTANUM_0,B.ZEONUM_0,B.SAMT FROM ZEXPTRA A
RIGHT JOIN 
(SELECT ZEONUM_0,DECODE(GROUPING_ID(ZTANUM_0),1,'小计',ZTANUM_0) ZTANUM_0,SUM(ZTAAMT_0) AS SAMT FROM ZEXPTRA 
WHERE BPCNUM_0='A130022' AND ZTANUM_0 NOT IN (SELECT ZTANUM_0 FROM ZEXPTRA WHERE ZTATYP_0='2' AND ZTAORIO_0='6') AND ZEONUM_0 IN ('B01','B03') AND FCY_0='C01' AND ZTADAT_0 BETWEEN TO_DATE('2011-01-01','YYYY-MM-DD') AND TO_DATE('2013-04-17','YYYY-MM-DD')
GROUP BY ZEONUM_0,ROLLUP(ZTANUM_0)) B ON A.ZTANUM_0=B.ZTANUM_0结果如下
    ZTADAT_0 ZTATYP_0 ZTAORIO_0 ZTAORIN_0 ZTANUM_0 ZEONUM_0 SAMT
1 2012/9/8 1 4 SDHC0112090018 11216-08/09/2012-11:42:02-12090000000036 B01 5304
2 2012/9/8 1 4 SDHC0112090018 11216-08/09/2012-11:42:02-12090000000047 B01 270
3 2012/9/8 1 4 SDHC0112090019 11216-08/09/2012-11:44:42-12090000000058 B01 1620
4 2012/8/15 1 4 SDHC0112080031 11216-15/08/2012-11:25:27-12080000000474 B01 528
5 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000491 B01 1550.4
6 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000502 B01 979.2
7 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000513 B01 5475
8 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000524 B01 2280
9 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000535 B01 1387.5
10 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000546 B01 1350
11 2012/10/17 1 4 SDHC0112100023 11216-17/10/2012-13:59:31-12100000000215 B01 685.44
12 2012/10/17 1 4 SDHC0112100023 11216-17/10/2012-13:59:31-12100000000226 B01 864.96
13 2012/10/17 1 4 SDHC0112100022 11216-17/10/2012-19:35:08-12100000000338 B01 2775
14 2012/10/17 1 4 SDHC0112100022 11216-17/10/2012-19:35:08-12100000000349 B01 5850
15 2012/10/17 1 4 SDHC0112100022 11216-17/10/2012-19:35:08-12100000000360 B01 1620
16 2012/10/17 1 4 SDHC0112100022 11216-17/10/2012-19:35:08-12100000000371 B01 1428
17 2012/8/23 1 4 SDHC0112080043 11216-23/08/2012-10:10:39-12080000000599 B01 7020
18 2012/8/28 1 4 SDHC0112080056 11216-28/08/2012-09:53:10-12080000000611 B01 2643.84
19 2012/8/9 1 5 ZPJC01120800001 11221-10/08/2012-15:18:58-12080000000296 B01 23724.2
20 2012/8/9 1 5 ZPJC01120800002 11221-13/08/2012-08:59:36-12080000000297 B01 40000
21 2012/9/17 2 5 ZPJC01120900004 11221-17/09/2012-14:47:19-12090000000121 B01 -15263.6
22 2013/1/7 1 6 ZRNC01130000067 BILLY-07/01/2013-14:12:43-13010000000192 B01 64011.94
23 2012/10/16 2 7 ZAPC01121000004 FELIX-16/10/2012-13:59:31-12100000000039 B01 -37800
24 2012/10/16 1 7 ZAPC01121000004 FELIX-16/10/2012-14:04:07-12100000000043 B01 37800
25 2012/10/16 2 7 ZAPC01121000005 FELIX-16/10/2012-22:46:23-12100000000046 B01 -28080
26 小计 B01 128023.88
27 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000376 B03 5427
28 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000377 B03 16000
29 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000378 B03 16000
30 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000379 B03 6960
31 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000380 B03 45
32 2012/8/14 2 3 SOHC0112080022 11221-14/08/2012-16:11:09-12080000000473 B03 -44400
33 2012/10/17 1 9 ZBRC01121000004 11232-17/10/2012-10:00:26-12100000000047 B03 28080
34 2012/10/17 2 3 SOHC0112100025 11232-17/10/2012-10:32:39-12100000000048 B03 -28080
35 2013/1/7 1 6 ZRNC01130000026 BILLY-07/01/2013-14:07:17-13010000000120 B03 32
36 小计 B03 64
现在想按 ZTADAT_0日期 在每个分组内排序 ,不影响 分组和小计 ..求指点

解决方案 »

  1.   


    我在子查询之前 就弄了一个按日期排序的数据集, 但是好像用了 rollup 后 它就根据rollup(ZTANUM_0)里面的字段排序了 
      

  2.   

        ZTADAT_0 ZTATYP_0 ZTAORIO_0 ZTAORIN_0 ZTANUM_0 ZEONUM_0 ZTAAMT_0
    1 2012/8/9 1 5 ZPJC01120800002 11221-13/08/2012-08:59:36-12080000000297 B01 40000
    2 2012/8/9 1 5 ZPJC01120800001 11221-10/08/2012-15:18:58-12080000000296 B01 23724.2
    3 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000376 B03 5427
    4 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000377 B03 16000
    5 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000380 B03 45
    6 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000378 B03 16000
    7 2012/8/14 1 9 ZBRC01120800005 11221-14/08/2012-10:09:37-12080000000379 B03 6960
    8 2012/8/14 2 3 SOHC0112080022 11221-14/08/2012-16:11:09-12080000000473 B03 -44400
    9 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000491 B01 1550.4
    10 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000502 B01 979.2
    11 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:34-12080000000513 B01 5475
    12 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000524 B01 2280
    13 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000535 B01 1387.5
    14 2012/8/15 1 4 SDHC0112080031 11216-15/08/2012-11:25:27-12080000000474 B01 528
    15 2012/8/15 1 4 SDHC0112080033 11216-15/08/2012-14:12:35-12080000000546 B01 1350SELECT ZTADAT_0,ZTATYP_0,ZTAORIO_0,ZTAORIN_0,ZTANUM_0,ZEONUM_0,ZTAAMT_0 FROM ZEXPTRA 
    WHERE BPCNUM_0='A130022' AND ZTANUM_0 NOT IN (SELECT ZTANUM_0 FROM ZEXPTRA WHERE ZTATYP_0='2' AND ZTAORIO_0='6') AND ZEONUM_0 IN ('B01','B03') AND FCY_0='C01' AND ZTADAT_0 BETWEEN TO_DATE('2011-01-01','YYYY-MM-DD') AND TO_DATE('2013-04-17','YYYY-MM-DD')
    ORDER BY ZTADAT_0  基于这个查询出的数据集
      

  3.   


    测试数据 .
    CREATE GLOBAL TEMPORARY TABLE TB(ZTADAT DATE,ZTATYP INT,ZTAORIO INT,ZTAORIN VARCHAR(15),ZTANUM VARCHAR(40),ZEONUM VARCHAR(5),ZTAAMT DECIMAL)
    INSERT INTO TB(ZTADAT,ZTATYP,ZTAORIO,ZTAORIN,ZTANUM,ZEONUM,ZTAAMT)VALUES
    (DATE'2012-08-9',1,5,'ZPJC01120800002','11221-13/08/2012-08:59:36-12080000000297','B01',40000)
    (DATE'2012-08-14',1,5,'ZPJC01120800005','11221-14/08/2012-08:59:36-12080000000376','B01',50000) 
    (DATE'2012-08-16',1,5,'ZPJC01120800006','11221-16/08/2012-08:59:36-12080000000380','B03',60000) 
    (DATE'2012-08-17',1,9,'ZPJC01120800007','11221-17/08/2012-08:59:36-12080000000387','B03',80000) 
      

  4.   


    你这个测试数据的脚本 明显不是oracle 语法