各位大哥,小弟刚开始学习数据库,最近在学习的时候遇到个数据库SQL的问题,不知道该怎么写这句SQL,想上来向各位请教一下,先说一下问题:
有一张表 假设表名叫(table1),其中列和数据如下:name | title | parent|
-----------------------
A 一级 --
B 二级 A
C 二级 A
D 一级 --
E 三级 B
F 三级 C
G 二级 D
H 三级 B
现在要查询出如下样式的结果,不知道该怎么写SQL
-------------------------------------------
A B E
A B H
A C F
D G ---------------------------------------------
不知道各位能看明白我的意思么,昨天问了一下人,他们说要用到什么递归,
我还是不大明白,希望各位能够帮忙看一下,顺便给点意见,谢谢.
还有我是用的sybase数据库.
有一张表 假设表名叫(table1),其中列和数据如下:name | title | parent|
-----------------------
A 一级 --
B 二级 A
C 二级 A
D 一级 --
E 三级 B
F 三级 C
G 二级 D
H 三级 B
现在要查询出如下样式的结果,不知道该怎么写SQL
-------------------------------------------
A B E
A B H
A C F
D G ---------------------------------------------
不知道各位能看明白我的意思么,昨天问了一下人,他们说要用到什么递归,
我还是不大明白,希望各位能够帮忙看一下,顺便给点意见,谢谢.
还有我是用的sybase数据库.
我竟然看懂了...select substr(max(sys_connect_by_path(name,' ')),2)
from table1
start with title='一级'
connect by prior name=parent
就是查询出:一级标题对应的所有子标题并依次列出来
比如一级标题A 他对应有二级标题B和C, 然后B对应有三级标题E,C对应有三级标题F
一级标题D对应有二级标题G,并且G没有三级标题了
那么查询数据中就是
A B E
A C F
D G -
(select name,parent from
(select name from table1 where title='一级' group by name) as t1
left join
(select name,parent from table1 as t where title='二级' group by name,parent) as t2
on t1.name =t2.name group by name,parent ) as t12)
left join
(select name,parent from table1 as t where title='三级' group by name,parent) as t3
on t12.parent = t3.parent group by name,parent
A B H
A C F
D G --楼主,这个是个什么意思??
(a VARCHAR2(10),
b VARCHAR2(10),
c VARCHAR2(10)
);
-- A 一级
-- B 二级 A
-- C 二级 A
-- D 一级
-- E 三级 B
-- F 三级 C
-- G 二级 D
-- H 三级 B SELECT d FROM (
SELECT t.*,SYS_CONNECT_BY_PATH(t.a,' ') d,CONNECT_BY_ISLEAF e FROM test t START WITH t.c IS NULL CONNECT BY PRIOR t.a=t.c )
WHERE e=1
第一级节点没有父节点,那么他的parent的值就是null,其他的二三级节点就一次类推;(其中有些节点只有二层,就是说没有三级节点)其实表中用到的字段就2个,一个是name ,一个是parent,且name和parent中数据是有关联关系的,name | parent
-------------------------
parm null --->这个是个一级节点
commonParm parm --->这个是个二级节点
coinParm commonParm --->这个是个三级节点
rateParm commonParm
bankParm commonParm
report null
singleFundReport report
manyFundReport report
thing singleFundRepor
attemper singleFundRepor
balanceTab manyFundReport
balanceChange manyFundReport
trade null
businessbalance trade
workDateparm trade
--------------------------------
那个根据上面的数据查询出来的结果就是
列一 | 列二 | 列三
---------------------------------
para commonParm coinParm
para commonParm rateParm
para commonParm bankParm
report singleFundReport thing
report singleFundReport attemper
report manyFundReport balanceTab
report manyFundReport balanceChange
trade businessbalance
trade workDateparm
WITH T AS
(SELECT 'A' NAME, '一级' TITLE, NULL PARENT
FROM DUAL
UNION ALL
SELECT 'B', '二级', 'A'
FROM DUAL
UNION ALL
SELECT 'C', '二级', 'A'
FROM DUAL
UNION ALL
SELECT 'D', '一级', NULL
FROM DUAL
UNION ALL
SELECT 'E', '三级', 'B'
FROM DUAL
UNION ALL
SELECT 'F', '三级', 'C'
FROM DUAL
UNION ALL
SELECT 'G', '三级', 'D'
FROM DUAL
UNION ALL
SELECT 'H', '三级', 'B' FROM DUAL)SELECT T1.NAME,T2.NAME,T3.NAME
FROM T T1
LEFT JOIN T T2
ON T1.NAME = T2.PARENT
LEFT JOIN T T3
ON T2.NAME = T3.PARENT
WHERE T1.TITLE='一级'
ORDER BY T1.NAME,T1.TITLE, T2.TITLE, T3.TITLE
WITH T AS
(SELECT 'A' NAME, '一级' TITLE, NULL PARENT
FROM DUAL
UNION ALL
SELECT 'B', '二级', 'A'
FROM DUAL
UNION ALL
SELECT 'C', '二级', 'A'
FROM DUAL
UNION ALL
SELECT 'D', '一级', NULL
FROM DUAL
UNION ALL
SELECT 'E', '三级', 'B'
FROM DUAL
UNION ALL
SELECT 'F', '三级', 'C'
FROM DUAL
UNION ALL
SELECT 'G', '三级', 'D'
FROM DUAL
UNION ALL
SELECT 'H', '三级', 'B' FROM DUAL)SELECT T1.NAME,T2.NAME,T3.NAME
FROM T T1
LEFT JOIN T T2
ON T1.NAME = T2.PARENT
LEFT JOIN T T3
ON T2.NAME = T3.PARENT
WHERE T1.TITLE='一级'
ORDER BY T1.NAME,T1.TITLE,T2.NAME, T2.TITLE, T3.TITLE
with
temp1 as (select name from table1 where parent='null'),
temp2 as (select a.parent,a.name from table1 a,temp1 b where a.parent=b.name)
select b.parent as 列1,b.name as 列2,a.name as 列3 from table1 a,temp2 b where a.parent=b.name
create table table1(name varchar(10),title varchar(10),parent varchar(10))insert into table1 values ('A','一级','')
insert into table1 values ('B','二级','A')
insert into table1 values ('C','二级','A')
insert into table1 values ('D','一级','')
insert into table1 values ('E','三级','B')
insert into table1 values ('F','三级','C')
insert into table1 values ('G','二级','D')
insert into table1 values ('H','三级','B')select * into t1 from table1 where title='一级'
select * into t2 from table1 where title='二级'
select * into t3 from table1 where title='三级'select a.name,b.name,c.name from t1 a
left join t2 b where a.name=b.parent
left join t3 c where b.name=c.parent
create table #table1(name varchar(10),title varchar(10),parent varchar(10))insert into #table1 values ('A','一级','')
insert into #table1 values ('B','二级','A')
insert into #table1 values ('C','二级','A')
insert into #table1 values ('D','一级','')
insert into #table1 values ('E','三级','B')
insert into #table1 values ('F','三级','C')
insert into #table1 values ('G','二级','D')
insert into #table1 values ('H','三级','B')select * from #table1select * into #t1 from #table1 where title='一级'
select * into #t2 from #table1 where title='二级'
select * into #t3 from #table1 where title='三级'
select a.name,b.name,c.name from #t1 a
left join #t2 b on a.name=b.parent
left join #t3 c on b.name=c.parent
select a.name,b.name,c.name
from #table1 a
left join #table1 b on a.name=b.parent and b.title='二级'
left join #table1 c on b.name=c.parent and c.title='三级'
where a.title='一级'
from table1 a,table1 b,table1 c
where a.name=b.parent(+) and b.name=c.parent(+)
and a.parent is null;
接分啊