各位大哥大姐大虾们,救命啊!
一条sql语句不会写啊!烦躁的要命,要跳楼了!
结果集如下:select code,grade from tablename;CODE GRADE
------- ------
01 8
0101
02 3
0201
0202 5
020201 7
020202
0203 2 现在需要写一条sql,查询下面这样的结果:CODE GRADE
------- ------
01 8
0101 8
02 3
0201 3
0202 5
020201 7
020202 5
0203 2 要求就是这样的:
code由偶数位数字组成,没两位表示一个等级。
如果本等级grade为空,则取上一等级的grade,上一等级还为空,则依次类推到顶级,如果顶级为空则为空。各位帮帮忙啊!
在线等待,来者均有分!
一条sql语句不会写啊!烦躁的要命,要跳楼了!
结果集如下:select code,grade from tablename;CODE GRADE
------- ------
01 8
0101
02 3
0201
0202 5
020201 7
020202
0203 2 现在需要写一条sql,查询下面这样的结果:CODE GRADE
------- ------
01 8
0101 8
02 3
0201 3
0202 5
020201 7
020202 5
0203 2 要求就是这样的:
code由偶数位数字组成,没两位表示一个等级。
如果本等级grade为空,则取上一等级的grade,上一等级还为空,则依次类推到顶级,如果顶级为空则为空。各位帮帮忙啊!
在线等待,来者均有分!
nvl(t.grade,(select a.grade from tablename a where a.code=substr(t.code,1,length(t.code)-2)))
from tablename t
CODE NVL(T.GRADE,(SELECTA.GRADEFROM
01 8
0101 8
02 3
0201 3
0202 5
020201 7
020202 5
0203 2
感谢回答!
不过,你的sql只是满足本等级为空,取上一等级的grade,但是如果上一等级grade也为空且不是顶级,就不能满足条件了
以此类推,判断为空继续往上查询呗
可是等级数是不确定的,正式上线会出现很多等级,我如何来写这样的一条sql语句?
CODE GRADE
01 8
0101
02 3
0201
0202 5
020201 7
020202
02020201
0203 2 SELECT *
FROM t_aaa
WHERE grade IS NOT NULL
UNION
SELECT d.code, e.grade
FROM t_aaa d,
(SELECT b.code, MAX(c.grade) grade --,c.len
FROM t_aaa b, (SELECT a.*, length(a.code) len FROM t_aaa a WHERE a.grade IS NOT NULL) c
WHERE b.grade IS NULL
AND substr(b.code, 1, 2) = substr(c.code, 1, 2)
AND length(b.code) > len
AND b.code LIKE c.code || '%'
GROUP BY b.code) e
WHERE d.grade IS NULL
AND d.code = e.code;CODE GRADE
01 8
0101 8
02 3
0201 3
0202 5
020201 7
020202 5
02020201 5
0203 2
取max(grade)多少有点问题如果有权限建自定义函数的话,还是用递归函数好了create or replace function getGrade(pc VARCHAR2) return INT is
Result INT;
n INT;
begin
IF length(pc)>=4 THEN
SELECT COUNT(*) INTO n FROM t2 WHERE code=substr(pc,1,length(pc)-2);
IF n=1 THEN
select grade INTO RESULT from t2 a where code=substr(pc,1,length(pc)-2);
END IF;
ELSE
RETURN(NULL);
END IF;
IF RESULT IS NULL THEN
result:=getGrade(substr(pc,1,length(pc)-2));
END IF;
return(Result);
end getGrade;
SQL> SELECT CODE,nvl(grade,getgrade(CODE)),grade FROM t2;
CODE NVL(GRADE,GETGRADE(CODE)) GRADE
-------------------- ------------------------- ---------------------------------------
01 8 8
0101 8
02 3 3
0201 3
0202 5 5
020201 7 7
020202 5
02020201 5
0203 2 2
02020201010101 5
10 rows selected
B C
2 01 8
3 0101
4 02 3
5 0201
6 0202 5
7 020201 7
8 020202
9 0203 2
1 020301 with t as (
select tt.*
from (select a.*, b.b as b2, length(a.b) as len
from (select * from a where c is not null) a
left join (select * from a where c is null) b on instr(b.b,
a.b) = 1
where length(a.b) <> length(b.b)) tt,
(select b2, max(len) as len
from (select a.*, b.b as b2, length(a.b) as len
from (select * from a where c is not null) a
left join (select * from a where c is null) b on instr(b.b,
a.b) = 1
where length(a.b) <> length(b.b))
group by b2) tt2
where tt.b2 = tt2.b2
and tt.len = tt2.len)
select distinct a.b,nvl(a.c,(select c from t where a.b=t.b2)) from t,a ; B C
7 01 8
8 0101 8
2 02 3
9 0201 3
1 0202 5
6 020201 7
3 020202 5
4 0203 2
5 020301 2
太复杂 期待高手
FROM t_aaa
WHERE grade IS NOT NULL
UNION
SELECT d.code, e.grade
FROM t_aaa d,
(SELECT b.code, c.grade, c.len
FROM t_aaa b, (SELECT a.*, length(a.code) len FROM t_aaa a WHERE a.grade IS NOT NULL) c
WHERE b.grade IS NULL
AND substr(b.code, 1, 2) = substr(c.code, 1, 2)
AND length(b.code) > len
AND b.code LIKE c.code || '%'
) e
WHERE d.grade IS NULL
AND d.code = e.code
AND (e.code, e.len) IN
(SELECT b.code, MAX(c.len)
FROM t_aaa b, (SELECT a.*, length(a.code) len FROM t_aaa a WHERE a.grade IS NOT NULL) c
WHERE b.grade IS NULL
AND substr(b.code, 1, 2) = substr(c.code, 1, 2)
AND length(b.code) > len
AND b.code LIKE c.code || '%'
GROUP BY b.code)CODE GRADE
01 8
0101 8
02 3
0201 3
0202 5
020201 7
020202 5
02020201 5
0203 2
其实问题不是很大,解决办法也很多,只是早上没睡醒,上班有点烦躁,没静下心来好好想想。
为什么非要写一条sql语句呢?写一个函数不比写一条sql简单多了吗?
哎,心静自然凉,呵呵
再次感谢各位!