select NSORT_ID sid,DCREATDATE,count(*) from SM_MESSAGE GROUP BY NSORT_ID, DCREATDATE
select NSORT_ID sid,min(MSG_ID),SSUBJECT, DCREATDATE from SM_MESSAGE GROUP BY NSORT_ID,SSUBJECT, DCREATDATE
select NSORT_ID sid,min(MSG_ID),max(SSUBJECT), max(DCREATDATE) from SM_MESSAGE GROUP BY NSORT_ID
select NSORT_ID sid, min(MSG_ID) over(partition by NSORT_ID) min from SM_MESSAGE NSORT_ID 是你进行分组的字段,按照你给出的数据,结果应该是:SID min 1 28 1 28 1 28 2 25 2 25
select * from (select t.*,row_number()over(partition by SID order by DCREATDATE) tt from SM_MESSAGE t) ttt where ttt.tt<=ii为每个分组里你想取的条数.
over(partition 什么意思呢?
over 是一个解析函数,它的返回值是基于分组后的同类多行纪录的一个总值,与聚集函数sum不同的是,前者返回每个组各行一个总值,且同组的值是相同的
SELECT t.* FROM SM_MESSAGE t,(SELECT SID,MSG_ID,rd FROM (SELECT SID,MIN(MSG_ID) MSG_ID, rank() over(ORDER BY MIN(MSG_ID) DESC ) rd FROM SM_MESSAGE GROUP BY SID)) a WHERE t.SID=a.SID
MSG_ID最小?
from SM_MESSAGE GROUP BY NSORT_ID
1 28
1 28
1 28
2 25
2 25
from (select t.*,row_number()over(partition by SID order by DCREATDATE) tt from SM_MESSAGE t) ttt
where ttt.tt<=ii为每个分组里你想取的条数.
(SELECT SID,MIN(MSG_ID) MSG_ID, rank() over(ORDER BY MIN(MSG_ID) DESC ) rd FROM SM_MESSAGE GROUP BY SID)) a
WHERE t.SID=a.SID
18:01:26 2 sid VARCHAR2(10),
18:01:26 3 msg_id NUMBER(4),
18:01:26 4 SSUBJECT VARCHAR2(100)
18:01:26 5 );
表已创建。已用时间: 00: 00: 00.00
18:01:28 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('1',28,'高配置奔四电脑转让了+ 08/11/200
4 16:01:09');已创建 1 行。已用时间: 00: 00: 00.01
18:04:09 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('1',30,'寻求笔记本电脑 08/11/2004 16:03
:07');已创建 1 行。已用时间: 00: 00: 00.00
18:04:09 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('1',34,'联想笔记本5500元 08/11/2004 16:
07:23');已创建 1 行。已用时间: 00: 00: 00.00
18:04:09 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('1',25,'二手办公家具低价 08/11/2004 15:
58:27');已创建 1 行。已用时间: 00: 00: 00.00
18:04:09 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('1',29,'低价二手办公家具,机会难得 08/1
1/2004 16:01:34');已创建 1 行。已用时间: 00: 00: 00.00
18:04:18 SQL*PLUS>commit;提交完成。已用时间: 00: 00: 00.00
18:15:19 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('2',29,'低价二手办公家具,机会难得 08/1
');已创建 1 行。已用时间: 00: 00: 01.02
18:16:04 SQL*PLUS>INSERT INTO aaa(SID,MSG_ID,SSUBJECT)VALUES('2',24,'低价二手办公家具,机会难得 08/1
');已创建 1 行。已用时间: 00: 00: 00.01
SQL>
SQL> SELECT T.*
2 FROM AAA T,
3 (SELECT SID, MSG_ID, RD
4 FROM (SELECT SID,
5 MIN(MSG_ID) MSG_ID,
6 RANK() OVER(ORDER BY MIN(MSG_ID) DESC) RD
7 FROM AAA
8 GROUP BY SID)) A
9 WHERE T.SID = A.SID
10 AND t.msg_id=a.MSG_ID
11 ;SID MSG_ID SSUBJECT
---------- ------ --------------------------------------------------------------------------------
1 25 二手办公家具低价 08/11/2004 15:58:27
2 24 低价二手办公家具,机会难得 08/1SQ