考验你，这句SQL有点难！
【情况描述】
有表company，里面存放所有企业信息，其中有字段area_id表示该企业所属地区的id，字段company_name表示企业名称。
有表area，里面存放所有地区信息，其中有字段id主键（对应表company中的area_id），其中p_id是该地区的上级地区id（对应自身表的id），
p_id和id是自反关系，其中字段area_name表示该地区名称。
【所需结果】
查询时通过传入的地区id来搜索company中的所有该地区的企业，以及该地区所有下级地区的所有企业（注意：该地区的下级可能还有下级）显示
结果包括company中的所有字段和area中的所有字段（如哪位能写出该SQL只需列出每个表中的一个字段即可如：company_name和area_name）
【要求】
通过一条SQL或视图或存储过程实现
【最终结果】
得到本人最真诚的感谢，和所有悬赏分。
[解决办法]
--生成测试数据 create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values('001' , null , '广东省')insert into tb values('002' , '001' , '广州市')insert into tb values('003' , '001' , '深圳市')insert into tb values('004' , '002' , '天河区')insert into tb values('005' , '003' , '罗湖区')insert into tb values('006' , '003' , '福田区')insert into tb values('007' , '003' , '宝安区')insert into tb values('008' , '007' , '西乡镇')insert into tb values('009' , '007' , '龙华镇')insert into tb values('010' , '007' , '松岗镇')go--创建用户定义函数 create function f_cid(@id varchar(10)) returns varchar(8000) as begin declare @i int , @ret varchar(8000) declare @t table(id varchar(10) , pid varchar(10) , level int) set @i = 1 insert into @t select id , pid , @i from tb where id = @id while @@rowcount <> 0 begin set @i = @i + 1 insert into @t select a.id , a.pid , @i from tb a , @t b where a.pid = b.id and b.level = @i - 1 end select @ret = isnull(@ret , '') + id + ',' from @t return left(@ret , len(@ret) - 1)end go --执行查询 select id , children = isnull(dbo.f_cid(id) , '') from tb group by iddrop table tbdrop function f_cid/*id children ---- ---------------------------------------001 001,002,003,004,005,006,007,008,009,010002 002,004003 003,005,006,007,008,009,010004 004005 005006 006007 007,008,009,010008 008009 009010 010*/（所影响的行数为 10 行）create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))insert into tb values('001' , null , N'广东省')insert into tb values('002' , '001' , N'广州市')insert into tb values('003' , '001' , N'深圳市')insert into tb values('004' , '002' , N'天河区')insert into tb values('005' , '003' , N'罗湖区')insert into tb values('006' , '003' , N'福田区')insert into tb values('007' , '003' , N'宝安区')insert into tb values('008' , '007' , N'西乡镇')insert into tb values('009' , '007' , N'龙华镇')insert into tb values('010' , '007' , N'松岗镇')go;with t as( select id , cid = id from tb union all select t.id , cid = tb.id from t join tb on tb.pid = t.cid )select id , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')from tbgroup by idorder by id/*id cid---- ---------------------------------------001 001,002,003,005,006,007,008,009,010,004002 002,004003 003,005,006,007,008,009,010004 004005 005006 006007 007,008,009,010008 008009 009010 010(10 行受影响)*/;with t as( select id , name , cid = id , path = cast(name as nvarchar(100)) from tb union all select t.id , t.name , cid = tb.id , path = cast(tb.name as nvarchar(100)) from t join tb on tb.pid = t.cid )select id , name , cid = STUFF((SELECT ',' + rtrim(cid) FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , ''), path = STUFF((SELECT ',' + path FROM t WHERE id = tb.id FOR XML PATH('')) , 1 , 1 , '')from tbgroup by id , nameorder by id/*id name cid path---- ---------- ------------------------------------------- ---------------------------------001 广东省 001,002,003,005,006,007,008,009,010,004 广东省,广州市,深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇,天河区002 广州市 002,004 广州市,天河区003 深圳市 003,005,006,007,008,009,010 深圳市,罗湖区,福田区,宝安区,西乡镇,龙华镇,松岗镇004 天河区 004 天河区005 罗湖区 005 罗湖区006 福田区 006 福田区007 宝安区 007,008,009,010 宝安区,西乡镇,龙华镇,松岗镇008 西乡镇 008 西乡镇009 龙华镇 009 龙华镇010 松岗镇 010 松岗镇(10 行受影响)*/drop table tb
[解决办法]
确实有点难。
[解决办法]
DECLARE @A INT SET @A=地区IDSELECT COMPANY_NAME,AREA_NAME /*第一级*/FROM COMPANY AJOIN AREA B ON A.AREA_ID=B.AREA_IDWHERE AREA_ID=@AUNION /*第二级*/SELECT COMPANY_NAME ,AREA_NAMEFROM COMPANY AJOIN AREA B ON A.AREA_ID=B.AREA_IDWHERE A.AREA_ID IN (SELECT AREA_ID FROM AREA WHERE P_ID=@A)UNION /*第三级*/SELECT COMPANY_NAME ,AREA_NAMEFROM COMPANY AJOIN AREA B ON A.AREA_ID=B.AREA_IDWHERE A.AREA_ID IN (SELECT A.AREA_IDFROM AREA AWHERE A.P_ID IN (SELECT AREA_ID FROM AREA WHERE P_ID=@A))...
[解决办法]
如果楼主是2005及高于05的版本，可以运用with cte来解决问题：
--company表 (id,parentid,name,...);with cte as( select * from company where id = ? --楼主需要查询的目标id union all select a.* from company a join cte b on a.parentid = b.id)select *from company a join area b on a.area_name = b.area_name join cte c on a.id = b.id
[解决办法]
-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate table tb(ID int, ParentID int)insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6-->Title:查找指定節點下的子結點if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin insert @t select ID from tb where ParentID=@ParentID while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin insert @t select ParentID from tb where ID=@ID while @@rowcount!=0 begin insert @t select a.ParentID from tb a inner join @t b on a.id=b.ParentID and not exists(select 1 from @t where ParentID=a.ParentID) end returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
[解决办法]
USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO-- 删除演示环境DROP TABLE Dept
[解决办法]
--网吧麽环境，思路create proc calc @id varchar(50)as begin declare @curr as varchar(1000) declare @priv as varchar(1000) set @curr =','+p_id while @curr <> @priv begin select @curr =@curr +id from area where CHARINDEX(p_id,@curr)<>0 and CHARINDEX(id,@curr)=0 set @priv = @curr end select * from company inner join (select * from area where CHARINDEX(id,@curr)=0) area on company.area_id=area.idend
[解决办法]
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
[解决办法]
create proc calc @id varchar(50)as begin declare @curr as varchar(1000) declare @priv as varchar(1000) set @priv='' set @curr =','+@id while @curr <> @priv begin set @priv = @curr select @curr =@curr+',' +id from area where CHARINDEX(pid,@curr)<>0 and CHARINDEX(id,@curr)=0 end-- select * from company inner join ( select * from area where CHARINDEX(id,@curr)<>0--) area -- on company.area_id=area.idendgo
[解决办法]
考虑4楼方法吧，不过写法好像有点问题
;with cte as( select * from Area where id = ? --楼主需要查询的地区id union all select a.* from Area a join cte b on a.parentid = b.id)select * --字段自己选择下from company a join cte c on a.Area_id = b.id