前段时间,应公司产品部的兄弟的请求,我写了一个Sql脚本来帮助他们统计不存在明细信息的地区列表。
需求是这样的,在项目中存在一张如下的地区表:
和这样地区表关联的还有一张地区明细表:
产品组的同事需要录入地区的详细信息,但是现在不记得之前录入了多少条记录,现在还剩下多少条记录,现在他们想知道现在没有录入详细信息的地区的列表。这个地区列表是个多级的地区列表,最多包括省,市,区,街4级分类。
刚开始想用游标来实现,发现查询的速度很慢,故转而采用存储过程。
1: set ANSI_NULLS ON
2: set QUOTED_IDENTIFIER ON
3: go
4:
5: --创建的存储过程用来查询地区的详细分级信息,从省,市,区,街,最多4级分类
6: Create procedure [dbo].[GetRegion]
7: @regionid int = 0,
8: @parentid int = 0,
9: @parentid2 int = 0,
10: @parentid3 int = 0,
11: @allregion varchar(200)='',
12: @regionname varchar(50)='',
13: @parentname1 varchar(50)='',
14: @parentname2 varchar(50)='',
15: @parentname3 varchar(50)=''
16: as
17: begin
18: if(@parentid!=0)
19: begin
20: set @parentid=(select parentid from region where regionid=@regionid);
21: set @regionname=(select name from region where regionid=@regionid);
22: set @parentname1=(select name from region where regionid=@parentid);
23: set @parentid2=(select parentid from region where regionid=@parentid);
24: set @parentname2=(select name from region where regionid=@parentid2);
25: set @parentid3=(select parentid from region where regionid=@parentid2);
26: set @parentname3=(select name from region where regionid=@parentid3);
27: --exec GetRegion @regionid,@parentid;
28: set @allregion=(@parentname3+@parentname2+@parentname1+@regionname);
29: print @allregion;
30: end
31: end
32:
33: --创建带行号的临时表(包含要查询的记录),通过循环传递每行的regionid,parentid调用上面的存储过程
34: declare @regionid int,@parentid int,@i int,@rownumber int;
35: begin
36: set @rownumber=(select count(1) from
37: region r where not exists
38: (
39: select 1 from regiondetail rd
40: where r.regionid=rd.regionid
41: ));
42: if OBJECT_ID (N'tempdb..#regiontemp1', N'U') IS NOT NULL
43: begin
44: drop table #regiontemp1;
45: end;
46: select * into #regiontemp1
47: from (select Row_Number() over
48: (Order by RegionId) as RowId ,* from
49: (
50: select r.regionid,r.parentid from
51: region r where not exists
52: (
53: select 1 from regiondetail rd
54: where r.regionid=rd.regionid
55: )
56: )t
57: )r;
58: set @i=1;
59: while @i<=@rownumber
60: begin
61: set @regionid=(select regionid from #regiontemp1 where RowId=@i);
62: set @parentid=(select parentid from #regiontemp1 where RowId=@i);
63: exec GetRegion @regionid,@parentid;
64: set @i=@i+1;
65: end
66: end
67:
Sql Server 2005数据库的打印出来的结果如下所示: