select m.* from diekiss m, (select min(id) as mid from diekiss group by doc_id,person) p where m.id = p.mid and doc_id = 1 order by doc_id,person ;
select min(id) as id ,person,message from diekiss group by person,message having doc_id=1
如果将数据库的所有DOC_ID呢?每个DOC_ID中只去PERSON的头一条记录
select m.* from diekiss m, (select min(id) as mid from diekiss group by doc_id,person) p where m.id = p.mid order by doc_id,person ;
select a.* from tableA a,(select person,min(rowid) rowid1 from tableA where doc_id = '1' group by person) b where a.rowid = b.rowid1 order by a.id;
支持select m.* from diekiss m, (select min(id) as mid from diekiss group by doc_id,person) p where m.id = p.mid order by doc_id,person ;
masterz(www.fruitfruit.com) ( ) 的方法已经解决了问题了
用自连接: select * from table t1 where not exists (select * from table where dco_id=1 and person=t1.person and id<t1.id) and doc_id=1或者用分析函数
1。建表语句 CREATE TABLE ZHOUXY.T_BBS ( ID NUMBER(2,0), DOC_ID NUMBER(2,0), PERSON VARCHAR2(10), MESSAGE VARCHAR2(50) ) /2。测试数据 ID DOC_ID PERSON MESSAGE 1 1 mike hello 2 1 tom good 3 2 tom ok 4 1 tom goodbye 5 2 mike hi 6 1 kate yes 3。SQL文SELECT ID , DOC_ID , PERSON , MESSAGE FROM ( SELECT ID , DOC_ID , PERSON , MESSAGE , RANK ( ) OVER ( PARTITION BY DOC_ID , PERSON ORDER BY DOC_ID , PERSON , ID ) RANK FROM T_BBS WHERE DOC_ID = 1 ) WHERE RANK =1 ORDER BY ID4。测试结果ID DOC_ID PERSON MESSAGE 1 1 mike hello 2 1 tom good 6 1 kate yes
你可以这样
select distinct person,doc_id form ...
大家能理解我说的吗?谢谢
在代码中就很好控制了
(select min(id) as mid from diekiss group by doc_id,person) p
where m.id = p.mid and doc_id = 1
order by doc_id,person
;
group by person,message having doc_id=1
(select min(id) as mid from diekiss group by doc_id,person) p
where m.id = p.mid
order by doc_id,person
;
(select min(id) as mid from diekiss group by doc_id,person) p
where m.id = p.mid
order by doc_id,person
;
select * from table t1 where not exists (select * from table where dco_id=1 and person=t1.person and id<t1.id) and doc_id=1或者用分析函数
CREATE TABLE ZHOUXY.T_BBS
(
ID NUMBER(2,0),
DOC_ID NUMBER(2,0),
PERSON VARCHAR2(10),
MESSAGE VARCHAR2(50)
)
/2。测试数据
ID DOC_ID PERSON MESSAGE
1 1 mike hello
2 1 tom good
3 2 tom ok
4 1 tom goodbye
5 2 mike hi
6 1 kate yes
3。SQL文SELECT
ID ,
DOC_ID ,
PERSON ,
MESSAGE
FROM
(
SELECT
ID ,
DOC_ID ,
PERSON ,
MESSAGE ,
RANK ( ) OVER ( PARTITION BY DOC_ID ,
PERSON
ORDER BY
DOC_ID ,
PERSON ,
ID ) RANK
FROM
T_BBS
WHERE
DOC_ID = 1 )
WHERE
RANK =1
ORDER BY
ID4。测试结果ID DOC_ID PERSON MESSAGE
1 1 mike hello
2 1 tom good
6 1 kate yes