1)table AA strucure:inquiry_id , email_id , sid , ppid ,type
2)select inquiry_id,sid,ppid,type
from AA
group by inquiry_id,sid,ppid,type having count(1) =2
order by inquiry_id,sid,ppid
inquiry_id sid ppid type count8000013444326 6008824985041 1008186222 S 2
8000013444326 6008824985041 1008276447 S 2
8000013444378 6008802481609 1005683880 S 2
8000013444400 6008824780848 1003518478 S 2
8000013444400 6008824780848 1003518819 S 2
上面的结果是select 出来的,也就是每行记录有在AA中有两条纪录。但是有不一样的email_id3)to do :现在要把上面的每两条记录的email_id 大的那条纪录的type值变成"P"。该怎么写SQL呢??
紧急求助,谢谢!!
2)select inquiry_id,sid,ppid,type
from AA
group by inquiry_id,sid,ppid,type having count(1) =2
order by inquiry_id,sid,ppid
inquiry_id sid ppid type count8000013444326 6008824985041 1008186222 S 2
8000013444326 6008824985041 1008276447 S 2
8000013444378 6008802481609 1005683880 S 2
8000013444400 6008824780848 1003518478 S 2
8000013444400 6008824780848 1003518819 S 2
上面的结果是select 出来的,也就是每行记录有在AA中有两条纪录。但是有不一样的email_id3)to do :现在要把上面的每两条记录的email_id 大的那条纪录的type值变成"P"。该怎么写SQL呢??
紧急求助,谢谢!!
解决方案 »
- 这可能么?
- 做分布式事务时连接到oracle数据库报错,MSSQL是可行的
- 高分求助一个存储过程问题
- 这个sql怎么写,我想把他导入oracle数据库中
- insert into 表(字段) select 字段 from 表,出现ORA-00997: illegal use of LONG datatype
- ORACLE数据库崩溃错误---RBS1ORCL.ORA
- 高手们请帮帮忙!!!
- 急!!!当删除一个表空间时,该表空间中数据表所对应的序列也能一起被删除吗?(高分!!)
- 怎样写这个sql语句?多谢
- 请问一个Pro*C的简单问题:
- 请教SQL*PLUS中有没有启动审核的命令
- 如何让oracle的错误提示为中文
SQL> select * from aa;INQUIRY_ID EMAIL_ID SID PPID TYPE
---------- ------------------------------------------------------------ ----- ----- ----
1 [email protected] 2 3 S
1 [email protected] 2 3 S
2 [email protected] 2 3 S
2 [email protected] 2 3 S
SQL> update aa set type='P' where not exists(
2 select 1 from (
3 select inquiry_id,email_id,sid,ppid,type from (
4 select inquiry_id,email_id,sid,ppid,type,
5 row_number()over(partition by inquiry_id,sid,ppid,type order by email_id desc) rn from aa
6 ) where rn=1
7 ) BB
8 where aa.inquiry_id=bb.inquiry_id
9 and aa.email_id=bb.email_id
10 and aa.sid=bb.sid
11 and aa.ppid = bb.ppid
12 and aa.type=bb.type
13 );2 rows updatedSQL> select * from aa;INQUIRY_ID EMAIL_ID SID PPID TYPE
---------- ------------------------------------------------------------ ----- ----- ----
1 [email protected] 2 3 S
1 [email protected] 2 3 P
2 [email protected] 2 3 P
2 [email protected] 2 3 S
SQL> select * from aa;INQUIRY_ID EMAIL_ID SID PPID TYPE
---------- ------------------------------------------------------------ ----- ----- ----
1 [email protected] 2 3 S
1 [email protected] 2 3 S
2 [email protected] 2 3 S
2 [email protected] 2 3 S
SQL> update aa set type='P' where not exists(
2 select 1 from (
3 select inquiry_id,email_id,sid,ppid,type from (
4 select inquiry_id,email_id,sid,ppid,type,
5 row_number()over(partition by inquiry_id,sid,ppid,type order by email_id desc) rn from aa
6 ) where rn=1
7 ) BB
8 where aa.inquiry_id=bb.inquiry_id
9 and aa.email_id=bb.email_id
10 and aa.sid=bb.sid
11 and aa.ppid = bb.ppid
12 and aa.type=bb.type
13 );2 rows updatedSQL> select * from aa;INQUIRY_ID EMAIL_ID SID PPID TYPE
---------- ------------------------------------------------------------ ----- ----- ----
1 [email protected] 2 3 S
1 [email protected] 2 3 P
2 [email protected] 2 3 P
2 [email protected] 2 3 S
SELECT INQUIRY_ID, SID, PPID, DECODE(RN, 1, 'P', TYPE) TYPESS, COUNTS
FROM (SELECT AA.*,
ROW_NUMBER() OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE ORDER BY EMAIL_ID DESC) RN,
COUNT(1) OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE) COUNTS
FROM AA) A
WHERE COUNTS > 1;
UPDATE aa
SET aa.TYPE = 'p'
WHERE aa.ROWID IN
(SELECT m.ROWID
FROM ROWID row_number() over(PARTITION BY inquiry_id, sid, ppid ORDER BY email_id DESC) rn)
WHERE rn = 1) m
UPDATE aa
SET aa.TYPE = 'p'
WHERE aa.ROWID IN (SELECT m.ROWID
FROM (SELECT ROWID row_number() over(PARTITION BY inquiry_id, sid, ppid ORDER BY email_id DESC) rn) m
WHERE m.rn = 1)
inquiry_id , email_id , sid , ppid ,type 1 [email protected] 11 1111 s
1 [email protected] 11 1111 s
2 [email protected] 22 2222 s
3 [email protected] 22 2222 sUPDATE AA
SET TYPE = 'p'
WHERE EXISTS
(SELECT 1
FROM (SELECT A.INQUIRY_ID, A.SID, A.PPID, MAX(A.EMAIL_ID) EMAIL_ID
FROM AA A
GROUP BY A.INQUIRY_ID, A.SID, A.PPID
HAVING COUNT(*) = 2) TAB
WHERE AA.INQUIRY_ID = TAB.INQUIRY_ID
AND AA.SID = TAB.SID
AND AA.EMAIL_ID = TAB.EMAIL_ID)
1 [email protected] 11 1111 s
1 [email protected] 11 1111 p
2 [email protected] 22 2222 s
3 [email protected] 22 2222 p
ROW_NUMBER() OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE ORDER BY EMAIL_ID DESC) RN,
COUNT(1) OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE) COUNTS
FROM AA 中, 1)OVER(PARTITION BY INQUIRY_ID, SID, PPID, TYPE ORDER BY EMAIL_ID DESC))
over (parion by ...) 怎么个语法? 2)ROW_NUMBER() 呢?
多谢!