求任何几个节点的最小层共有父结点。表结构测试数据已经给出,求高手。

yygyogfny 2010-12-22 11:13:56

--地区表
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0
union all
select '广东',1
union all
select '广州',2
union all
select '天河',3
union all
select '天河1店',4

union all
select '深圳',2
union all
select '宝安',6
union all
select '宝安1店',7

union all
select '湖南',1
union all
select '长沙',9
union all
select '星沙',10
union all
select '星沙1店',11

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5
union all
select 8
---------
--求出#t2表中,5,8两个节点的最小层的共同父结点,在此例中是:(2,广东)


select * from #t
select * from #t2
drop table #t
drop table #t2


求高手。
...全文
175 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
coleling 2010-12-22
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 dlut_liuq 的回复:]

SQL code
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0
union all
select '广东',1
union all
select '广州',2
union all
select '天河',3
unio……
[/Quote]

up,拜一下
飘零一叶 2010-12-22
  • 打赏
  • 举报
回复
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0
union all
select '广东',1
union all
select '广州',2
union all
select '天河',3
union all
select '天河1店',4

union all
select '深圳',2
union all
select '宝安',6
union all
select '宝安1店',7

union all
select '湖南',1
union all
select '长沙',9
union all
select '星沙',10
union all
select '星沙1店',11

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5
union all
select 8

;with c1 as
(
select a.* from #t a join #t2 b on a.id=b.id
union all
select b.* from c1 a join #t b on a.pareid=b.id
)
select top(1) id from c1 group by id having COUNT(*)=(select count(distinct id) from #t2) order by id desc
--------------
id
2
coleling 2010-12-22
  • 打赏
  • 举报
回复
改了一下你的表名

--地区表
create table ta( id int identity(1,1), areaname nvarchar(20), pareid int)

insert into ta
select '中国',0 union all
select '广东',1 union all
select '广州',2 union all
select '天河',3 union all
select '天河1店',4 union all
select '深圳',2 union all
select '宝安',6 union all
select '宝安1店',7 union all
select '湖南',1 union all
select '长沙',9 union all
select '星沙',10 union all
select '星沙1店',11
go
--另有一表,某个人所拥有的地区
create table tb(id int)
insert into tb
select 5 union all
select 8
go

--建个函数,返回所有父节点
if object_id('getparentlist','tf') is not null
drop function getparentlist
go

create function getparentlist(@id int)
returns @tb table(id int,areaname nvarchar(20),lv int)
as
begin
with cte as
(
select id,areaname,pareid,lv=0 from ta where id=@id
union all
select a.id,a.areaname,a.pareid,lv=b.lv+1 from ta a join cte b on a.id = b.pareid
)
insert @tb select id,areaname,lv from cte where lv<>0

return
end
go

declare @sql varchar(8000)
select @sql = case when @sql is null then 'select top (1) t0.* from (select id,areaname from dbo.getparentlist('+cast(id as varchar(10))+')) t0 '
else @sql+' join (select id,areaname from dbo.getparentlist('+cast(id as varchar(10))+')) t'+cast(id as varchar(10))+' on t0.id = t'+cast(id as varchar(10))+'.id' end
from tb

exec(@sql)

/*
id areaname
----------- --------------------
2 广东

(1 行受影响)
*/



记得结贴哦
yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
to:bin_520_yan

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5
union all
select 8
union all
select 12

如果我加入一个:'星沙1店',12,结果应该是:中国
DataBox-MDX 2010-12-22
  • 打赏
  • 举报
回复
该sql语句支持#t2中存在不止5、8两个条件,可以有更多个

use City;
go
if object_id(N'#t',N'U') is not null drop table #t
go
if object_id(N'#t2',N'U') is not null drop table #t2
go
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0 union all
select '广东',1 union all
select '广州',2 union all
select '天河',3 union all
select '天河1店',4 union all
select '深圳',2 union all
select '宝安',6 union all
select '宝安1店',7 union all
select '湖南',1 union all
select '长沙',9 union all
select '星沙',10 union all
select '星沙1店',11

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5 union all
select 8
go
declare @sql nvarchar(2000)
declare @par nvarchar(2000)
set @par=''
set @sql='
with cte as
(
select id,areaname,pareid from #t where '
select @par=@par+'or id='+ltrim(id)+' ' from #t2
select @par=STUFF(@par,1,3,'')
select @sql=@sql+@par+'
union all
select #t.id,#t.areaname,#t.pareid from #t inner join cte
on cte.pareid=#t.id
)
,cte1 as
(
select top 100 percent *,ROW_NUMBER()over(partition by id order by id) as RN from cte order by id
)
select top 1 id,areaname,pareid from cte1 where RN>1 order by id desc
'
exec sp_executesql @sql
drop table #t
drop table #t2
/*
id areaname pareid
----------- -------------------- -----------
2 广东 1
*/

yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
;with c1 as
(
select * from #t where id=5
union all
select b.* from c1 a join #t b on a.pareid=b.id
),
c2 as
(
select * from #t where id=8
union all
select b.* from c2 a join #t b on a.pareid=b.id
)
select top(1)c1.* from c1 join c2 on c1.id=c2.id order by c1.id desc
--------------
id areaname pareid
2 广东 1

谢谢你的解答。
因为#t2表中会有几条记录,是不定的,
所以会比较麻烦些。。
飘零一叶 2010-12-22
  • 打赏
  • 举报
回复
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0
union all
select '广东',1
union all
select '广州',2
union all
select '天河',3
union all
select '天河1店',4

union all
select '深圳',2
union all
select '宝安',6
union all
select '宝安1店',7

union all
select '湖南',1
union all
select '长沙',9
union all
select '星沙',10
union all
select '星沙1店',11

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5
union all
select 8

;with c1 as
(
select * from #t where id=5
union all
select b.* from c1 a join #t b on a.pareid=b.id
),
c2 as
(
select * from #t where id=8
union all
select b.* from c2 a join #t b on a.pareid=b.id
)
select top(1)c1.* from c1 join c2 on c1.id=c2.id order by c1.id desc
--------------
id areaname pareid
2 广东 1

办法不是很好,再想想
yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
没分了,实在不好意思。。坐等。
yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
to:Dlut_LIuQ

(飘零一叶)

高手!!!!哈哈,非常感谢!!
yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
已经非常接近正确答案了
yygyogfny 2010-12-22
  • 打赏
  • 举报
回复
TO:bin_520_yan

改了下,这下应该可以了。


如果#t2表里只有一条记录呢,比如8..

查出来好像就没有记录了。
DataBox-MDX 2010-12-22
  • 打赏
  • 举报
回复
改了下,这下应该可以了。

use City;
go
if object_id(N'#t',N'U') is not null drop table #t
go
if object_id(N'#t2',N'U') is not null drop table #t2
go
create table #t
(
id int identity(1,1),
areaname nvarchar(20),
pareid int
)

insert into #t
select '中国',0 union all
select '广东',1 union all
select '广州',2 union all
select '天河',3 union all
select '天河1店',4 union all
select '深圳',2 union all
select '宝安',6 union all
select '宝安1店',7 union all
select '湖南',1 union all
select '长沙',9 union all
select '星沙',10 union all
select '星沙1店',11

--另有一表,某个人所拥有的地区
create table #t2(id int)
insert into #t2
select 5 union all
select 8
go
declare @sql nvarchar(2000)
declare @par nvarchar(2000)
set @par=''
set @sql='
with cte as
(
select id,areaname,pareid from #t where '
select @par=@par+'or id='+ltrim(id)+' ' from #t2
select @par=STUFF(@par,1,3,'')
select @sql=@sql+@par+'
union all
select #t.id,#t.areaname,#t.pareid from #t inner join cte
on cte.pareid=#t.id
)
,cte1 as
(
select top 100 percent *,ROW_NUMBER()over(partition by id order by id) as RN from cte order by id
)
select top 1 id,areaname,pareid from cte1 where RN>1 order by RN desc
'
exec sp_executesql @sql
drop table #t
drop table #t2

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧