一个小sql的问题记录 对分析函数的应用

el/2024/7/17 20:50:59

附件:第1次提的需求
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001'   FROM DUAL
UNION ALL
SELECT 3,'001'   FROM DUAL
UNION ALL
SELECT 4,'002'   FROM DUAL
UNION ALL
SELECT 5,'003'   FROM DUAL
UNION ALL
SELECT 6,'008'   FROM DUAL
UNION ALL
SELECT 7,'003'   FROM DUAL
UNION ALL
SELECT 8,'003'   FROM DUAL
UNION ALL
SELECT 9,'002'   FROM DUAL
UNION ALL
SELECT 10,'002'   FROM DUAL
)
SELECT * FROM TB1;


LSH USERID
1    001
2    001
3    001
4    002
5    003
6    008
7    003
8    003
9    002
10  002


--2.希望得到的结果:
1)同一个USERID,如果LSH连续,如何为其生成相同的NEW_LSH?
2)NEW_LSH不能重复使用
3)不用游标
LSH USERID  NEW_LSH
1    001     1
2    001     1
3    001     1
4    002     4
5    003     5
6    008     6
7    003     7
8    003     7
9    002     9
10  002     9


可以达到要求的sql语句:

第一种方法:

select lsh, userid, min(lsh) over(partition by userid, flag) new_lsh
  from (SELECT lsh,
               userid,
               case
                 when userid = lead(userid)
                  over(order by rownum) or userid = lag(userid)
                  over(order by rownum) then
                  1
                 else
                  0
               end flag
          FROM TB1)
 order by lsh;

第二种方法:

t1 as
(select lsh,
         userid,
         lsh - row_number() over(partition by userid order by lsh) rn
    from tmp),
t2 as(    
select userid, rn, min(lsh) new_lsh from t1 group by userid, rn)
select a.lsh,a.userid, b.new_lsh
from t1 a
left join t2 b
on a.userid = b.userid
and a.rn = b.rn
order by 1;

第三种方法:(11g的递归with)

TMP(LSH,USERID,LEV) AS(
  SELECT LSH,USERID,1 LEV FROM TB1 T WHERE ROWNUM<=1
  UNION ALL
  SELECT T1.LSH,T1.USERID,CASE WHEN T1.USERID=T2.USERID THEN LEV ELSE T1.LSH END FROM  TMP T2,TB1 T1 WHERE T1.LSH-T2.LSH=1
)
select * from tmp;

第四种方法:

select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
  from (select t.*,
               decode(userid, lag(userid) over(order by lsh), null, lsh) flag
          from TB1 t) x;




---------------------------

之后要求所有改变
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001'   FROM DUAL
UNION ALL
SELECT 3,'001'   FROM DUAL
UNION ALL
SELECT 4,'002'   FROM DUAL
UNION ALL
SELECT 5,'003'   FROM DUAL
UNION ALL
SELECT 6,'008'   FROM DUAL
UNION ALL
SELECT 7,'003'   FROM DUAL
UNION ALL
SELECT 8,'003'   FROM DUAL
UNION ALL
SELECT 9,'002'   FROM DUAL
UNION ALL
SELECT 10,'002'   FROM DUAL
UNION ALL
SELECT 13,'001'   FROM DUAL 
UNION ALL
SELECT 15,'002'   FROM DUAL
UNION ALL
SELECT 17,'001'   FROM DUAL 
UNION ALL
SELECT 19,'001'   FROM DUAL 
)
select * from tb1;


LSH USERID
1    001
2    001
3    001
4    002
5    003
6    008
7    003
8    003
9    002
10  002
13 001      
15 002     
17 001      
19 001      


--希望得到的结果
LSH USERID NEW_LSH
1   001      1
2   001      1
3   001      1
4   002      4
5   003      5
6   008      6
7   003      7
8   003      7
9   002      9
10 002      9
13 001      13
15 002      15
17 001      17
19 001      17
--希望得到的结果
1)同一个USERID,如果LSH连续(17,19两个序号算连续),如何为其生成相同的NEW_LSH?
2)NEW_LSH不能重复使用
3)不用游标



以上的sql只有第四种方依旧有效


第四种方法:

select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
  from (select t.*,
               decode(userid, lag(userid) over(order by lsh), null, lsh) flag
          from TB1 t) x;



http://www.ngui.cc/el/5557331.html

相关文章

动态sql insert

创建两个表一个是insert语句 一个是需要插入数据的表 create table T ( sql VARCHAR2(200) ); create table TEST ( a1 VARCHAR2(100), a2 VARCHAR2(100), a3 VARCHAR2(100) ); 将insert 语句插入到T表: insert into t (SQL) values (insert into test (a1,a2,a3) values (100…

oracle 日期格式

set time on; 显示时间 set timing on; 显示执行时间 set sqlprompt"_date>" 显示日期和时间 alter session SET NLS_DATE_FORMATYYYY-MM-DD HH24:MI:SS; 修改当前会话日期格式 SQL> set time on; 09:23:06 SQL> select sysdate from dual; SY…

oracle 几个脚本记录

--*****************查看表空间大小***************** SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES…

oracle 索引类型

索引的分类 1二叉树索引或者叫B数索引(B-tree indexes),B树索引是使用最多的一种索引.在默认情况下,我们创建的索引都是B树索引.B树索引基于二叉树原理 2.二叉树聚簇索引(B-tree Cluster indexes) 主要用于聚簇 3.哈希聚簇索引(Hash Cluster indexes) 主要用于哈希(Hash)聚簇…

oracle scope

alter system set parameter value scopespfile; 这里面的scopespfile是什么意思,下面详细解释一下&#xff1a; SCOPE参数值有三个选项。 SCOPEMEMORY:只改变当前实例运行&#xff0c;亦即初始化参数改变了只对当前实例有效&#xff0c;当实例重启之后&#xff0c;初始化参数…

Oracle闪回(FlashBack)归档 数据库

Flashback Database功能非常类似与RMAN的不完全恢复&#xff0c;它可以把整个数据库回退到过去的某个时点的状态&#xff0c;这个功能依赖于Flashback log日志。比RMAN更快速和高效&#xff0c;因此Flashback Database 可以看作是不完全恢复的替代技术。闪回数据库的前提是要开…

oracle 表空间自动扩展大小

select a.FILE_NAME,a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY from dba_data_files a; --AUTOEXTENSIBLE 是否自动扩展 --MAXBYTES 最大 --INCREMENT_BY 自动扩展块数 SQL> show parameter db_block NAME TYPE VALUE ----…

使用微软提供的Office Online实现Office文档的在线查看,编辑等功能

使用微软提供的Office Online平台只需要一个网址即可在线查看Xls,doc,PPT等文档 http://view.officeapps.live.com/op/view.aspx?src要查看的文档地址 详情&#xff1a;http://www.cnblogs.com/huangtailang/p/76492af9d30087d8659d8d5400d20fc7.html pdf预览&#xff08;pdf.…

上传windows上的项目到github

1.注册 github 账户 2.创建repository&#xff08;储藏室&#xff09; 3.然后到repository的管理界面 4.下载git base 地址&#xff1a; https://git-for-windows.github.io/&#xff0c;全部下一步下一步安装 5.用cd命令进入项目所在路径 6.按照官网的提示步骤上传提交文件…

jquery实现简单的图片轮播

第一步css布局 外层层一个固定显示一张图片大小的div&#xff0c;内层一个div放图片&#xff0c;大小根据图片大小决定&#xff0c;如果是左右轮播&#xff0c;设置图片float&#xff1a;left jquery实现 var src$("innerDiv img:first").attr("src"); $…