这种需求如何SQL查询？

CheerYang 2011-01-26 10:38:19 


我有一张表baseinfo，字段结构如下

lid  decimal(8, 0)    父节点

pid  decimal(8, 0)    子节点

ms   nvarchar(80)     名称



-- 1、温馨提示：

-- 完整地址举例：珠海市｜香洲区｜凤凰南路121号｜鑫北花园2栋｜三单元｜2020号｜

-- 一般地址会以｜为界划分行下区域、街道、小区、单元、门牌等层次数据

-- 因此，baseinfo表中数据有上百万条，其中，ms主要是存放以上地址的某部份，即｜分开的某部份。

-- 其中，在baseinfo中查询ms='珠海市' 为根节点。



-- 2、现在以 select * from baseinfo t where ms='珠海市' 查询出来的结果如下：

lid               pid             cid     ms    bz

28000272	28000261	1307094	香洲区	0	GDSZHSXZQ	NULL	NULL	0	0	NULL	NULL	0

28001294	28000261	891843	斗门区	0	GDSZHSDMQ	NULL	NULL	0	0	NULL	NULL	0

28001295	28000261	1013837	金湾区	0	GDSZHSJWQ	NULL	NULL	0	0	NULL	NULL	0



-- 3、先以select * from baseinfo t where pid='28000261' 结果如下：

lid               pid             cid     ms    bz

28000272	28000261	1307094	香洲区	0	GDSZHSXZQ	NULL	NULL	0	0	NULL	NULL	0

28001294	28000261	891843	斗门区	0	GDSZHSDMQ	NULL	NULL	0	0	NULL	NULL	0

28001295	28000261	1013837	金湾区	0	GDSZHSJWQ	NULL	NULL	0	0	NULL	NULL	0



-- 4、再以select * from baseinfo t where pid='28000272' 结果如下:

lid               pid             cid     ms    bz

28000161	28000272	924728	888商业街	0	888SYJ	NULL	NULL	0	0	NULL	NULL	0

28000172	28000272	792641	鞍莲路	0	ALL	NULL	NULL	0	0	NULL	NULL	0

28000173	28000272	792649	鞍山路	0	ASL	NULL	NULL	0	0	NULL	NULL	0

28000174	28000272	792888	安富街	0	AFJ	NULL	NULL	0	0	NULL	NULL	0

28000175	28000272	792900	安宁路	0	ANL	NULL	NULL	0	0	NULL	NULL	0

28000176	28000272	792902	安平路	0	APL	NULL	NULL	0	0	NULL	NULL	0

28000177	28000272	796114	八一路	0	BYL	NULL	NULL	0	0	NULL	NULL	0



-- 5、仍以select * from baseinfo t where pid='28000161' 结果如下:

lid               pid             cid     ms    bz

28000162	28000161	890301	东街	0	DJ	NULL	NULL	0	0	NULL	NULL	0

28000163	28000161	900943	二巷	0	EX	NULL	NULL	0	0	NULL	NULL	0

28000164	28000161	1080527	六巷	0	LX	NULL	NULL	0	0	NULL	NULL	0





-- 6、再查select * from baseinfo t where pid='28000162' 结果如下:

lid               pid             cid     ms    bz

28069288	28000162	826042	25号	3	25H	NULL	4     	65	0	0	0	0

28069289	28000162	941381	9号	3	9H	NULL	4     	77	0	0	0	0

28069291	28000162	825658	22号	3	22H	NULL	4     	62	0	0	0	0

28069292	28000162	825530	21号	3	21H	NULL	4     	61	0	0	0	0



-- 7、再查select * from baseinfo t where pid='28069288' 结果如下:

无数据





--------------------------------------------------------

--  问题：

--  我想把baseinfo的数据提取成以下数据

--  addr1      addr2      addr3         addr4    addr5

--  珠海市     香洲区     888商业街     东街     25号

--  珠海市     香洲区     888商业街     东街     9号

--  珠海市     香洲区     888商业街     ...      ...

--  ...

--  珠海市     斗门区     ...

--  ...



--  从根节点开始循环查询，直至最后无结果时返回上一级下一条继续查询，最尾一节点不能以 xx号为判断，

--  必须以查询无结果为判断跳出循环。



--  请问这样的查询如何用SQL或存储过程实现？

--------------------------------------------------------
...全文
170 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
cxmcxm 2011-01-27
  • 打赏
  • 举报
 回复
1.先算出层数最大值
2.以最大长度创建临时表
create table #tmp (id1,addr1,id2 addr2,id3,addr3.....id最大数，addr最大数，id,pid,lx) --lx用于区分查询结果
3.用一while来对#tmp插入记录
declare @n int
set @n=1
insert into #tmp 插入根地址 id,与ms放于id1与addr1中 lx设为@n
while @n<最大层
begin
----------------------------------
insert into #tmp ...select
from #tmp a,baseinfo b where where a.id=b.fid and a.lx=@n
--关键是上面的语句，因为@n要对应不同的id列与addr列，所以上面语句应用动态生成语句，
如果最大层不大，也可用一串if @n=层数 来分别写每层的语句
set @n=@n+1
end
--以上语句执行完，select addr1,addr2.....* from #tmp即可得到LZ的需求
CheerYang 2011-01-26
  • 打赏
  • 举报
 回复
楼上的基本符合我的需求了。谢谢你们先。

但还有个问题，这样的数据结果集是以列排的，如果我指定我的几百万条数据的ID为根节点后，我怎样才能得到我的数据格式？

-- addr1 addr2 addr3 addr4 addr5
-- 珠海市 香洲区 888商业街 东街 25号
-- 珠海市 香洲区 888商业街 东街 9号
-- 珠海市 香洲区 888商业街 ... ...
-- ...
-- 珠海市 斗门区 ...
-- ...

dawugui 2011-01-26
  • 打赏
  • 举报
 回复 


/*

标题：SQL SERVER 2005中查询指定节点及其所有子节点的方法(表格形式显示)

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 

时间：2010-02-02

地点：新疆乌鲁木齐

*/



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



DECLARE @ID VARCHAR(3)



--查询ID = '001'的所有子节点

SET @ID = '001'

;WITH T AS

(

  SELECT ID , PID , NAME 

  FROM TB

  WHERE ID = @ID

  UNION ALL

  SELECT A.ID , A.PID , A.NAME 

  FROM TB AS A JOIN T AS B ON A.PID = B.ID

)

SELECT * FROM T ORDER BY ID

/*

ID   PID  NAME

---- ---- ----------

001  NULL 广东省

002  001  广州市

003  001  深圳市

004  002  天河区

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



(10 行受影响)

*/



--查询ID = '002'的所有子节点

SET @ID = '002'

;WITH T AS

(

  SELECT ID , PID , NAME 

  FROM TB

  WHERE ID = @ID

  UNION ALL

  SELECT A.ID , A.PID , A.NAME 

  FROM TB AS A JOIN T AS B ON A.PID = B.ID

)

SELECT * FROM T ORDER BY ID

/*

ID   PID  NAME

---- ---- ----------

002  001  广州市

004  002  天河区



(2 行受影响)

*/



--查询ID = '003'的所有子节点

SET @ID = '003'

;WITH T AS

(

  SELECT ID , PID , NAME 

  FROM TB

  WHERE ID = @ID

  UNION ALL

  SELECT A.ID , A.PID , A.NAME 

  FROM TB AS A JOIN T AS B ON A.PID = B.ID

)

SELECT * FROM T ORDER BY ID

/*

ID   PID  NAME

---- ---- ----------

003  001  深圳市

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



(7 行受影响)

*/



drop table tb



--注：除ID值不一样外，三个SQL语句是一样的。
dawugui 2011-01-26
  • 打赏
  • 举报
 回复 


/*

标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 

时间：2008-05-12

地点：广东深圳

*/



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(3)) returns @t_level table(id varchar(3) , level int)

as

begin

  declare @level int

  set @level = 1

  insert into @t_level select @id , @level

  while @@ROWCOUNT > 0

  begin

    set @level = @level + 1

    insert into @t_level select a.id , @level

    from tb a , @t_Level b

    where a.pid = b.id and b.level = @level - 1

  end

  return

end

go



--调用函数查询001(广东省)及其所有子节点

select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

002  001  广州市

003  001  深圳市

004  002  天河区

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 10 行）

*/



--调用函数查询002(广州市)及其所有子节点

select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

002  001  广州市

004  002  天河区



（所影响的行数为 2 行）

*/



--调用函数查询003(深圳市)及其所有子节点

select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

003  001  深圳市

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 7 行）

*/



drop table tb

drop function f_cid






@@ROWCOUNT：返回受上一语句影响的行数。

返回类型：integer。

注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。

示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。



UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'

IF @@ROWCOUNT = 0

   print 'Warning: No rows were updated'



结果：



(所影响的行数为 0 行)

Warning: No rows were updated

叶子 2011-01-26
  • 打赏
  • 举报
 回复 


--创建表

declare @T table (ID int,pid int,NAME varchar(6))

insert into @T

select 1,0,'上衣' union all

select 2,0,'鞋子' union all

select 3,0,'裤子' union all

select 4,1,'毛衣' union all

select 5,1,'衬衫' union all

select 6,2,'球鞋' union all

select 7,2,'皮鞋' union all

select 8,3,'西裤' union all

select 9,3,'筒裤' union all

select 10,4,'羊毛衣' union all

select 11,4,'牛毛衣' union all

select 12,5,'白衬衫' union all

select 13,5,'黑衬衫'

 

declare @i int

set @i =1 --参数假定为



;with

depts as(

    select * from @T

    where ID = 1

    union all

    select a.*

    from @T a, depts b

    where a.pid = b.ID

)



--取出所有id为1的子项

select * from depts

/*

ID          pid         NAME

----------- ----------- ------

1           0           上衣

4           1           毛衣

5           1           衬衫

12          5           白衬衫

13          5           黑衬衫

10          4           羊毛衣

11          4           牛毛衣

*/
CheerYang 2011-01-26
  • 打赏
  • 举报
 回复
baseinfo的数据太庞大了，无法贴得比较全面的数据

其实把各步骤的查询结果合起来差不多了。

看了递归，还未懂如何应用到baseinfo中。
GoAwayZ 2011-01-26
  • 打赏
  • 举报
 回复
参考表的递归查询。
gw6328 2011-01-26
  • 打赏
  • 举报
 回复 


USE test;

CREATE TABLE T(ID INT IDENTITY ,PID INT,C VARCHAR(10));

INSERT INTO T SELECT 0,'a'

UNION ALL SELECT 1,'a-1'

UNION ALL SELECT 1,'a-2'

UNION ALL SELECT 1,'a-3'

UNION ALL SELECT 3,'a-2-1'

UNION ALL SELECT 3,'a-2-2';



select * from t;



;WITH CTE AS

(

	SELECT * FROM T WHERE ID=3 --这里是参数，你可以修改

	UNION ALL

	SELECT A.* FROM T A JOIN CTE B ON A.PID=B.ID

)

SELECT * FROM CTE
AcHerat 元老 2011-01-26
  • 打赏
  • 举报
 回复 


--LZ把你表的数据贴点出来！
CheerYang 2011-01-26
  • 打赏
  • 举报
 回复
补充，

上面2和3步骤执行的结果是一样的。2和3只需执行其中一种即可，都是实现查询根节点的所有数据
CheerYang 2011-01-26
  • 打赏
  • 举报
 回复
楼上的基本符合我的需求了。谢谢你们先。

但还有个问题，这样的数据结果集是以列排的，如果我指定我的几百万条数据的ID为根节点后，我怎样才能得到我的数据格式？

-- addr1 addr2 addr3 addr4 addr5
-- 珠海市 香洲区 888商业街 东街 25号
-- 珠海市 香洲区 888商业街 东街 9号
-- 珠海市 香洲区 888商业街 ... ...
-- ...
-- 珠海市 斗门区 ...
-- ...

java sql in语句,sql语句In查询的好
sql语句In查询的好SQL语句查询生性为前端，无奈要写SQL，这个完全不懂，为了钱也要说，“我会做”。碎言做了一个项目就一直能拖两个月，虽说期间也做了一些其他事情，但是需求改来改去的，一直完不成，简直毫无成就感，终于最后一套方案落地，终于可以看到最新的功能上线了。开心，美滋滋，期间也遇到一些坑。嗯，不，是技术难点。在此分享sql的语句查询都知道在跟数据库交互的时候是非常耗时间的，也就是说尽量减少...
SQL查询语句之查询数据
查询数据是指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。在MySQL 中是使用SELECT语句来查询数据的。 1、基本查询语句 查询数据是数据库操作中最常用的操作。通过对数据库的查询，用户可以从数据库中获取需要的数据。数据库中可能包含着无数的表，表中可能包含着无数的记录。因此，要获得所需的数据并非易事。MySQL中可以使用SELECT 语句来查询数据。根据查询的条件的不同，数据库系统
为什么要进行SQL优化？建议收藏
为什么SQL需要优化? 查询性能低 执行时间过长 等待时间过长 SQL写的太差(尤其体现在多表查询上) 索引失效 服务器参数(缓存,线程数)设置不合理 项目需求不合理 …等等 我们程序员写代码时能做的只有对写SQL尽可能的做出优化,执行效率更高,有效的使用索引,重点放在写SQL上 SQL的执行过程 MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的...
mybatis拦截器修改SQL
平常我们写项目时应该都会遇到这么一个问题，一些接口需要根据账号权限的不同而展示不同的数据，又或者是SaaS化产品的租户，一般遇到这种情况，我们在写查询语句的时候，会加上相应的SQL来过滤掉不符合的数据，这样当然是可行的，但是基本上SQL都是相同的，所以就会造成会有很多重复的SQL片段，这样看起来也不太雅观，那有什么方法可以不写这么多重复的SQL吗？这一步就比较自由了，主要看大家的需求是怎样的，我这里就稍微弄个简单点的，注解里有一个参数，这个参数会拼接到原来的SQL上。
SQL视图查询详解
T-SQL查询进阶--深入浅出视图 简介        视图可以看作定义在SQL Server上的虚拟表.视图正如其名字的含义一样，是另一种查看数据的入口.常规视图本身并不存储实际的数据，而仅仅存储一个Select语句和所涉及表的metadata.     视图简单的理解如下:          通过视图，客户端不再需要知道底层table的表结构及其之间的关系。视图提供
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章