求sql语句高手请进～

select a.categoryid,a.categoryName,b.folderid,b.folderName
from T_CAL_CATEGORY a,T_CAL_CATEGORY b
where substr(a.categoryid|','|,b.CALENDARCAT)>0
看看ok？

select distinct d.categoryid ,d.categoryName ,c.folderid,c.folderName
from
(select a.categoryid , a.categoryName , a.parentid ,b.folderName ,b.folderid
from T_CAL_CATEGORY a,T_FOLDERS b
where to_char(a.categoryid)=substr(b.CALENDARCAT,1,instr(b.CALENDARCAT,',',1,1))
or instr(b.CALENDARCAT,','||to_char(a.categoryid)||',')>0) c, T_CAL_CATEGORY d
where d.parentid = 0
and c.parentid =d.categoryid
order by d.parentid ,c.folderid如果只有二级的话就应该可以这样写.我是在下张表中的记录中查询是否存在例如',2,'之类的东西,当然也要考虑第一个","前是不是2.试一下.

楼主测试数据里只有2层父子关系，假设存在2层以上的父子关系，使用下面的SQL，当然2层关系也适合，要求oracel9i及以上版本
SQL> select * from T_CAL_CATEGORY;CATEGORYID CATEGORYNAME           PARENTID SEQUENCE
---------- -------------------- ---------- --------------------
        21 篮球                         19 19.21
        22 英超                         20 19.20.22
         1 休闲                          0 1.
         2 户外活动                      1 1.2.
         3 公园                          1 1.3.
        11 出行                          0 11.
        12 旅游                         11 11.12.
        13 交通                         11 11.13.
        19 体育                          0 19.
        20 足球                         19 19.20.10 rows selectedSQL> select * from T_FOLDERS;  FOLDERID FOLDERNAME                                         CALENDARCAT
---------- -------------------------------------------------- --------------------
         1 20001的主日历                                      2,12,
         2 06年世界杯足球赛                                   20,
         3 sdafsdfasdfas                                      2,3,
         4 日历2                                              3,
         5 ggggg                                              2,12,
         6 tttttttt                                           22,6 rows selectedSQL> SELECT DISTINCT substr(id_path, 2, instr(id_path, '/', 2) - 2) categoryid,
  2                  substr(name_path, 2, instr(name_path, '/', 2) - 2) categoryName,
  3                  folderid,
  4                  folderName
  5  FROM   (SELECT t.*,
  6                 sys_connect_by_path(categoryid, '/') || '/' id_path,
  7                 sys_connect_by_path(categoryname, '/') || '/' name_path
  8          FROM   t_cal_category t
  9          START  WITH parentid = 0
10          CONNECT BY PRIOR categoryid = parentid) tt,
11         T_FOLDERS tf
12  WHERE  instr(',' || tf.CALENDARCAT, ',' || tt.categoryid || ',') > 0
13  ORDER  BY categoryid
14  /CATEGORYID CATEGORYNA   FOLDERID FOLDERNAME
---------- ---------- ---------- --------------------------------------------------
1          休闲                1 20001的主日历
1          休闲                3 sdafsdfasdfas
1          休闲                4 日历2
1          休闲                5 ggggg
11         出行                1 20001的主日历
11         出行                5 ggggg
19         体育                2 06年世界杯足球赛
19         体育                6 tttttttt8 rows selected

如果是10G的话，用CONNECT_BY_ROOT方法会简单得多，我这里没环境，就不列举了，有兴趣可以自己查查资料

create table #tab1(
categoryid int,
categoryName varchar(20),
parentid int ,
sequenc varchar(20)
)
create table #tab2(
folderid int ,
folderName varchar(20),
calendarat varchar(20)
)
insert into #tab1
select 1,'休闲',0,'1.' union all
select 2,'户外活动',1,'1.2.' union all
select 3,'公园',1,'1.3.' union all
select 11,'出行',0,'11.' union all
select 12,'旅游',11,'11.12.' union all
select 13,'交通',11,'11.13.' union all
select 19,'体育',0,'19.' union all
select 20,'足球',19,'19.20.'insert into #tab2
select 1,'20001的主日历','2,12,' union all
select 2,'06年世界杯足球赛','20,' union all
select 3,'sdafsdfasdfas','2,3,' union all
select 4,'日历2','3,' union all
select 5,'ggggg','3,12,'
--查询
select aa.categoryid,aa.categoryName,bb.folderid,bb.folderName from (select * from #tab1 where parentid=0)aa
inner join
(
select * from #tab1 a
inner join
#tab2 b on charindex(cast(a.categoryid as varchar(10))+',',b.calendarat)>0
)bb on aa.categoryid=bb.parentid
group by aa.categoryid,aa.categoryName,bb.folderid,bb.folderName--删除
drop table #tab1
drop table #tab2/*
categoryid  categoryName         folderid    folderName
----------- -------------------- ----------- --------------------
1           休闲                   1           20001的主日历
1           休闲                   3           sdafsdfasdfas
1           休闲                   4           日历2
1           休闲                   5           ggggg
11          出行                   1           20001的主日历
11          出行                   5           ggggg
19          体育                   2           06年世界杯足球赛
*/

哦，如果有二级以下的记录的话可以用这个：
select aa.categoryid,aa.categoryName,bb.folderid,bb.folderName from (select * from #tab1 where parentid=0)aa
inner join
(
select * from #tab1 a
inner join
#tab2 b on charindex(cast(a.categoryid as varchar(10))+',',b.calendarat)>0
)bb on aa.categoryid=left(bb.sequenc,charindex('.',bb.sequenc)-1)
group by aa.categoryid,aa.categoryName,bb.folderid,bb.folderName

更正:
where to_char(a.categoryid)=substr(b.CALENDARCAT,1,instr(b.CALENDARCAT,',',1,1))改为:where to_char(a.categoryid)=substr(b.CALENDARCAT,1,instr(b.CALENDARCAT,',',1,1)-1)
或者整个的SQL改为:select distinct d.categoryid ,d.categoryName ,c.folderid,c.folderName
from
(select a.categoryid , a.categoryName , a.parentid ,b.folderName ,b.folderid
from T_CAL_CATEGORY a,T_FOLDERS b
where instr(','||b.CALENDARCAT,','||a.categoryid||',')>0) c, T_CAL_CATEGORY d
where d.parentid = 0
and c.parentid =d.categoryid
order by d.parentid ,c.folderid

调试易

求sql语句高手请进～

解决方案 »

求sql语句 高手请进～

解决方案 »

求sql语句高手请进～