求一SQL语句

Question

表：tbl
ID    NAME
-------------------
1    X1
1    X2
1    X3
2    Y1
2    Y2
2    Y3
3    Z1
3    Z2
3    Z3
我想按每个ID相同的取２条记录，想得到结果如下：
ID    NAME
-------------------
1    X1
1    X3
2    Y1
2    Y2
3    Z1
3    Z3
这样的SQL语句如何写？

Answer 1

select * from tbl
      WHERE ROWID IN (
               SELECT ROWID
                 FROM (SELECT ROWID,
                              ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ROWID) rn
                         FROM tbl)
                WHERE rn < 3)

Answer 2

SQL> select * from tmp order by names
  2  /NAMES                COURSE                   GRADE
-------------------- -------------------- ---------
a                    语文                        62
a                    数学                        90
a                    英语                        85
a                    语文                        62
a                    英语                        85
a                    数学                        90
b                    语文                        70
b                    英语                        92
b                    数学                        95
b                    英语                        92
b                    语文                        70
b                    数学                        95
c                    语文                        80
c                    英语                        95
c                    语文                        80
c                    数学                        75
c                    英语                        95
c                    数学                        7518 rows selected.SQL> SELECT   *
  2      FROM tmp
  3     WHERE ROWID IN (
  4              SELECT ROWID
  5                FROM (SELECT ROWID,
  6                             ROW_NUMBER () OVER (PARTITION BY names ORDER BY ROWID)
  7                                                                             rn
  8                        FROM tmp)
  9               WHERE rn < 3)
 10  ORDER BY NAMES
 11  /NAMES                COURSE                   GRADE
-------------------- -------------------- ---------
a                    语文                        62
a                    数学                        90
b                    语文                        70
b                    数学                        95
c                    语文                        80
c                    数学                        756 rows selected.SQL>