宗地数据处理中的sql语句综合

源数据：现状宗地（zd表）
包含五十多列数据（objectid,zdh,qsz,...,shape），其中shape是几何图形列
目的：找出满足条件的宗地列表
1.求现状宗地中两两图形相交的面积
--定义表变量
declare  @table table
(
ZDH1 NVARCHAR(100),
ZDH2 NVARCHAR(100),
xArea float
)
--定义变量
declare @objectid nvarchar(100)
declare @ZDH nvarchar(100)
declare @SHAPE geometry
--使用游标进行遍历
DECLARE myCursor CURSOR
FOR select objectid,ZDH,SHAPE from ZD WHERE ISNULL(ZDH,'') <> '' ORDER BY objectid

OPEN myCursor
FETCH NEXT FROM myCursor INTO @objectid,@ZDH,@SHAPE

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @table
SELECT @ZDH,ZDH,SHAPE.STIntersection(@SHAPE).STArea()
FROM ZD
WHERE SHAPE.STIntersects(@SHAPE) =1 
AND ZDH <> @ZDH AND SHAPE.STIntersection(@SHAPE).STArea() > 0 
AND objectid > @objectid

FETCH NEXT FROM myCursor INTO @objectid,@ZDH,@SHAPE
END

CLOSE myCursor
DEALLOCATE myCursor
--系统中是否已经存在B_01_InterArea,若已经存在则删除
if exists(select 1 from sysobjects where name = 'B_01_InterArea')
drop table B_01_InterArea

--将遍历到的结果插入到新的表中
SELECT * INTO B_01_InterArea FROM @table

SELECT * from B_01_InterArea where ZDH1='12075' or ZDH2='12075'

2. 统计每块宗地相交的次数即与多少块宗地相交
---统计宗地号出现的次数
select zdh,count(0) as xCount into B_02_InCountD5 from 
(
select zdh1 as zdh from B_01_InterArea
union all
select zdh2 as zdh from B_01_InterArea
) a
group by zdh order by xCount desc

3. 统计叠加面积占小宗地面积的百分比的宗地明细
declare  @table table
(
ZDH1 NVARCHAR(100),
ZDH1_Area float,
ZDH2 NVARCHAR(100),
ZDH2_Area float,
xArea float
)

insert into @table
select ZDH1,(select shape.STArea() from zd where zdh = zdh1),ZDH2,(select shape.STArea() from zd where zdh = zdh2),xArea
from B_01_InterArea

select * into B_03_InterPercentAll23 from 
(
select *,
case when ZDH1_Area > ZDH2_Area then 100*xArea/ZDH2_Area
else 100*xArea/ZDH1_Area
end xPercent
from @table
) a

ORDER BY A.xPercent DESC
select * from B_03_InterPercentAll order by xPercent desc
4. 排序使大宗地面积在前小宗地面积在后
select
case when ZDH1_Area > ZDH2_Area then zdh1
else zdh2
end zdh1,

case when ZDH1_Area > ZDH2_Area then ZDH1_Area
else ZDH2_Area
end ZDH1_Area,

case when ZDH2_Area < ZDH1_Area then zdh2
else zdh1
end zdh2,

case when ZDH2_Area < ZDH1_Area then ZDH2_Area
else ZDH1_Area
end ZDH2_Area,

xArea,
xPercent

into B_03_InterPercentAll_PX from B_03_InterPercentAll order by xPercent desc
5. 统计宗地相交次数小于5个的宗地明细
declare  @table table
(
ZDH NVARCHAR(100),
xCount INT,
xDesc NVARCHAR(max)
)

declare @ZDH nvarchar(100)
declare @xCount INT


DECLARE myCursor CURSOR
FOR select ZDH,xCount from B_02_InterCount WHERE xCount < 5

OPEN myCursor
FETCH NEXT FROM myCursor INTO @ZDH,@xCount

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @lotids NVARCHAR(300) = ''
SELECT @lotids = @lotids + ZDH2 + ',面积:' + CONVERT(NVARCHAR,xArea) + '~' FROM A_01 WHERE ZDH1 = @ZDH
INSERT INTO @table
SELECT  @ZDH,@xCount,@lotids

FETCH NEXT FROM myCursor INTO @ZDH,@xCount
END

CLOSE myCursor
DEALLOCATE myCursor
SELECT * INTO B_03_InterConXy5 FROM @table

6. 统计叠加面积占小宗地面积的百分比大于等于99%的宗地明细
declare  @table table
(
ZDH1 NVARCHAR(100),
ZDH2 NVARCHAR(100),
xArea float,
ZDH1_Area float,
ZDH2_Area float
)

insert into @table
select ZDH1,ZDH2,xArea,(select shape.STArea() from zd where zdh = zdh1),(select shape.STArea() from zd where zdh = zdh2)
from B_01_InterArea


select * into B_04_InterPercentDy99 from 
(
select *,
case when ZDH1_Area > ZDH2_Area then 100*xArea/ZDH2_Area
else 100*xArea/ZDH1_Area
end xPercent
from @table
) a
WHERE xPercent >= 99
ORDER BY A.xPercent DESC

select * from B_04_InterPercentDy99 order by xPercent desc

7. 计算shape列的面积
declare @ZDH NVARCHAR(100),
     @shape geometry;
select @shape = shape from ZD ;
select @shape;
select @shape.STArea() as 面积;--求面积
8.要在SQL中写类似for循环，可以使用SQL中的游标来实现，当然SQL中也有for loop、while do等，我这里仅以使用游标方式来进行示例
---利用游标循环更新、删除MemberAccount表中的数据
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
--UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新
--DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除
FETCH NEXT FROM My_Cursor; --读取下一行数据
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标

http://blog.sina.com.cn/s/blog_9d0a434f0102wnwt.html