22,199
社区成员
发帖
与我相关
我的任务
分享
--地区表
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
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
--地区表
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 行受影响)
*/
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
*/
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
办法不是很好,再想想
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