我现在有一个表:
ID CODE
1 C10
2 C10
2 C11
3 C5
3 C10
4 C11想通过一句SQL得到以下结果:
ID COMP
1 C10
2 C10 / C11
3 C5 / C10
4 C11谢谢!!!
ID CODE
1 C10
2 C10
2 C11
3 C5
3 C10
4 C11想通过一句SQL得到以下结果:
ID COMP
1 C10
2 C10 / C11
3 C5 / C10
4 C11谢谢!!!
楼主【tonybao】截止到2008-07-25 09:25:01的历史汇总数据(不包括此帖):
发帖的总数量:36 发帖的总分数:1415 每贴平均分数:39
回帖的总数量:68 得分贴总数量:15 回帖的得分率:22%
结贴的总数量:36 结贴的总分数:1415
无满意结贴数:1 无满意结贴分:100
未结的帖子数:0 未结的总分数:0
结贴的百分比:100.00% 结分的百分比:100.00%
无满意结贴率:2.78 % 无满意结分率:7.07 %
敬礼!
select id, code
from test_csdn c
where c.id = (select max(id) from test_csdn)
or c.id = (select min(id) from test_csdn)
union
select a.id, a.code / b.code cmp
from test_csdn a, test_csdn b
where a.id = b.id
and b.code < a.code
是ORACLE哈,谢谢!
from test_csdn c
where c.id in (select id from test_csdn group by id having count(*) = 1)
union
select t.id, t.code / s.code COMP
from (select rownum anum, id, code from test_csdn) t,
(select rownum bnum, id, code from test_csdn) s
where t.id = s.id
and anum < bnum不好意思,上边的那个sql有点问题,这新的sql的实现的效果是,
union前部分把所有只有一条记录的结果查出来,后部分是处理每个ID有2条记录的情况,实现C10 / C11但是不清楚搂主的纪录究竟怎样,一个ID只有(1 or 2)条记录,还是还会出现如 C10,C11,C12。。的情况上边的sql实现了 一个ID只有(1 or 2)条记录 的情形
CREATE FUNCTION mg(@Group varchar(255))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+'/'+rtrim(code) FROM the_table WHERE id=@Group RETURN(substring(@r,2,8000))
END然后写查询语句:
select t1.id,dbo.mg(t1.id) from the_table t1 group by t1.id
谢谢!这个同ID的记录是不限定的,可能还会有C11 C12
感觉这样子不是一个好办法,不过给我了很大的启迪,谢谢!
我是新人,有哪位高手能帮我把它改成ORACLE的吗?
是不是如果存在C10,C11,C12...的时候,结果应该是C10/C11/C12...
1. C10/C11只是一个分隔符而已。
2. 是不是如果存在C10,C11,C12...的时候,结果应该是C10/C11/C12... 对,要显示:C10/C11/C12
r varchar(8000) := '';
tmp varchar(100) := '';
cursor cur_code is
select m.code from test_csdn m where m.id = v_id;
begin
open cur_code;
loop
fetch cur_code
into tmp;
exit when cur_code%notfound;
r := r || '/' ||tmp;
end loop;
RETURN(substr(r, 2));
end;
select t1.id, mg(t1.id) from test_csdn t1 group by t1.id
测试通过
ERROR 位于第7行:
ORA-06550: line 7, column 12:
PLS-00103: Encountered the symbol "|" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
MYEVI你可以把你的测试过程贴给我吗? 我出现了“警告:已创建的函数出现编译错误。” 谢谢!!!
r varchar(8000) := '';
tmp varchar(100) := '';
cursor cur_code is
select m.code from test_csdn m where m.id = v_id;
begin
open cur_code;
loop
fetch cur_code
into tmp;
exit when cur_code%notfound;
r := r || '/' ||tmp;
end loop;
RETURN(substr(r, 2));
end;
谁用一个sql写出来,才叫厉害。
/**oracle**/
select id,rcode from (
select x.id id , instr(x.rcode,'/',1,1) pp , num ,x.rcode rcode from (
select a.id id ,a.code code , count(id) num ,concat('',case when a.code = b.code then a.code when a.code != b.code then concat(concat(a.code,'/'),b.code) end ) rcode
from table1 a, table1 b where a.id=b.id group by a.id, a.code
) x) where (pp = 0 and num = 1 )or pp > 0/**hsqldb**/
select id,rcode from (
select x.id id , position('/' in x.rcode) pp , num ,x.rcode rcode from (
select a.id id ,a.code code , count(id) num ,concat('',case when a.code = b.code then a.code when a.code != b.code then concat(concat(a.code,'/'),b.code) end ) rcode
from table1 a, table1 b where a.id=b.id group by a.id, a.code
) x) where (pp = 0 and num = 1 )or pp > 0