如何实现这样特殊的子查询 有一个表
create table PeccancyRecord
(
ID VARCHAR2(50) not null,
RECORDID VARCHAR2(20) not null,
DRIVINGLICENSENO VARCHAR2(15),
DRIVERNAME VARCHAR2(15),
CARMARK VARCHAR2(15),
CARTYPE VARCHAR2(15),
WORKNAME VARCHAR2(100),
ACCOUNTNO VARCHAR2(15),
CHECKINGPOSITION VARCHAR2(50),
CHECKINGFORM VARCHAR2(50),
CHECKINGDATE DATE,
TEAMNO NUMBER(10),
CHECKINGGROUPNO VARCHAR2(15),
INSPECTOR VARCHAR2(15),
OPERATIONKIND VARCHAR2(50),
CHECKINGCONDITION VARCHAR2(500),
REVOKEFLAG NUMBER(10),
WARNFLAG NUMBER(10),
WRITEOFFFLAG NUMBER(10),
FORFEITSUM NUMBER(10),
KEAPLENGTH NUMBER(10),
MANAGESTATE NUMBER(10),
TRAINFLAG NUMBER(10),
MANAGEFLAG NUMBER(10),
RECORDFLAG NUMBER(10),
HANDLER VARCHAR2(15),
CHECKDATETIME DATE,
RESOLVEDATE DATE,
USERFEEDBACK VARCHAR2(100),
WORKFORFEIT NUMBER(10),
WORKRESOLVEIDEA VARCHAR2(100),
MODEL VARCHAR2(50),
CARCOLOR VARCHAR2(15),
ARTIFICIALPERSON VARCHAR2(15),
POSITION VARCHAR2(50),
ADDRESS VARCHAR2(50),
PHONENO VARCHAR2(30),
POSTALCODE VARCHAR2(15),
HISTORYRECORD VARCHAR2(500),
RECORDTIME DATE,
ILLEGALRECORD VARCHAR2(50),
ILLEGALKIND VARCHAR2(30),
PUNISHPERSON NUMBER(10),
PUNISHCOMPANY NUMBER(10),
DISTRICTNO VARCHAR2(10),
YSZTYPE VARCHAR2(50),
PUNISH NUMBER(10),
TIEDOCID VARCHAR2(15),
WHOLEFACTORYNAME VARCHAR2(50),
HOMEADDR VARCHAR2(50),
DELCASE NUMBER(2),
YSZNUM VARCHAR2(15),
SETDATETIME DATE,
KOUFENBZ VARCHAR2(10),
KOUFEN NUMBER(10),
TEACHFLAG NUMBER(10),
SUBMITDATE DATE,
SUBMITED NUMBER(10) not null,
EDITDATETIME DATE,
CUR_YEAR VARCHAR2(8),
CUR_MONTH VARCHAR2(4)
)
要求是这样:
如有多条“CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录,则仅保留ManageState数值最大的那条记录
我写了
select a.CarMark,a.CheckingDate,a.CheckingPosition,a.ManageState from "PeccancyRecord" a where id in
(select id from "PeccancyRecord" b
where ManageState=
(
select max(ManageState) from "PeccancyRecord" c where
c.CarMark=b.CarMark
and c.CheckingDate=b.CheckingDate
and c.CheckingPosition=b.CheckingPosition
)
)
order by a.CarMark
数据库为oraclel9i
其中CheckingPositon有null值
结果查出来的数据除了我想要的CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录的ManageState最大的那条外 还有这3个字段不重复的只有一条的记录 若将最外层的in改为not in 则除了有CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录的ManageState除了最大的那条剩下的外 还有CheckingPositon为null的记录也查出来了不知道这个问题怎么解决 一个查询能否解决 还是要写pl/sql
create table PeccancyRecord
(
ID VARCHAR2(50) not null,
RECORDID VARCHAR2(20) not null,
DRIVINGLICENSENO VARCHAR2(15),
DRIVERNAME VARCHAR2(15),
CARMARK VARCHAR2(15),
CARTYPE VARCHAR2(15),
WORKNAME VARCHAR2(100),
ACCOUNTNO VARCHAR2(15),
CHECKINGPOSITION VARCHAR2(50),
CHECKINGFORM VARCHAR2(50),
CHECKINGDATE DATE,
TEAMNO NUMBER(10),
CHECKINGGROUPNO VARCHAR2(15),
INSPECTOR VARCHAR2(15),
OPERATIONKIND VARCHAR2(50),
CHECKINGCONDITION VARCHAR2(500),
REVOKEFLAG NUMBER(10),
WARNFLAG NUMBER(10),
WRITEOFFFLAG NUMBER(10),
FORFEITSUM NUMBER(10),
KEAPLENGTH NUMBER(10),
MANAGESTATE NUMBER(10),
TRAINFLAG NUMBER(10),
MANAGEFLAG NUMBER(10),
RECORDFLAG NUMBER(10),
HANDLER VARCHAR2(15),
CHECKDATETIME DATE,
RESOLVEDATE DATE,
USERFEEDBACK VARCHAR2(100),
WORKFORFEIT NUMBER(10),
WORKRESOLVEIDEA VARCHAR2(100),
MODEL VARCHAR2(50),
CARCOLOR VARCHAR2(15),
ARTIFICIALPERSON VARCHAR2(15),
POSITION VARCHAR2(50),
ADDRESS VARCHAR2(50),
PHONENO VARCHAR2(30),
POSTALCODE VARCHAR2(15),
HISTORYRECORD VARCHAR2(500),
RECORDTIME DATE,
ILLEGALRECORD VARCHAR2(50),
ILLEGALKIND VARCHAR2(30),
PUNISHPERSON NUMBER(10),
PUNISHCOMPANY NUMBER(10),
DISTRICTNO VARCHAR2(10),
YSZTYPE VARCHAR2(50),
PUNISH NUMBER(10),
TIEDOCID VARCHAR2(15),
WHOLEFACTORYNAME VARCHAR2(50),
HOMEADDR VARCHAR2(50),
DELCASE NUMBER(2),
YSZNUM VARCHAR2(15),
SETDATETIME DATE,
KOUFENBZ VARCHAR2(10),
KOUFEN NUMBER(10),
TEACHFLAG NUMBER(10),
SUBMITDATE DATE,
SUBMITED NUMBER(10) not null,
EDITDATETIME DATE,
CUR_YEAR VARCHAR2(8),
CUR_MONTH VARCHAR2(4)
)
要求是这样:
如有多条“CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录,则仅保留ManageState数值最大的那条记录
我写了
select a.CarMark,a.CheckingDate,a.CheckingPosition,a.ManageState from "PeccancyRecord" a where id in
(select id from "PeccancyRecord" b
where ManageState=
(
select max(ManageState) from "PeccancyRecord" c where
c.CarMark=b.CarMark
and c.CheckingDate=b.CheckingDate
and c.CheckingPosition=b.CheckingPosition
)
)
order by a.CarMark
数据库为oraclel9i
其中CheckingPositon有null值
结果查出来的数据除了我想要的CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录的ManageState最大的那条外 还有这3个字段不重复的只有一条的记录 若将最外层的in改为not in 则除了有CarMark、CheckingDate、CheckingPositon”三个字段都相同的记录的ManageState除了最大的那条剩下的外 还有CheckingPositon为null的记录也查出来了不知道这个问题怎么解决 一个查询能否解决 还是要写pl/sql
msn [email protected] 请注明
这样试试
select CarMark,CheckingDate,CheckingPosition,ManageState
from (
select CarMark,CheckingDate,CheckingPosition,max(ManageState) as ManageState,count(*) as cnt
from "PeccancyRecord"
group by CarMark,CheckingDate,CheckingPosition
having count(*)>1
) order by CarMark;
msn [email protected]
select * from
(
select a.*, row_number() over(partition by CarMark,CheckingDate,CheckingPosition order by ManageState desc ) as row_num
from PeccancyRecord a
) b
where row_num=1