南沙区拆迁项目 SQL 语句
赋值语句相关
房屋分类拆分语句
SELECT
*
FROM
(
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX( FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr)
WHERE instr(SZC, '.')= 0
一、自然幢赋值
1、赋值居民地小编组号
update respyattr set xbzh = (select groupid from geopy where geopy.objectid = respyattr.objectid)
二、居民地面赋值
1、更新房屋面结构
/*如果后期有修改的可能，则可以考虑采用字典的形式改写*/
UPDATE respyattr set jglx = ZLDICNAME(DCODE,'字典名称') WHERE 1 = 1;
UPDATE
respyattr
SET
JGLX = (
CASE
Dcode WHEN '0404000230' THEN 'A'
WHEN '0404000330' THEN 'C'
WHEN '0404000730' THEN 'B'
WHEN '0404000530' THEN 'M'
WHEN '0404000630' THEN 'E'
WHEN '0404000830' THEN 'E'
WHEN '0404000930' THEN 'E'
WHEN '0404000430' THEN 'E'
END
)
WHERE 1 = 1;
2、赋值默认【复合结构信息】
没有设置【复合结构】内容时，使用默认配置填充。
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1A全'
ELSE '1-' || FWCS || 'A全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404000230' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1B全'
ELSE '1-' || FWCS || 'B全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404000730' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1C全'
ELSE '1-' || FWCS || 'C全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404000330' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1M全'
ELSE '1-' || FWCS || 'M全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404000530' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1M全'
ELSE '1-' || FWCS || 'M全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404000530' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '2飘楼全'
ELSE '2-' || CAST(
FWCS + 1 AS text
) || '飘楼全'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404002130' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '2不封闭阳台半'
ELSE '2-' || CAST(
FWCS + 1 AS text
) || '不封闭阳台半'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404010630' AND FHJGXX IS NULL;
UPDATE respyattr
SET FHJGXX = (
SELECT
CASE
WHEN FWCS IS NULL THEN '1无上盖室外楼梯半'
ELSE '1-' || FWCS || '无上盖室外楼梯半'
END
FROM GEOPY
WHERE GEOPY.objectid = respyattr.objectid)
WHERE DCODE = '0404019130' AND FHJGXX IS NULL;
3、赋值【房屋结构名称】
UPDATE
respyattr
SET
FWJGMC = CASE
JGLX WHEN 'A' THEN '框架'
WHEN 'B' THEN '混合'
WHEN 'C' THEN '砖木'
WHEN 'M' THEN '钢'
WHEN 'N' THEN '钢、钢混'
END;
4、赋值【建基面积】
UPDATE
respyattr
SET
sfjsjj = CASE
WHEN fhjgxx LIKE '1%' THEN '1'
ELSE 0
END
WHERE fhjgxx IS NOT NULL AND ( sfjsjj IS NULL OR sfjsjj = '') ;
UPDATE
respyattr
SET
jjmj = (
SELECT
round(scmj, ZLDICNAME('MJWS','小组配置'))
FROM geopy
WHERE geopy.objectid = respyattr.objectid)
WHERE sfjsjj = '1';
UPDATE respyattr SET jjmj = NULL WHERE sfjsjj = '0';
5、赋值【编号注记】
UPDATE
respyattr
SET
BHZJ = (
SELECT
group_concat('<' || BH || '>', '\n')
FROM
(SELECT
RP.DBZH, t.OBJECTID, t.DCODE, ROW_NUMBER() OVER( PARTITION BY RP.dbzh ORDER BY t.objectid, sxh) BH
, GEOPY.FWCS
, CASE
WHEN MC LIKE 'B%' THEN 'B' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'A%' THEN 'A' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'C%' THEN 'C' || ifnull(GEOPY.FWCS, '')
ELSE REPLACE(MC, '1-1', '') END MC
, JSXS, SXH, CS, SZC
, Round(GEOPY.SCMJ, 4) * (
CASE
WHEN MC LIKE 'B%'
OR MC LIKE 'A%'
OR MC LIKE 'C%' THEN ifnull(
GEOPY.FWCS, 1)
ELSE CS END) * JSXS JZMJ
FROM
(SELECT *
FROM
(SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, ' 挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr)
WHERE instr(SZC, '.')= 0) t
JOIN (
SELECT OBJECTID, DBZH FROM respyattr
) RP ON
t.objectid = RP.objectid
JOIN GEOPY ON
t.objectid = GEOPY.objectid
WHERE
MC IS NOT NULL
) m
GROUP BY
objectid
HAVING
m.objectid = respyattr.objectid)
6、赋值【注记内容】
UPDATE
respyattr
SET
ZJNR = (
SELECT
group_concat(MC, '\n')
FROM
(SELECT
RP.DBZH, t.OBJECTID, t.DCODE
, ROW_NUMBER() OVER(PARTITION BY RP.dbzh ORDER BY t.objectid, sxh) BH
, GEOPY.FWCS
, CASE
WHEN MC LIKE 'B%' THEN 'B' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'A%' THEN 'A' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'C%' THEN 'C' || ifnull(GEOPY.FWCS, '')
ELSE REPLACE(MC, '1-1', '') END MC
, JSXS, SXH, CS, SZC, Round(GEOPY.SCMJ, 4) * (
CASE
WHEN MC LIKE 'B%'
OR MC LIKE 'A%'
OR MC LIKE 'C%' THEN ifnull(GEOPY.FWCS, 1)
ELSE CS END) * JSXS JZMJ
FROM
(SELECT *
FROM
(SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, ' 挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr)
WHERE instr(SZC, '.')= 0) t
JOIN (
SELECT
OBJECTID
, DBZH
FROM
respyattr
) RP ON
t.objectid = RP.objectid
JOIN GEOPY ON
t.objectid = GEOPY.objectid
WHERE
MC IS NOT NULL
) m
GROUP BY
objectid
HAVING
m.objectid = respyattr.objectid);
7、赋值阳台飘楼等非主体房屋的【房屋结构名称】
UPDATE
RESPYATTR
SET
FWJGMC = (
SELECT
FWJGMC
FROM
(
SELECT
DISTINCT XBZH
, FWJGMC
FROM
RESPYATTR r2
WHERE
SFJSJJ = 1
ORDER BY
r2.JJMJ DESC
) r3
WHERE
XBZH = r3.XBZH
)
WHERE
(
XBZH <> ''
OR XBZH NOT NULL
)
AND SFJSJJ <> 1;
三、宗地赋值
1、相机点信息赋值使用权宗地
通过叠加分析功能赋值。
2、赋值宗地【建筑总面积】
UPDATE syqzdattr
SET JZZMJ = (
SELECT
sum(JZMJ)
FROM
(
SELECT
RP.DBZH, t.OBJECTID, t.DCODE, ROW_NUMBER() OVER(PARTITION BY RP.dbzh ORDER BY t.objectid, sxh) BH, GEOPY.FWCS
, CASE WHEN MC LIKE 'B%' THEN 'B' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'A%' THEN 'A' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'C%' THEN 'C' || ifnull(GEOPY.FWCS, '')
ELSE REPLACE(MC, '1-1', '') END MC
, JSXS, SXH, CS, SZC
, Round(GEOPY.SCMJ, ZLDICNAME('MJWS','小组配置'))
* (CASE WHEN MC LIKE 'B%' OR MC LIKE 'A%' OR MC LIKE 'C%' THEN ifnull(GEOPY.FWCS, 1) ELSE CS END) * JSXS JZMJ
FROM
(
SELECT
*
FROM
(
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX( FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr)
WHERE instr(SZC, '.')= 0) t
JOIN (SELECT OBJECTID, DBZH FROM respyattr) RP ON t.objectid = RP.objectid
JOIN GEOPY ON t.objectid = GEOPY.objectid
WHERE MC IS NOT NULL) m
GROUP BY DBZH HAVING m.DBZH = syqzdattr.objectid);
3、赋值宗地【宗地面积】
UPDATE
syqzdattr
SET
zdmj = (
SELECT
round(scmj, ZLDICNAME('MJWS','小组配置'))
FROM geopy
WHERE geopy.objectid = syqzdattr.objectid);
4、赋值宗地【无证土地面积，无证建筑面积】
UPDATE
syqzdattr
SET
wztdmj = round(zdmj - ifnull(zztdmj, 0), ZLDICNAME('MJWS','小组配置'));
UPDATE
syqzdattr
SET
wzjzmj = round(jzzmj - ifnull(zzjzmj, 0), ZLDICNAME('MJWS','小组配置'));
5、赋值宗地调查信息
UPDATE SYQZDATTR SET XMMC = ZLDICNAME('XMMC','小组配置') WHERE 1 = 1;
UPDATE SYQZDATTR SET CHDW = ZLDICNAME('CLDW','小组配置') WHERE 1 = 1;
UPDATE SYQZDATTR SET CLR = ZLDICNAME('CLY','小组配置') WHERE CLR IS NULL OR CLR = '';
UPDATE SYQZDATTR SET CLRQ = strftime('%Y%m%d','now') WHERE CLRQ IS NULL OR CLRQ = '';
成果输出相关
一、房屋测量成果图（宗地）
拆迁界址点
1. 与宗地关联关系
SYQZDATTR.OBJECTID = 拆迁界址点.ZD_OID
2. 数据提取语句
表名： 拆迁界址点
SELECT * FROM MBJZDANNPT MB JOIN jzdpt ON MB.JZD_OID = jzdpt.objectid WHERE SFGJD = 1;
拆迁界址线
1. 与宗地关联关系
SYQZDATTR.OBJECTID = 拆迁界址线.ZD_OID
2. 数据提取语句
表名： 拆迁界址线
SELECT * FROM MBJZXANNPT MB JOIN jzXln ON MB.JZX_OID = jzxln.objectid;
白云区建筑面积统计表
1. 与宗地关联关系
SYQZDATTR.OBJECTID = 白云区建筑面积统计表.DBZH
2. 数据提取语句
表名： 白云区建筑面积统计表
SELECT
RP.DBZH, t.OBJECTID, t.DCODE
, ROW_NUMBER() OVER(PARTITION BY RP.dbzh
ORDER BY t.objectid, sxh) BH
, CASE WHEN SZC LIKE '1%' THEN RP.JJMJ END JJMJ
, RP.FWJGMC, GEOPY.FWCS
, CASE
WHEN MC LIKE 'B%' THEN 'B' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'A%' THEN 'A' || ifnull(GEOPY.FWCS, '')
WHEN MC LIKE 'C%' THEN 'C' || ifnull(GEOPY.FWCS, '')
ELSE REPLACE(MC, '1-1', '') END MC
, JSXS, SXH, CS, SZC
, Round(GEOPY.SCMJ, ZLDICNAME('MJWS','小组配置'))
* (
CASE
WHEN MC LIKE 'B%'
OR MC LIKE 'A%'
OR MC LIKE 'C%' THEN ifnull(GEOPY.FWCS, 1) ELSE CS END) * JSXS JZMJ
FROM
(SELECT *
FROM
(SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'C', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'C', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'C', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'C', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'C', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'B', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'B', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'B', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'B', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'B', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, 'A', 1, 1) MC
, ZLFHJGXX(FHJGXX, 'A', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, 'A', 1, 3) SXH
, ZLFHJGXX(FHJGXX, 'A', 1, 4) CS
, ZLFHJGXX(FHJGXX, 'A', 1, 5) SZC
FROM respyattr
UNION
SELECT
OBJECTID, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 1, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 1, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '飘楼', 2, 1) MC
, ZLFHJGXX(FHJGXX, '飘楼', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '飘楼', 2, 4) CS
, ZLFHJGXX(FHJGXX, '飘楼', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '不封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 1) MC
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 2) JSXS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 3) SXH
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 4) CS
, ZLFHJGXX(FHJGXX, '封闭阳台', 2, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有上盖室外楼梯', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 1) MC
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 4) CS
, ZLFHJGXX(FHJGXX, '楼梯间', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '夹层', 1, 1) MC
, ZLFHJGXX(FHJGXX, '夹层', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '夹层', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '夹层', 1, 4) CS
, ZLFHJGXX(FHJGXX, '夹层', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '有柱走廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '无柱走廊', 1, 5) SZC
FROM
respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '单柱廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '檐廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '檐廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '檐廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '檐廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, '门廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '门廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '门廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '门廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '门廊', 1, 5) SZC
FROM respyattr
UNION
SELECT
objectid, DCODE
, ZLFHJGXX(FHJGXX, ' 挑廊', 1, 1) MC
, ZLFHJGXX(FHJGXX, '挑廊', 1, 2) JSXS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 3) SXH
, ZLFHJGXX(FHJGXX, '挑廊', 1, 4) CS
, ZLFHJGXX(FHJGXX, '挑廊', 1, 5) SZC
FROM respyattr)
WHERE instr(SZC, '.')= 0) t
JOIN (
SELECT
OBJECTID, DBZH, JJMJ, FWJGMC
FROM
respyattr
) RP ON
t.objectid = RP.objectid
JOIN GEOPY ON t.objectid = GEOPY.objectid
WHERE MC IS NOT NULL
本文来自博客园，作者：AiZhenVIP，转载请注明原文链接：https://www.cnblogs.com/aizhen/p/16607232.html
浙公网安备 33010602011771号