1 参考树型表的查找ID PID A B C aaa 1 2 2 bsd aaa 34 45 465 tew bsd 5 6 6 rty tew 567 435 4 gtt rty 34 67 7 a 36 7 7 b a 546 67 7 c b 45 67 4PID为父结点ID,要求输入ID='bsd'求出所有的父结点和子结点。 ID PID A B C aaa 1 2 2 bsd aaa 34 45 465 tew bsd 5 6 6 rty tew 567 435 4 gtt rty 34 67 7Answer: declare @temp table (id char(8))insert @temp values ('bsd')while exists ( select id from tempTab where pid in (select id from @temp) and id not in (select id from @temp) ) insert @temp select id from tempTab where pid in (select id from @temp) and id not in (select id from @temp) ---把所有子节点插入@tempwhile exists ( select pid from tempTab where id in (select id from @temp) and pid not in (select id from @temp) ) insert @temp select pid from tempTab where id in (select id from @temp) and pid not in (select id from @temp) ---把所有父接点插入@tempselect * from tempTab where id in (select id from @temp)2 建立公司部门关联表 公司id 部门id 3 int
1 参考树型表的查找ID PID A B C aaa 1 2 2 bsd aaa 34 45 465 tew bsd 5 6 6 rty tew 567 435 4 gtt rty 34 67 7 a 36 7 7 b a 546 67 7 c b 45 67 4PID为父结点ID,要求输入ID='bsd'求出所有的父结点和子结点。 ID PID A B C aaa 1 2 2 bsd aaa 34 45 465 tew bsd 5 6 6 rty tew 567 435 4 gtt rty 34 67 7Answer: declare @temp table (id char(8))insert @temp values ('bsd')while exists ( select id from tempTab where pid in (select id from @temp) and id not in (select id from @temp) ) insert @temp select id from tempTab where pid in (select id from @temp) and id not in (select id from @temp) ---把所有子节点插入@tempwhile exists ( select pid from tempTab where id in (select id from @temp) and pid not in (select id from @temp) ) insert @temp select pid from tempTab where id in (select id from @temp) and pid not in (select id from @temp) ---把所有父接点插入@tempselect * from tempTab where id in (select id from @temp)2 建立公司部门关联表 公司id 部门id 3 int
aaa 1 2 2
bsd aaa 34 45 465
tew bsd 5 6 6
rty tew 567 435 4
gtt rty 34 67 7
a 36 7 7
b a 546 67 7
c b 45 67 4PID为父结点ID,要求输入ID='bsd'求出所有的父结点和子结点。
ID PID A B C
aaa 1 2 2
bsd aaa 34 45 465
tew bsd 5 6 6
rty tew 567 435 4
gtt rty 34 67 7Answer:
declare @temp table (id char(8))insert @temp values ('bsd')while exists (
select id from tempTab
where pid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from tempTab
where pid in (select id from @temp)
and id not in (select id from @temp)
---把所有子节点插入@tempwhile exists (
select pid from tempTab
where id in (select id from @temp)
and pid not in (select id from @temp)
)
insert @temp
select pid from tempTab
where id in (select id from @temp)
and pid not in (select id from @temp)
---把所有父接点插入@tempselect * from tempTab
where id in (select id from @temp)2 建立公司部门关联表 公司id 部门id
3 int
aaa 1 2 2
bsd aaa 34 45 465
tew bsd 5 6 6
rty tew 567 435 4
gtt rty 34 67 7
a 36 7 7
b a 546 67 7
c b 45 67 4PID为父结点ID,要求输入ID='bsd'求出所有的父结点和子结点。
ID PID A B C
aaa 1 2 2
bsd aaa 34 45 465
tew bsd 5 6 6
rty tew 567 435 4
gtt rty 34 67 7Answer:
declare @temp table (id char(8))insert @temp values ('bsd')while exists (
select id from tempTab
where pid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from tempTab
where pid in (select id from @temp)
and id not in (select id from @temp)
---把所有子节点插入@tempwhile exists (
select pid from tempTab
where id in (select id from @temp)
and pid not in (select id from @temp)
)
insert @temp
select pid from tempTab
where id in (select id from @temp)
and pid not in (select id from @temp)
---把所有父接点插入@tempselect * from tempTab
where id in (select id from @temp)2 建立公司部门关联表 公司id 部门id
3 int
即一个公司 对 一个或若干个部门可以,但一个部门如果对应若干个公司可能找起来就不太容易.
2. 最好使用ID做公司及部门的主键
3 如果是整数就用INT 如果值较大就用BIGINT 较小用SMALLINT
你的意思是不是要类型转换?,如果这样,我将来按这个INT字段排序时,SQL语句应该如何写?
无符号型的整数对应于数据库的什么类型?BIGINT吗?是不是有点浪费?