如何从一个表中随机选出10个不一样的数据

Question

如何从一个表中随机选出10个不一样的数据
NUMDPID NUMSINGERID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 1
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
3 10
4 1
4 2
4 3
4 4
4 5
4 6
4 7
4 8
4 9
4 10
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
5 10
6 1
6 2
6 3
6 4
6 5
6 6
6 7
6 8
6 9
6 10
7 1
7 2
7 3
7 4
7 5
7 6
7 7
7 8
7 9
7 10
8 1
8 2
8 3
8 4
8 5
8 6
8 7
8 8
8 9
8 10
9 1
9 2
9 3
9 4
9 5
9 6
9 7
9 8
9 9
9 10
10 1
10 2
10 3
10 4
10 5
10 6
10 7
10 8
10 9
10 10
针对每种 NUMDPID 随机选一个 NUMSINGERID， 结果如下：（例子）NUMDPID NUMSINGERID
1 4
2 2
3 9
4 10
5 3
6 8
7 6
8 5
9 9
10 1这个语句怎么写来着， 我无法一下子全部选出来，只能一次选一个，这样需要执行10个SQL语句，很麻烦，要一次性全选出来怎么弄？？  

Answer 1

SELECT a.numdpid, a.numsingerid
  FROM a,
       (SELECT     ROWNUM numdpid,
                   ROUND (DBMS_RANDOM.VALUE (1, 10)) numsingerid
              FROM DUAL
        CONNECT BY ROWNUM <= 10) b
 WHERE a.numdpid = b.numdpid AND a.numsingerid = b.numsingerid

Answer 2

select numdpid,numsingerid from a where rownum<=10;

Answer 3

SQL> select * from t1;                                NUMDPID                             NUMSINGERID
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      1                                       2
                                      1                                       3
                                      1                                       4
                                      2                                       1
                                      2                                       2
                                      2                                       3
                                      2                                       4
                                      3                                       1
                                      3                                       2
                                      3                                       3
                                      3                                       4
                                      4                                       1
                                      4                                       2
                                      4                                       3
                                      4                                       416 rows selectedSQL> 
SQL> select distinct * from (
  2    select NUMDPID,FIRST_VALUE(NUMSINGERID)over(partition by NUMDPID order by dbms_random.value) from t1
  3  );                                NUMDPID FIRST_VALUE(NUMSINGERID)OVER(P
--------------------------------------- ------------------------------
                                      1                              1
                                      2                              2
                                      3                              2
                                      4                              3SQL> 
SQL> select distinct * from (
  2    select NUMDPID,FIRST_VALUE(NUMSINGERID)over(partition by NUMDPID order by dbms_random.value) from t1
  3  );                                NUMDPID FIRST_VALUE(NUMSINGERID)OVER(P
--------------------------------------- ------------------------------
                                      1                              4
                                      2                              3
                                      3                              4
                                      4                              2SQL> 
SQL> select distinct * from (
  2    select NUMDPID,FIRST_VALUE(NUMSINGERID)over(partition by NUMDPID order by dbms_random.value) from t1
  3  );                                NUMDPID FIRST_VALUE(NUMSINGERID)OVER(P
--------------------------------------- ------------------------------
                                      1                              3
                                      2                              1
                                      3                              4
                                      4                              2SQL> 

Answer 4

-- TRY IT ..
SQL> SELECT RN1, RN2
  2    FROM (SELECT RN1,
  3                 RN2,
  4                 ROW_NUMBER() OVER(PARTITION BY RN1 ORDER BY DBMS_RANDOM.VALUE) RN
  5            FROM (SELECT ROWNUM RN1 FROM DUAL CONNECT BY ROWNUM < 10) R1,
  6                 (SELECT ROWNUM RN2 FROM DUAL CONNECT BY ROWNUM < 10) R2)
  7   WHERE RN = 1;       RN1        RN2
---------- ----------
         1          3
         2          8
         3          4
         4          5
         5          2
         6          8
         7          3
         8          3
         9         10
        10          710 rows selectedSQL> SELECT RN1, RN2
  2    FROM (SELECT RN1,
  3                 RN2,
  4                 ROW_NUMBER() OVER(PARTITION BY RN1 ORDER BY DBMS_RANDOM.VALUE) RN
  5            FROM (SELECT ROWNUM RN1 FROM DUAL CONNECT BY ROWNUM < 10) R1,
  6                 (SELECT ROWNUM RN2 FROM DUAL CONNECT BY ROWNUM < 10) R2)
  7   WHERE RN = 1;       RN1        RN2
---------- ----------
         1          4
         2          5
         3          8
         4          3
         5          1
         6          3
         7          4
         8          7
         9          7
        10          910 rows selected

Answer 5

用wmsys.wm_concat函数就是了。是要得到1 4 2 2 3 9 4 10 5 3 6 8 7 6 8 5 9 9 10 1还是要得到423410这样的