求一條sql語句, 解決立既給分

Question

billno    id       itemno    price
10000     20060    ESD_01    1.30
10000     20068    ESD_23    52.30
10002     12521    ESD_98    12.20
10002     12521    ESD_98    15.23
10003     12554    FRD_52    125.22
........這是一張票具的表體,billno id itemno 聯合主健,billno 關聯表頭,要求結果集如下:billno    id       itemno    price
10000     1    ESD_01    1.30
10000     2    ESD_23    52.30
10002     1    ESD_98    12.20
10002     2    ESD_98    15.23
10003     1    FRD_52    125.22求sql語句一條,多謝

Answer 1

SELECT billno, rn id, itemno, price
  FROM tab,
       (SELECT ROWID row_id, rn
          FROM (SELECT ROWID,
                       ROW_NUMBER () OVER (PARTITION BY billno ORDER BY ROWID)
                                                                           rn
                  FROM tab)) x
 WHERE tab.ROWID = x.row_id

Answer 2

我的测试如下。SQL> select * from tmp
  2  /NAMES                COURSE                   GRADE
-------------------- -------------------- ---------
a                    语文                        62
b                    语文                        70
c                    语文                        80
a                    数学                        90
b                    数学                        95
c                    数学                        75
a                    英语                        85
b                    英语                        92
c                    英语                        95
a                    语文                        62
b                    语文                        70
c                    语文                        80
a                    数学                        90
b                    数学                        95
c                    数学                        75
a                    英语                        85
b                    英语                        92
c                    英语                        9518 rows selected.SQL> SELECT names, rn ID, course, grade
  2    FROM tmp,
  3         (SELECT ROWID row_id, rn
  4            FROM (SELECT ROWID,
  5                         ROW_NUMBER () OVER (PARTITION BY names ORDER BY ROWID)
  6                                                                             rn
  7                    FROM tmp)) x
  8   WHERE tmp.ROWID = x.row_id
  9  /NAMES                       ID COURSE                   GRADE
-------------------- --------- -------------------- ---------
a                            1 语文                        62
a                            2 数学                        90
a                            3 英语                        85
a                            4 语文                        62
a                            5 数学                        90
a                            6 英语                        85
b                            1 语文                        70
b                            2 数学                        95
b                            3 英语                        92
b                            4 语文                        70
b                            5 数学                        95
b                            6 英语                        92
c                            1 语文                        80
c                            2 数学                        75
c                            3 英语                        95
c                            4 语文                        80
c                            5 数学                        75
c                            6 英语                        9518 rows selected.SQL>

Answer 3

SELECT billno,
   ROW_NUMBER() OVER(PARTITION BY billno ORDER BY id) id,
   itemno,price
  FROM yourtable;

Answer 4

select * from 
(
select billno, dense_rank()over(partition by billno order by itemno, price) as id, itemno, price from tab_name 
)
order by 1,2