USER_ID BILL_MONTH TYPE
194000004449 20110501 1
194000004449 20110502 1
194000004449 20110505 2
194000004449 20110506 2
194000004449 20110507 1
194000004449 20110508 1
194000004449 20110509 1
194000004449 20110510 2
194000004449 20110511 2
194000004449 20110512 2
194000004449 20110514 2
194000004449 20110515 1
194000004449 20110516 1
194000004449 20110517 1
194000004449 20110518 1
194000004449 20110519 1
194000004449 20110520 1
194000004449 20110521 1
194000004449 20110522 1
194000004449 20110523 1
194000004449 20110524 1
194000004449 20110525 1
194000004449 20110526 1
194000004449 20110527 1
我想对数据进行排名需得到如下结果
USER_ID BILL_MONTH TYPE PM
194000004449 20110501 1 1
194000004449 20110502 1 1
194000004449 20110505 2 2
194000004449 20110506 2 2
194000004449 20110507 1 3
194000004449 20110508 1 3
194000004449 20110509 1 3
194000004449 20110510 2 4
194000004449 20110511 2 4
194000004449 20110512 2 4
194000004449 20110514 2 4
194000004449 20110515 1 5
194000004449 20110516 1 5
194000004449 20110517 1 5
194000004449 20110518 1 5
194000004449 20110519 1 5
194000004449 20110520 1 5
194000004449 20110521 1 5
194000004449 20110522 1 5
194000004449 20110523 1 5
194000004449 20110524 1 5
194000004449 20110525 1 5
194000004449 20110526 1 5
194000004449 20110527 1 5请问SQL语句如何写
194000004449 20110501 1
194000004449 20110502 1
194000004449 20110505 2
194000004449 20110506 2
194000004449 20110507 1
194000004449 20110508 1
194000004449 20110509 1
194000004449 20110510 2
194000004449 20110511 2
194000004449 20110512 2
194000004449 20110514 2
194000004449 20110515 1
194000004449 20110516 1
194000004449 20110517 1
194000004449 20110518 1
194000004449 20110519 1
194000004449 20110520 1
194000004449 20110521 1
194000004449 20110522 1
194000004449 20110523 1
194000004449 20110524 1
194000004449 20110525 1
194000004449 20110526 1
194000004449 20110527 1
我想对数据进行排名需得到如下结果
USER_ID BILL_MONTH TYPE PM
194000004449 20110501 1 1
194000004449 20110502 1 1
194000004449 20110505 2 2
194000004449 20110506 2 2
194000004449 20110507 1 3
194000004449 20110508 1 3
194000004449 20110509 1 3
194000004449 20110510 2 4
194000004449 20110511 2 4
194000004449 20110512 2 4
194000004449 20110514 2 4
194000004449 20110515 1 5
194000004449 20110516 1 5
194000004449 20110517 1 5
194000004449 20110518 1 5
194000004449 20110519 1 5
194000004449 20110520 1 5
194000004449 20110521 1 5
194000004449 20110522 1 5
194000004449 20110523 1 5
194000004449 20110524 1 5
194000004449 20110525 1 5
194000004449 20110526 1 5
194000004449 20110527 1 5请问SQL语句如何写
select USER_ID,BILL_MONTH ,type,count(*) from table group by USER_ID,BILL_MONTH order by USER_ID,BILL_MONTH
Name Type Nullable Default Comments
---------- -------- -------- ------- --------
USER_ID VARCHAR(12) Y
BILL_MONTH VARCHAR(8) Y
TYPE NUMBER Y
SQL> select * from t;
USER_ID BILL_MONTH TYPE
------------ ---------- ----------
194000004449 20110501 1
194000004449 20110502 1
194000004449 20110505 2
194000004449 20110506 2
194000004449 20110507 1
194000004449 20110508 1
194000004449 20110509 1
194000004449 20110510 2
194000004449 20110511 2
194000004449 20110512 2
194000004449 20110514 2
194000004449 20110515 1
194000004449 20110516 1
194000004449 20110517 1
194000004449 20110518 1
194000004449 20110519 1
194000004449 20110520 1
194000004449 20110521 1
194000004449 20110522 1
194000004449 20110523 1
194000004449 20110524 1
194000004449 20110525 1
194000004449 20110526 1
194000004449 20110527 1
24 rows selected
SQL>
SQL> SELECT t.user_id, t.bill_month, t.type, b.rn
2 FROM t,
3 (SELECT a.*,
4 lead(p_month, 1, '30000101') over(ORDER BY bill_month) e_month,
5 rownum rn
6 FROM (SELECT t.*,
7 lag(TYPE, 1, -1) over(ORDER BY t.bill_month) p_type,
8 lag(bill_month, 1, '19000101') over(ORDER BY t.bill_month) p_month
9 FROM t) a
10 WHERE TYPE <> p_type) b
11 WHERE t.bill_month BETWEEN b.bill_month AND e_month;
USER_ID BILL_MONTH TYPE RN
------------ ---------- ---------- ----------
194000004449 20110501 1 1
194000004449 20110502 1 1
194000004449 20110505 2 2
194000004449 20110506 2 2
194000004449 20110507 1 3
194000004449 20110508 1 3
194000004449 20110509 1 3
194000004449 20110510 2 4
194000004449 20110511 2 4
194000004449 20110512 2 4
194000004449 20110514 2 4
194000004449 20110515 1 5
194000004449 20110516 1 5
194000004449 20110517 1 5
194000004449 20110518 1 5
194000004449 20110519 1 5
194000004449 20110520 1 5
194000004449 20110521 1 5
194000004449 20110522 1 5
194000004449 20110523 1 5
194000004449 20110524 1 5
194000004449 20110525 1 5
194000004449 20110526 1 5
194000004449 20110527 1 5
24 rows selected
SQL>