一個Oracle查詢的 sql 問題

我的問題是這樣的：

有一個表 t ，欄位 c1, c2, c3, c4，要求從c1的重複值記錄中隨機選一條，構成結果集。怎麼實現？

比如記錄如下：

aa1 bb1 cc1 dd1
aa1 bb2 cc2 dd2
aa1 bb3 cc3 dd3
aa2 bb4 cc4 dd4
aa2 bb5 cc5 dd5
aa2 bb6 cc6 dd6
.....

結果是從值aa1中隨機選取一條記錄， aa2中隨機選取一條記錄，依此類推，得出結果集。用單條 sql 語句怎麼實現？

lastwinner的語句是：

select b.c1, b.c2, b.c3, b.c4 from
(select c1, 0 as lb, count(*) as ub from t2 group by c1) a,
(select c1, c2, c3, c4, row_number()over(partition by c1 order by c2, c3, c4) rn from t2) b
where a.c1=b.c1
and ceil(dbms_random.value(a.lb, a.ub)) = b.rn;

這個實現思路很妙。第一個子查詢按欄位c1分組，得出c1每個值對應記錄數的上下限；第二個字查詢利用row_number()分析函數，按欄位c1分區，c1值相同的記錄編號；外層查詢把兩個子查詢的結果集用c1欄位關聯，在每個c1值對記錄數上下限之間隨機取值，並且利用ceil函數可以取到兩端的邊界值。

分析了上面的查詢語句之後，看到 jackywood 的語句 更簡單：

select *
    from (select C1, C2, C3, C4,
                 row_number() over(partition by c1 order by dbms_random.value) as rn
          from t2)
where rn = 1;

把隨機值直接用到 row_number() 分析函數的order by的參數中，好，更簡單。

兩個方案都很好，執行的效率都可以，第二個更直接、更簡單。其實這樣的需求還是挺常見的，比如有一個歌曲庫song，每首歌曲有唯一的一個songid，每個演唱者singer有多首歌曲，現在想要從一個歌手及歌曲的列表，要求從每個歌手演唱的歌曲中隨機選出一首歌曲，就可以這樣寫：

select *
    from (select t.*,
            row_number() over(partition by singer order by dbms_random.value) as rn
from songs t)
where rn = 1;

或者

select * from
(select singer, 0 as lb, count(*) as ub from songs group by singer) a,
(select t.*, row_number()over(partition by singer order by songid) rn
from songs t) b
where a.singer = b.singer
and ceil(dbms_random.value(a.lb, a.ub)) = b.rn;

解決問題的關鍵是 row_number() 分析函數的使用。

先面就簡單介紹一下相關的內容。

ROWID，ROWNUM，ROW_NUMBER()的區別
(1)、rowid是指記錄的物理序號；
(2)、rownum僅指記錄的序號；
(3)、row_numer(order by colunmx) 是一個函數，可能通過它來給記錄指定序號；
並且 row_numer(partition by colunmx order by colunmy)指為列colunmx按colunmy的順序指定序號，每一個colunmx的值都是從1開始到最後一個，因此這很方便找重複記錄！ 

下面是一個常見的rowid和row_number的“查重”應用：
(1)、
SELECT * FROM table1 WHERE ROWID!=(SELECT MAX(ROWID) FROM table1 D 
                   WHERE table1.colunm1=D.colunm1 AND table1.colunm2=D.colunm2);
(2)、
SELECT * FROM (
    SELECT * FROM 
        (SELECT a.*,ROW_NUMBER() OVER(PARTITION BY colunm1,colunm2 order by colunmx) b FROM table1 a
) aa
WHERE aa.b=1;

註：查詢table1中column1和column2中重複的記錄。