有一个这样的表:ID NAME_ID SIT_CD F_NN
1 1234 99 jkak
2 1234 98 hjhj
3 1234 91 cccc
4 1235 76 ffff
5 1235 44 fggf
6 1236 98 ddss要求取出NAME_ID相同的行(SIT_CD最小的行)
最后结果是:
ID NAME_ID SIT_CD F_NN
3 1234 91 cccc
5 1235 44 fggf
6 1236 98 ddss请问行家这个SQL怎么写呀!
1 1234 99 jkak
2 1234 98 hjhj
3 1234 91 cccc
4 1235 76 ffff
5 1235 44 fggf
6 1236 98 ddss要求取出NAME_ID相同的行(SIT_CD最小的行)
最后结果是:
ID NAME_ID SIT_CD F_NN
3 1234 91 cccc
5 1235 44 fggf
6 1236 98 ddss请问行家这个SQL怎么写呀!
1 1234 99 jkak
2 1234 98 hjhj
3 1234 91 cccc
4 1266 91 dddd
这样就不能取得了!
是正确的啊,在最后有个group by Name_ID,肯定不会出现相同Name_ID的记录的,你有没有在代码中试过啊,应该是可以的啊,你试一下就知啦
min(ID )KEEP (DENSE_RANK FIRST ORDER BY SIT_CD) ID,
min(F_NN )KEEP (DENSE_RANK FIRST ORDER BY SIT_CD) F_NN ,
min(SIT_CD)KEEP (DENSE_RANK FIRST ORDER BY SIT_CD) SIT_CD
from t
group by NAME_ID
Connected as chanetSQL>
SQL> CREATE TABLE tab_csdn(fid NUMBER(1), name_id NUMBER(4), sit_id NUMBER(2), f_nn VARCHAR2(5));Table createdSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(1, 1234, 99, 'jkak');1 row insertedSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(2, 1234, 98, 'hjhj');1 row insertedSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(3, 1234, 91, 'cccc');1 row insertedSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(4, 1235, 76, 'ffff');1 row insertedSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(5, 1235, 44, 'fggf');1 row insertedSQL> INSERT INTO tab_csdn(fid, name_id, sit_id, f_nn) VALUES(6, 1236, 98, 'ddss');1 row insertedSQL> select * from tab_csdn;FID NAME_ID SIT_ID F_NN
--- ------- ------ -----
1 1234 99 jkak
2 1234 98 hjhj
3 1234 91 cccc
4 1235 76 ffff
5 1235 44 fggf
6 1236 98 ddss6 rows selectedSQL> SELECT fid, name_id, sit_id, f_nn
2 FROM (SELECT t.*, row_number() over(PARTITION BY name_id ORDER BY sit_id) rn
3 FROM tab_csdn t)
4 WHERE rn = 1
5 /FID NAME_ID SIT_ID F_NN
--- ------- ------ -----
3 1234 91 cccc
5 1235 44 fggf
6 1236 98 ddssSQL>
from theTable a
where not exists (select 1 from theTable b where b.ID=a.ID and b.SIT_CD<a.SIT_CD)
from (select a.*,
first_value(a.sit_id) over(partition by a.name_id order by a.sit_id) value
from tab_csdn a) t1
where t1.sit_id = t1.value
(select b.ID from tablename b where b.SIT_CD in
(select min(c.SIT_CD) from tablename c group by c.NAME_ID)) ;
(select name_id,min(SIT_CD) from tablename group by NAME_ID )
可能能达到要求.
(select b.*,row_number() over(partition by name_id order by sit_cd) v_name_id
from b ) where v_name_id<2
(select name_id,min(SIT_CD) as minid from 表名 group by name_id) b
where a.name_id=b.name_id and a.SIT_CD=b.minid
WHERE a.name_id=b.name_id AND a.sit_id=b.min_sit_id;
select * from tablename where SIT_CD+NAME_ID in(select min(SIT_CD+NAME_ID) from tablename group by SIT_CD+NAME_ID)