用户注册



邮箱:

密码:

用户登录


邮箱:

密码:
记住登录一个月忘记密码?

发表随想


还能输入:200字
云代码 - sql代码库

多行合一 orcale

2017-09-17 作者:举报

[sql]代码库

CREATE TABLE dept(DEPTID int,ENAME varchar(20 BYTE),SEX varchar(20 BYTE),AGE varchar(20 BYTE));
insert into dept values(1,'张三','F','18'); 
insert into dept values(1,'李四','F','19'); 
insert into dept values(1,'王五','F','20'); 
insert into dept values(2,'刘六','M','21'); 
insert into dept values(2,'赵七','M','22'); 
insert into dept values(2,'曹九','M','23');

commit;
select * from dept;
SELECT DISTINCT FIRST_VALUE(deptid) OVER(PARTITION BY deptid ORDER BY lv DESC) AS deptid,
FIRST_VALUE(ename) OVER(PARTITION BY deptid ORDER BY lv DESC) AS ename
FROM (SELECT deptid, SYS_CONNECT_BY_PATH(ename, ' ') ename, LEVEL lv
FROM (SELECT deptid,ename,LAG(ename, 1, NULL) OVER(PARTITION BY deptid ORDER BY ename) ename_1
FROM (SELECT deptid, ename FROM dept))
CONNECT BY PRIOR ename = ename_1
ORDER BY deptid, ename);

SELECT DISTINCT FIRST_VALUE (deptid) OVER (PARTITION BY deptid ORDER BY lv DESC) AS deptid, 
FIRST_VALUE (sex) OVER (PARTITION BY sex ORDER BY lv DESC) AS sex, 
FIRST_VALUE (ename) OVER (PARTITION BY deptid ORDER BY lv DESC) AS ename 
FROM (SELECT deptid, sex, SYS_CONNECT_BY_PATH (ename, ' ') ename, LEVEL lv 
FROM (SELECT deptid, ename, sex, 
LAG (ename, 1, NULL) OVER (PARTITION BY deptid ORDER BY ename) ename_1 
FROM (SELECT deptid, ename, sex FROM dept)) 
CONNECT BY PRIOR ename = ename_1 
ORDER BY deptid) ;

SELECT deptid,SYS_CONNECT_BY_PATH(ename, ' ') AS ename,sex,SYS_CONNECT_BY_PATH(age, ' ') AS age
FROM (SELECT deptid,ename,sex,age,
RANK() OVER(ORDER BY deptid)+ROW_NUMBER() OVER(ORDER BY deptid) rn,
ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY deptid) rm
FROM dept) a1
WHERE a1.ROWID IN (SELECT MAX(a2.ROWID) FROM dept a2 WHERE a2.deptid = a1.deptid)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1;


分享到:
更多

网友评论    (发表评论)

共1 条评论 1/1页

发表评论:

评论须知:

  • 1、评论每次加2分,每天上限为30;
  • 2、请文明用语,共同创建干净的技术交流环境;
  • 3、若被发现提交非法信息,评论将会被删除,并且给予扣分处理,严重者给予封号处理;
  • 4、请勿发布广告信息或其他无关评论,否则将会删除评论并扣分,严重者给予封号处理。