由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - 问个sql问题
相关主题
recursive CTE ...说说俺们这以前一个老印的code,滥用CTE
新手问个简单的SELECT问题怎么reference temp table的column
To get the 2nd, 3rd, 4th largest value最近写了不少SQL script,请大牛评价下属于什么水平
再现急求答案,多谢。list duplicators in one table
求教一个数据库面试题,多对多关系表设计 (转载)Re: recursive sql?
问一道sql的面试题啊 自己实在是没想出来请教一个问题
请问那种很复杂的sql语句,有什么套路吗?webbew SQL问题解法2 -- SQL 利器Recursive CTE
其实我发现了CODE的写得好不好recursive CTE巧解费波纳茨数列
相关话题的讨论汇总
话题: manager话题: cte话题: table话题: employees话题: level
进入Database版参与讨论
1 (共1页)
g*****r
发帖数: 130
1
这个面试题是job板上一个同学推荐的。有人能帮忙回答一下吗?
谢谢!
There is an EMPLOYEES(ID, NAME, MANAGER_ID) table. MANAGER_ID points to the
ID column of the same table. Given a manager name (assuming ID is unique and
NAME is unique), what kind of SQL query would you write to retrieve all
employees directly managed by this manager? What is the query to get
employees 2 levels down? Is there a way to write a query to get employees N
levels down? Is there another way of organising this table (different
columns?) to hold these kinds of tree structures?
i*****w
发帖数: 75
2
USE CTE (Common Table Expression) to retrieve until it's exhausted (parent -
> Child relationship).

the
and
N

【在 g*****r 的大作中提到】
: 这个面试题是job板上一个同学推荐的。有人能帮忙回答一下吗?
: 谢谢!
: There is an EMPLOYEES(ID, NAME, MANAGER_ID) table. MANAGER_ID points to the
: ID column of the same table. Given a manager name (assuming ID is unique and
: NAME is unique), what kind of SQL query would you write to retrieve all
: employees directly managed by this manager? What is the query to get
: employees 2 levels down? Is there a way to write a query to get employees N
: levels down? Is there another way of organising this table (different
: columns?) to hold these kinds of tree structures?

g***l
发帖数: 18555
3
应该MAINTAIN一个JUNCTION TABLE
MANAGER_EMPLOYEE (MANAGER_ID, EMPLOYEE_ID, LEVEL_ID,DEPARTMENT_ID)
这样就可以一个LEVEL一个LEVEL的往下走,每一LEVEL JOIN一次
g***l
发帖数: 18555
4
为什么要JUNCTION TABLE?因为MANAGER换了,总不可能把底下所有的EMPLOYEE的
RECORD都UPDATE一遍吧?UPDATE JUNCTION TABLE就可以了.这是3RD NORMAL FORM了。
有LEVEL,也知道哪一层。否则从哪个人开始找呢,随便拉出个EMPLOYEE开始,他在第
几层是不知道的,必须从CEO开始找起。这个JUNCTION TABLE基本上就MAITAIN了公司的
MANAGEMENT TREE
N*******m
发帖数: 517
5
SQL Server和DB2里有Recursive Queries,
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.90).aspx
Oracle里面好像有专门的SQL语句可以用。
i****a
发帖数: 36252
6
我很煩這種 table. 我公司承包軟體給一印度公司, 有這樣一個的self reference資料
庫表, 每次找資料都煩死.
要麼看 SP example 寫 recursive query, 要麼 manually run multiple selects or
use temp tables.
題外話:
存這種資料, MS SQL 2008 有 hierarchy data type, Oracle 好象也有

the
and
N

【在 g*****r 的大作中提到】
: 这个面试题是job板上一个同学推荐的。有人能帮忙回答一下吗?
: 谢谢!
: There is an EMPLOYEES(ID, NAME, MANAGER_ID) table. MANAGER_ID points to the
: ID column of the same table. Given a manager name (assuming ID is unique and
: NAME is unique), what kind of SQL query would you write to retrieve all
: employees directly managed by this manager? What is the query to get
: employees 2 levels down? Is there a way to write a query to get employees N
: levels down? Is there another way of organising this table (different
: columns?) to hold these kinds of tree structures?

g***l
发帖数: 18555
7
关系型数据库不是用来存储等级型数据的,如果有的话,必须单独一个TABLE存储等级关系
,否则每次都要CREATE TREE,是REDUNDANT的。
y****w
发帖数: 3747
8
oracle用connet by. 以前的CTE就是摆设,只有到11r2还是多少才完善起来。

【在 N*******m 的大作中提到】
: SQL Server和DB2里有Recursive Queries,
: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.90).aspx
: Oracle里面好像有专门的SQL语句可以用。

y****w
发帖数: 3747
9
别烦,recursive CTE是必须要掌握的冬冬之一。

or

【在 i****a 的大作中提到】
: 我很煩這種 table. 我公司承包軟體給一印度公司, 有這樣一個的self reference資料
: 庫表, 每次找資料都煩死.
: 要麼看 SP example 寫 recursive query, 要麼 manually run multiple selects or
: use temp tables.
: 題外話:
: 存這種資料, MS SQL 2008 有 hierarchy data type, Oracle 好象也有
:
: the
: and
: N

y****w
发帖数: 3747
10

the
and
N
with tt(id, nm, lvl)
as
(
select id, name, 0 from EMPLOYEES where name = 'xxoo'
union all
select id, name, lvl +1 from tt, EMPLOYEES e where tt.MANAGER_ID = tt.id)
select * from tt where lvl = $LEVEL

【在 g*****r 的大作中提到】
: 这个面试题是job板上一个同学推荐的。有人能帮忙回答一下吗?
: 谢谢!
: There is an EMPLOYEES(ID, NAME, MANAGER_ID) table. MANAGER_ID points to the
: ID column of the same table. Given a manager name (assuming ID is unique and
: NAME is unique), what kind of SQL query would you write to retrieve all
: employees directly managed by this manager? What is the query to get
: employees 2 levels down? Is there a way to write a query to get employees N
: levels down? Is there another way of organising this table (different
: columns?) to hold these kinds of tree structures?

相关主题
问一道sql的面试题啊 自己实在是没想出来说说俺们这以前一个老印的code,滥用CTE
请问那种很复杂的sql语句,有什么套路吗?怎么reference temp table的column
其实我发现了CODE的写得好不好最近写了不少SQL script,请大牛评价下属于什么水平
进入Database版参与讨论
y****w
发帖数: 3747
11
假设某一级mgr被外星人干掉了,有人让位了大家高高兴兴的都升一级, 然后请你老人家来维护level,假设原先这家伙有99级下属~
3NF和tree结构有啥关系。在关系型数据库里面表达tree结构最简单的就是这样的tree表,怕冗余把非id列3NF出去--这可能就是原先那个面试题的最后一问的初衷。
即使特定dbms能提供某些更酷的结构来做这个,但本质怎么也不会变的。
而且,你这里是manager-employee关系,可能还好,但有些关系里面子节点可是有不止一个parent的,想象一下access plan,有些节点是可以同时处于不同level的。
你的junction table只是稍稍扩展了一下tree而已,特定应用可能方便些,丢了很多灵活性,外加更新更困难了。

【在 g***l 的大作中提到】
: 应该MAINTAIN一个JUNCTION TABLE
: MANAGER_EMPLOYEE (MANAGER_ID, EMPLOYEE_ID, LEVEL_ID,DEPARTMENT_ID)
: 这样就可以一个LEVEL一个LEVEL的往下走,每一LEVEL JOIN一次

g***l
发帖数: 18555
12
LEVEL更新了,当然要UPDATE数据了,要不谁知道你升级了?MANAGER死了,才不会去更
新别的EMPLOYEE呢,只有升级了的EMPLOYEE才会有人管。

人家来维护level,假设原先这家伙有99级下属~
tree表,怕冗余把非id列3NF出去--这可能就是原先那个面试题的最后一问的初衷。
止一个parent的,想象一下access plan,有些节点是可以同时处于不同level的。
灵活性,外加更新更困难了。

【在 y****w 的大作中提到】
: 假设某一级mgr被外星人干掉了,有人让位了大家高高兴兴的都升一级, 然后请你老人家来维护level,假设原先这家伙有99级下属~
: 3NF和tree结构有啥关系。在关系型数据库里面表达tree结构最简单的就是这样的tree表,怕冗余把非id列3NF出去--这可能就是原先那个面试题的最后一问的初衷。
: 即使特定dbms能提供某些更酷的结构来做这个,但本质怎么也不会变的。
: 而且,你这里是manager-employee关系,可能还好,但有些关系里面子节点可是有不止一个parent的,想象一下access plan,有些节点是可以同时处于不同level的。
: 你的junction table只是稍稍扩展了一下tree而已,特定应用可能方便些,丢了很多灵活性,外加更新更困难了。

y****w
发帖数: 3747
13
人家原先那个还不用更新这个level. 你引入这个所谓junction table只是把复杂性转
移到update去了,本质上根本没解决问题。另外,你怎么解决某节点可以属于不同
level的问题?

【在 g***l 的大作中提到】
: LEVEL更新了,当然要UPDATE数据了,要不谁知道你升级了?MANAGER死了,才不会去更
: 新别的EMPLOYEE呢,只有升级了的EMPLOYEE才会有人管。
:
: 人家来维护level,假设原先这家伙有99级下属~
: tree表,怕冗余把非id列3NF出去--这可能就是原先那个面试题的最后一问的初衷。
: 止一个parent的,想象一下access plan,有些节点是可以同时处于不同level的。
: 灵活性,外加更新更困难了。

g***l
发帖数: 18555
14
一个人既当CEO,又当CLERK不大可能吧,我又当MANAGER,还在ACCOUNTING当ACCOUNTANT,一个人俩老板?那就是MANY TO MANY的关系,TREE就乱套了,必须用我的JUNCTION TABLE,只用那个单一TABLE里MANAGER填MULTIPLE VALUES?

【在 y****w 的大作中提到】
: 人家原先那个还不用更新这个level. 你引入这个所谓junction table只是把复杂性转
: 移到update去了,本质上根本没解决问题。另外,你怎么解决某节点可以属于不同
: level的问题?

B*****g
发帖数: 34098
15
I use CTE in oracle 9i/10g/11g, never have any problem.
But most important thing to remember is oracle use CBO, when you use CTE
does not mean oracle will run the CTE part first, oracle may still choose
other execution plan.

【在 y****w 的大作中提到】
: oracle用connet by. 以前的CTE就是摆设,只有到11r2还是多少才完善起来。
B*****g
发帖数: 34098
16
http://psoug.org/reference/connectby.html
SELECT *
FROM EMPLOYEES
WHERE LEVEL <= ?
START WITH ID = ?
CONNECT BY PRIOR id = manager_id
There are other ways to hold these tree structure data, such as create a
relationship table.

the
and
N

【在 g*****r 的大作中提到】
: 这个面试题是job板上一个同学推荐的。有人能帮忙回答一下吗?
: 谢谢!
: There is an EMPLOYEES(ID, NAME, MANAGER_ID) table. MANAGER_ID points to the
: ID column of the same table. Given a manager name (assuming ID is unique and
: NAME is unique), what kind of SQL query would you write to retrieve all
: employees directly managed by this manager? What is the query to get
: employees 2 levels down? Is there a way to write a query to get employees N
: levels down? Is there another way of organising this table (different
: columns?) to hold these kinds of tree structures?

y****w
发帖数: 3747
17
I mean
recursice cte
earlier version cte is no much more than subquery.
correct me if my memory is wrong

【在 B*****g 的大作中提到】
: I use CTE in oracle 9i/10g/11g, never have any problem.
: But most important thing to remember is oracle use CBO, when you use CTE
: does not mean oracle will run the CTE part first, oracle may still choose
: other execution plan.

B*****g
发帖数: 34098
18
赫赫,你说的是CTE,不是recursive cte。
Post from asktom
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_
And recursive ct is available from 11g r2, that means for this part, sql server/db2 can be convert to oracle, but can not convert oracle to others, hehe

【在 y****w 的大作中提到】
: I mean
: recursice cte
: earlier version cte is no much more than subquery.
: correct me if my memory is wrong

y****w
发帖数: 3747
19
"以前的CTE就是摆设,只有到11r2还是多少才完善起来"
俺没说以前没有CTE阿,好吧,没强调recursive,虽然一般说起cte俺suppose就是recursive的,不然和subquery除了写法没啥区别。
oracle community还是习惯于connect by,但是从其他数据库过来的会更喜欢recursive CTE。

server/db2 can be convert to oracle, but can not convert oracle to others,
hehe

【在 B*****g 的大作中提到】
: 赫赫,你说的是CTE,不是recursive cte。
: Post from asktom
: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_
: And recursive ct is available from 11g r2, that means for this part, sql server/db2 can be convert to oracle, but can not convert oracle to others, hehe

B*****g
发帖数: 34098
20
一个sub用10次,用起来还是很方便的。而且execution plan也比较好控制。

recursive的,不然和subquery除了写法没啥区别。
recursive CTE。

【在 y****w 的大作中提到】
: "以前的CTE就是摆设,只有到11r2还是多少才完善起来"
: 俺没说以前没有CTE阿,好吧,没强调recursive,虽然一般说起cte俺suppose就是recursive的,不然和subquery除了写法没啥区别。
: oracle community还是习惯于connect by,但是从其他数据库过来的会更喜欢recursive CTE。
:
: server/db2 can be convert to oracle, but can not convert oracle to others,
: hehe

y****w
发帖数: 3747
21
y, good point.

【在 B*****g 的大作中提到】
: 一个sub用10次,用起来还是很方便的。而且execution plan也比较好控制。
:
: recursive的,不然和subquery除了写法没啥区别。
: recursive CTE。

1 (共1页)
进入Database版参与讨论
相关主题
recursive CTE巧解费波纳茨数列求教一个数据库面试题,多对多关系表设计 (转载)
求思路:如何保存/更新CTE recursive query结果问一道sql的面试题啊 自己实在是没想出来
我经常问的几道SQL SERVER DBA的面试题,图省事不问编程请问那种很复杂的sql语句,有什么套路吗?
数据库面试题其实我发现了CODE的写得好不好
recursive CTE ...说说俺们这以前一个老印的code,滥用CTE
新手问个简单的SELECT问题怎么reference temp table的column
To get the 2nd, 3rd, 4th largest value最近写了不少SQL script,请大牛评价下属于什么水平
再现急求答案,多谢。list duplicators in one table
相关话题的讨论汇总
话题: manager话题: cte话题: table话题: employees话题: level