会python真的可以为所欲为0(回)
350天前
这里还有人吗1(回)
535天前这里还有人吗0(回)
535天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
760天前到处都是羊，不想上班0(回)
840天前
鸽子
0(回)
844天前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; |
by: 发表于：2017-09-28 14:49:14 顶(0) | 踩(0) 回复
？？
回复评论