select depid ,name from a where depid in (select distinct depid from a )
To bzszp(www.bzszp.533.net) : 你是高手,其实我说的不够完整,应该是表的数据集为: depID Name A 甲 A 乙 A 丙 B 丁 B 戊 B 己 C 庚 C 辛 C 末 每个depID的行数不一定只有3行或更多,我想实现选择出相同depID的前两条记录。 谢谢指教!
支持 bzszp(www.bzszp.533.net) 这也是删除重复记录的方法。 delete from tbname where rowid not in(select min(rowid) from tbname group by depid);
测试: create table test(depID varchar2(1),Name varchar2(10)); insert into test values('A', '甲'); insert into test values('A' , '乙'); insert into test values('B' , '丙'); insert into test values('B' , '丁'); insert into test values('C' , '戊'); insert into test values('C' , '己');1:同一楼 select * from test where rowid in(select min(rowid) from test group by depid); /* D NAME - ---------- A 甲 B 丙 C 戊已选择3行。 */ 2: select * from test a where rowid =(select min(rowid) from test where depid = a.depid); /* D NAME - ---------- A 甲 B 丙 C 戊已选择3行。 */
To Dear all: 我的问题是【每个depID的行数不一定只有3行或更多,我想实现选择出相同depID的前两条记录】??????
再测: create table test(depID varchar2(1),Name varchar2(10)); insert into test values('A', '一'); insert into test values('A' , '二'); insert into test values('A' , '三'); insert into test values('B' , '四'); insert into test values('B' , '五'); insert into test values('B' , '六'); insert into test values('C' , '七'); insert into test values('C' , '八'); insert into test values('C' , '九'); --语句: select * from test a where rowid in (select rowid from test where depid = a.depid and rownum<3); /*结果 D NAME - ---------- A 一 A 二 B 四 B 五 C 七 C 八已选择6行。 */
15:20:00 SQL> select * from tb;COL1 COL2 ---------- ---------- A aaa A aab A aac B bba B bbb B bbc B bbd C ccc已选择8行。已用时间: 00: 00: 00.47 15:20:06 SQL> select col1,col2 from ( 15:20:11 2 select col1,col2,rank() over(partition by col1 order by col2) col3 from tb) 15:20:11 3 where col3<=2;COL1 COL2 ---------- ---------- A aaa A aab B bba B bbb C ccc已用时间: 00: 00: 00.31 15:20:13 SQL>
但仍有一问,若表a不是一张物理表,而是一张多表连接的视图,如下, select * from (select b1.rowid,a1.initials,a1.title,a1.cust_ph1,a1.cust_ph2,a1.cust_mp from tblcustomer a1, tblvehprod b1 where a1.qad_userid=b1.qad_userid and a1.status_flag=1 and b1.valid_flag=1 and a1.veh_model='A' and a1.cust_mp is not null) a 这又出现了一个新的问题:“ORA-01445:无法从没有键值保存表的连接视图中选择 ROWID” 单独执行上面Sql后面的select b1.rowid,a1.initials,a1.title,a1.cust_ph1,a1.cust_ph2,a1.cust_mp from tblcustomer a1, tblvehprod b1 where a1.qad_userid=b1.qad_userid and a1.status_flag=1 and b1.valid_flag=1 and a1.veh_model='A' and a1.cust_mp is not null是完全正确的。 请问又该如何解决呢?
select b1.rowid as rid,...
都复杂,按楼主的要求只需要 select a , first_value( b ) from DDTAB ;
sorry 写错了 select a , first_value( b ) from DDTAB group by a
select * from (select row_number() over(partition by depID order by rownum) rm,a.* from ddtab a) where rm=1
Thanks you all for your warm attention! 再问: To: txlicenhe(马可) select * from test a where rowid in (select rowid from test where depid = a.depid and rownum<3); 如果表test不是一张物理表,而是一张多表连接的视图,如何处理where子句 【where rowid in (select rowid from test where depid = a.depid and rownum<3)】中的rowid;
你是高手,其实我说的不够完整,应该是表的数据集为:
depID Name
A 甲
A 乙
A 丙
B 丁
B 戊
B 己
C 庚
C 辛
C 末
每个depID的行数不一定只有3行或更多,我想实现选择出相同depID的前两条记录。
谢谢指教!
这也是删除重复记录的方法。
delete from tbname
where rowid not in(select min(rowid) from tbname group by depid);
create table test(depID varchar2(1),Name varchar2(10));
insert into test values('A', '甲');
insert into test values('A' , '乙');
insert into test values('B' , '丙');
insert into test values('B' , '丁');
insert into test values('C' , '戊');
insert into test values('C' , '己');1:同一楼
select * from test
where rowid in(select min(rowid) from test group by depid);
/*
D NAME
- ----------
A 甲
B 丙
C 戊已选择3行。
*/
2:
select * from test a
where rowid =(select min(rowid) from test where depid = a.depid);
/*
D NAME
- ----------
A 甲
B 丙
C 戊已选择3行。
*/
我的问题是【每个depID的行数不一定只有3行或更多,我想实现选择出相同depID的前两条记录】??????
create table test(depID varchar2(1),Name varchar2(10));
insert into test values('A', '一');
insert into test values('A' , '二');
insert into test values('A' , '三');
insert into test values('B' , '四');
insert into test values('B' , '五');
insert into test values('B' , '六');
insert into test values('C' , '七');
insert into test values('C' , '八');
insert into test values('C' , '九');
--语句:
select * from test a
where rowid in (select rowid from test where depid = a.depid and rownum<3);
/*结果
D NAME
- ----------
A 一
A 二
B 四
B 五
C 七
C 八已选择6行。
*/
---------- ----------
A aaa
A aab
A aac
B bba
B bbb
B bbc
B bbd
C ccc已选择8行。已用时间: 00: 00: 00.47
15:20:06 SQL> select col1,col2 from (
15:20:11 2 select col1,col2,rank() over(partition by col1 order by col2) col3 from tb)
15:20:11 3 where col3<=2;COL1 COL2
---------- ----------
A aaa
A aab
B bba
B bbb
C ccc已用时间: 00: 00: 00.31
15:20:13 SQL>
select * from (select b1.rowid,a1.initials,a1.title,a1.cust_ph1,a1.cust_ph2,a1.cust_mp
from tblcustomer a1, tblvehprod b1
where a1.qad_userid=b1.qad_userid and
a1.status_flag=1 and b1.valid_flag=1 and a1.veh_model='A' and a1.cust_mp is not null) a
这又出现了一个新的问题:“ORA-01445:无法从没有键值保存表的连接视图中选择 ROWID”
单独执行上面Sql后面的select b1.rowid,a1.initials,a1.title,a1.cust_ph1,a1.cust_ph2,a1.cust_mp
from tblcustomer a1, tblvehprod b1
where a1.qad_userid=b1.qad_userid and
a1.status_flag=1 and b1.valid_flag=1 and a1.veh_model='A' and a1.cust_mp is not null是完全正确的。
请问又该如何解决呢?
select a , first_value( b ) from DDTAB ;
select a , first_value( b ) from DDTAB group by a
再问:
To: txlicenhe(马可)
select * from test a
where rowid in (select rowid from test where depid = a.depid and rownum<3);
如果表test不是一张物理表,而是一张多表连接的视图,如何处理where子句
【where rowid in (select rowid from test where depid = a.depid and rownum<3)】中的rowid;