高分请教个SQL语句，麻烦高手看看！谢谢！

Question

表结构:
test1(tradeid integer,deminid integer,ammountid intger,ammountValue integer)
假如现在有这样的两行数据:
tradeid deminid ammountid ammountValue
1       1       1         2
1       1       3         4
想查出这样的结果:
tradeid deminid ammountid ammountValue ammountid ammountValue
1       1        1        2            3         4即tradeid,deminid相同的记录，把ammountid,ammountValue合并的一条记录里查出来，请高手帮忙看看！谢谢！在线等！

Answer 1

TRY IT ..SQL> SELECT * FROM TEST1;  TRADEID            DEMINID         AMMOUNTID      AMMOUNTVALUE
--------------    -------------    --------------   -------------
1                      1                1                2
1                      1                3                4
2                      2                5                6
2                      2                7                8SQL> 
SQL> SELECT *
  2    FROM (SELECT TRADEID,
  3                 DEMINID,
  4                 AMMOUNTID,
  5                 AMMOUNTVALUE,
  6                 LEAD(AMMOUNTID) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTID",
  7                 LEAD(AMMOUNTVALUE) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTVALUE"
  8            FROM TEST1)
  9   WHERE NVL(NEW_AMMOUNTID + NEW_AMMOUNTVALUE, -1) >= 0;TRADEID           DEMINID       AMMOUNTID         AMMOUNTVALUE   NEW_AMMOUNTID   NEW_AMMOUNTVALUE
------------- --------------  -----------------   ------------------ ------------- ----------------
 1                    1             1                  2             3                4
 2                    2             5                  6             7                8SQL> 

Answer 2

DROP TABLE test1;
CREATE TABLE test1(tradeid integer,deminid integer,ammountid INTEGER,ammountValue integer) ;
INSERT INTO TEST1 VALUES (1, 1, 1, 2);
INSERT INTO TEST1 VALUES (1, 1, 3, 4);
INSERT INTO TEST1 VALUES (1, 1, 5, 6);
--10g的方法
SELECT TRADEID,
       DEMINID,
       WMSYS.WM_CONCAT(AMMOUNTID || '/' || AMMOUNTVALUE) X
  FROM TEST1
 GROUP BY TRADEID, DEMINID;
输出:
TRADEID DEMINID    X
1         1     1/2,3/4,5/6

Answer 3

SQL> SELECT * FROM TEST1;  TRADEID            DEMINID         AMMOUNTID      AMMOUNTVALUE
--------------    -------------    --------------   -------------
1                      1                1                2
1                      1                3                4
2                      2                5                6
2                      2                7                8SQL> 
SQL> SELECT *
  2    FROM (SELECT TRADEID,
  3                 DEMINID,
  4                 AMMOUNTID,
  5                 AMMOUNTVALUE,
  6                 LEAD(AMMOUNTID) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTID",
  7                 LEAD(AMMOUNTVALUE) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTVALUE"
  8            FROM TEST1)
  9   WHERE NVL(NEW_AMMOUNTID + NEW_AMMOUNTVALUE, -1) >= 0;TRADEID           DEMINID       AMMOUNTID         AMMOUNTVALUE   NEW_AMMOUNTID   NEW_AMMOUNTVALUE
------------- --------------  -----------------   ------------------ ------------- ----------------
 1                    1             1                  2             3                4
 2                    2             5                  6             7                8=====================
首先感谢这位兄弟，但是这个SQL还是有一定的问题，当插入
insert into TEST1 values(1,1,2,4);
insert into TEST1 values(1,1,3,4);
insert into TEST1 values(1,2,2,6);
insert into TEST1 values(1,2,2,5);
insert into TEST1 values(1,3,4,4);这条数据是查不出来的！即当该数据单独成一组的时候，就被过滤了！

Answer 4

DROP TABLE test1;
CREATE TABLE test1(tradeid integer,deminid integer,ammountid INTEGER,ammountValue integer) ;
INSERT INTO TEST1 VALUES (1, 1, 1, 2);
INSERT INTO TEST1 VALUES (1, 1, 3, 4);
INSERT INTO TEST1 VALUES (1, 1, 5, 6);
--10g的方法
SELECT TRADEID,
       DEMINID,
       WMSYS.WM_CONCAT(AMMOUNTID || '/' || AMMOUNTVALUE) X
  FROM TEST1
 GROUP BY TRADEID, DEMINID;
输出:
TRADEID    DEMINID       X
1            1        1/2,3/4,5/6
==================
感谢3楼提供的这个方法，但是我们这边用的是9i数据库，这个函数用不起来！~~

Answer 5

可以通过COUNTS标识
SQL> SELECT * FROM TEST1;TRADEID                              DEMINID                               AMMOUNTID                            AMMOUNTVALUE
----------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1                                       3                                       4                                       4
1                                       1                                       1                                       2
1                                       1                                       3                                       4
2                                       2                                       5                                       6
2                                       2                                       7                                       8SQL> SELECT TRADEID,
  2         DEMINID,
  3         AMMOUNTID,
  4         AMMOUNTVALUE,
  5         NEW_AMMOUNTID,
  6         NEW_AMMOUNTVALUE
  7    FROM (SELECT TRADEID,
  8                 DEMINID,
  9                 AMMOUNTID,
 10                 AMMOUNTVALUE,
 11                 LEAD(AMMOUNTID) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTID",
 12                 LEAD(AMMOUNTVALUE) OVER(PARTITION BY TRADEID, DEMINID ORDER BY TRADEID, DEMINID) "NEW_AMMOUNTVALUE",
 13                 COUNT(1) OVER(PARTITION BY TRADEID, DEMINID) "COUNTS"
 14            FROM TEST1)
 15   WHERE NVL(NEW_AMMOUNTID + NEW_AMMOUNTVALUE, -1) >= 0
 16      OR COUNTS = 1;TRADEID             DEMINID             AMMOUNTID            AMMOUNTVALUE        NEW_AMMOUNTID        NEW_AMMOUNTVALUE
---------------- ---------------- ---------------------- -------------------- -------------------- ----------------------
1                      1                    1                    2                    3                   4
1                      3                    4                    4               
2                      2                    5                    6                    7                   8SQL> 

Answer 6

使用如下方法（8i/9i/10g）：SELECT TRADEID, DEMINID, MAX(X) X
  FROM (SELECT TRADEID, DEMINID, LTRIM(SYS_CONNECT_BY_PATH(ZZ, ','), ',') X
          FROM (SELECT TRADEID,
                       DEMINID,
                       AMMOUNTID || '/' || AMMOUNTVALUE ZZ,
                       ROW_NUMBER() OVER(PARTITION BY TRADEID, DEMINID ORDER BY AMMOUNTID) AS CURR,
                       ROW_NUMBER() OVER(PARTITION BY TRADEID, DEMINID ORDER BY AMMOUNTID) - 1 AS PREV
                  FROM TEST1)
         START WITH CURR = 1
        CONNECT BY PREV = PRIOR CURR
               AND TRADEID = PRIOR TRADEID)
 GROUP BY TRADEID, DEMINID
 ORDER BY 1;

Answer 7

使用如下方法（8i/9i/10g）： SELECT TRADEID,
       DEMINID,
       WMSYS.WM_CONCAT(AMMOUNTID || '/' || AMMOUNTVALUE) X
  FROM TEST1
 GROUP BY TRADEID, DEMINID;
SELECT TRADEID, DEMINID, MAX(X) X
  FROM (SELECT TRADEID, DEMINID, LTRIM(SYS_CONNECT_BY_PATH(ZZ, ','), ',') X
          FROM (SELECT TRADEID,
                       DEMINID,
                       AMMOUNTID || '/' || AMMOUNTVALUE ZZ,
                       ROW_NUMBER() OVER(PARTITION BY TRADEID, DEMINID ORDER BY AMMOUNTID) AS CURR,
                       ROW_NUMBER() OVER(PARTITION BY TRADEID, DEMINID ORDER BY AMMOUNTID) - 1 AS PREV
                  FROM TEST1)
         START WITH CURR = 1
        CONNECT BY PREV = PRIOR CURR
               AND TRADEID||DEMINID = PRIOR TRADEID||DEMINID)
 GROUP BY TRADEID, DEMINID
 ORDER BY 1;
输出：
TRADEID DEMINID X
1 1 1/2,3/4,5/6