目前有如下语句:
select distinct oldnumber,
res,
wellid,
id
from SAMPLEREGISTERINFO
where ID in ( 1809946,1809945,1809947,1809948,1809949)
order by id查询结果为:OLDNUMBER REMARKS WELLID ID
1 粉砂岩 杏83 1809945
1 粉砂岩 杏83 1809946
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809948
2 泥岩 杏83 1809949其中ID列为唯一索引。我想要的结果应该是这样OLDNUMBER REMARKS WELLID ID
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809949也就是,不考虑ID列,只要ID in ( 1809946,1809945,1809947,1809948,1809949) 条件确定的记录行中,OldNumber相同时,只取一行就可以。但记录顺序要求按照ID排序,ID列要不要都无所谓,如果要的话,随机取一个ID就可以。条件ID in ( 1809946,1809945,1809947,1809948,1809949)中的ID号是可变的。
注意:OleNumber字段中有汉字的情况,所以不能用OldNumber排序。我用如下语句实现后无法进行排序
select oldnumber,res ,wellid from (
select distinct oldnumber,res,wellid,id
from SAMPLEREGISTERINFO
where ID in ( 1809946,1809945,1809947,1809948,1809949)
order by id) a group by oldnumber,res ,wellid
select distinct oldnumber,
res,
wellid,
id
from SAMPLEREGISTERINFO
where ID in ( 1809946,1809945,1809947,1809948,1809949)
order by id查询结果为:OLDNUMBER REMARKS WELLID ID
1 粉砂岩 杏83 1809945
1 粉砂岩 杏83 1809946
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809948
2 泥岩 杏83 1809949其中ID列为唯一索引。我想要的结果应该是这样OLDNUMBER REMARKS WELLID ID
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809949也就是,不考虑ID列,只要ID in ( 1809946,1809945,1809947,1809948,1809949) 条件确定的记录行中,OldNumber相同时,只取一行就可以。但记录顺序要求按照ID排序,ID列要不要都无所谓,如果要的话,随机取一个ID就可以。条件ID in ( 1809946,1809945,1809947,1809948,1809949)中的ID号是可变的。
注意:OleNumber字段中有汉字的情况,所以不能用OldNumber排序。我用如下语句实现后无法进行排序
select oldnumber,res ,wellid from (
select distinct oldnumber,res,wellid,id
from SAMPLEREGISTERINFO
where ID in ( 1809946,1809945,1809947,1809948,1809949)
order by id) a group by oldnumber,res ,wellid
FROM SAMPLEREGISTERINFO a
WHERE a.ID IN (1809946, 1809945, 1809947, 1809948, 1809949)
AND NOT EXISTS (SELECT *
FROM SAMPLEREGISTERINFO b
WHERE b.OLDNUMBER = a.OLDNUMBER
AND b.REMARKS = a.REMARKS
AND b.WELLID = a.WELLID
AND b.id > a.id)
ORDER BY id;
语句不对啊。执行完了。还是我之前的语句的效果。
OLDNUMBER REMARKS WELLID ID
1 粉砂岩 杏83 1809945
1 粉砂岩 杏83 1809946
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809948
2 泥岩 杏83 1809949
2 select 1 OLDNUMBER,'粉砂岩' REMARKS,'杏83' WELLID, 1809945 ID from dual union all
3 select 1 OLDNUMBER,'粉砂岩' REMARKS,'杏83' WELLID, 1809946 ID from dual union all
4 select 1 OLDNUMBER,'粉砂岩' REMARKS,'杏83' WELLID, 1809947 ID from dual union all
5 select 2 OLDNUMBER,'泥岩' REMARKS,'杏83' WELLID, 1809948 ID from dual union all
6 select 2 OLDNUMBER,'泥岩' REMARKS,'杏83' WELLID, 1809949 ID from dual)
7 SELECT *
8 FROM SAMPLEREGISTERINFO a
9 WHERE a.ID IN (1809946, 1809945, 1809947, 1809948, 1809949)
10 AND NOT EXISTS (SELECT *
11 FROM SAMPLEREGISTERINFO b
12 WHERE b.OLDNUMBER = a.OLDNUMBER
13 AND b.REMARKS = a.REMARKS
14 AND b.WELLID = a.WELLID
15 AND b.id > a.id)
16 ORDER BY id;
OLDNUMBER REMARKS WELLID ID
---------- ------- ------ ----------
1 粉砂岩 杏83 1809947
2 泥岩 杏83 1809949
SQL>
--需要在你内层查询中设定一个跟oldnumber,res ,wellid 一个组的,并且有大小,
--这里用的max()取oldnumber,res ,wellid 分组下ID的最大值,从而外层可以按此列来排序,
--即达到按照ID来排序的目的:select oldnumber,res ,wellid from (
select distinct oldnumber,res,wellid,id,max(id)over(partition by oldnumber,res,wellid) rid
from SAMPLEREGISTERINFO
where ID in ( 1809946,1809945,1809947,1809948,1809949)
order by id) a group by oldnumber,res ,wellid ,rid
order by rid
from(
select '1' as oldnumber,'粉砂岩' as res,'杏83' as wellid,1809945 as id
from dual
union all
select '1','粉砂岩','杏83',1809946
from dual
union all
select '1','粉砂岩','杏83',1809947
from dual
union all
select '2','泥岩','杏83',1809948
from dual
union all
select '2','泥岩','杏83',1809949
from dual
)
group by oldnumber,res,wellid
order by id
会不会我想得太简单了 >_<
from (
select oldnumber,res,wellid,id
from test
where ID in ( 1809946,1809945,1809947,1809948,1809949)
) a group by oldnumber,res ,wellid