白云项目SQL语句记录

赋值语句相关

成果输出相关

一、封面

无相关 SQL 语句，直接从【使用权宗地】读取。

二、地上附着物清点汇总表

1. 与宗地关联关系

SYQZDATTR.ZDYBH = 白云区地上附着物测绘清点汇总表.ZDYBH

2. 数据提取语句

表名： 白云区地上附着物测绘清点汇总表

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT * FROM
    (
        /*汇总拍照点类别为 1 的房屋信息，单位为 ㎡ ，数量从 SL 字段合计，类型设置为 1 */
        SELECT
            '房屋' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
            , SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) SL
            , '1' AS LX
            , SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) 合计
            , BZ AS BZ
        FROM smjdattr WHERE PZDLB = 1 GROUP BY ZDYBH, YSMC
    UNION
        SELECT
            '按面积统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
            , SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) SL
            , '2' AS LX
            , SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('MJWS', '小组配置')) 合计
            , BZ AS BZ
        FROM smjdattr WHERE PZDLB = 2 OR ( PZDLB IN(3, 4) AND JLFF IN(1, 2)) GROUP BY ZDYBH, YSMC, GG
    UNION
        SELECT
            '按体积统计附着物' AS DL, ZDYBH, YSDM, YSMC, JLDW AS DW, YSMC AS YSMC_XS
            , SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('TJWS', '小组配置')) SL
            , '4' AS LX
            , SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('TJWS', '小组配置')) 合计
            , BZ AS BZ
        FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 3 GROUP BY ZDYBH, YSMC, GG
    UNION
        SELECT
            '按长度统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW  
            , SUBSTR(sum(SL)+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', sum(SL)+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('CDWS', '小组配置')) SL
            , '5' AS LX
            , SUBSTR(SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'), 1
            , charindex('.', SUM(sl) OVER()+ ZLDICNAME('WSCS', '小组配置'))+ ZLDICNAME('CDWS', '小组配置')) 合计
            , BZ AS BZ
        FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 4 GROUP BY ZDYBH, YSMC, GG
    UNION
        SELECT
            '按个数统计附着物' AS DL, ZDYBH, YSDM, YSMC, YSMC AS YSMC_XS, JLDW AS DW
            , sum(SL) SL
            , '6' AS LX
            , SUM(sl) OVER() 合计
            , BZ AS BZ
        FROM SMJDATTR WHERE PZDLB IN(3, 4) AND JLFF = 5 GROUP BY  ZDYBH, YSMC, GG
    ) t ORDER BY t.LX

三、房屋测绘调查明细表

1. 与宗地关联关系

SYQZDATTR.OBJECTID = 自然幢排序.DBZH
自然幢排序.OBJECTID = ZRZATTR.OBJECTID ORDER BY ZRZATTR.排序号

2. 数据提取语句

表名：自然幢排序

/*根据编号排序*/
select bh as 排序号,* from zrzattr  order by  bh

四、构筑物、附属物及青苗清点明细表

1.与宗地关联关系

SYQZDATTR.ZDYBH = 清点表1.ZDYBH

2.数据提取语句

表名：清点表1

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT
    ZDYBH
    , ZLDICNAME(substr(YSDM, 1, 1), '建构筑物名称') 一级类
    , ZLDICNAME(substr(YSDM, 1, 3), '建构筑物名称') 二级类
    , YSMC 三级类
    , YSMC AS YSMC_XS
    , JLDW
    , sum(SL) SL
    ,(CASE WHEN GG = '' OR GG IS NULL THEN ZDJS_GG ELSE GG END) GG1
    , BZ, BH, pzdlb
 FROM
    smjdattr
 GROUP BY
    ZDYBH, YSMC, GG1, BH
 HAVING
    pzdlb IN(2, 3, 4) AND YSMC IS NOT NULL
 ORDER BY
    zdybh
    , substr(ZLDICVALUE(YSMC, '建构筑物名称'), 1, 3)
    , ZLPADLEFT(BH, '0', 3)

五、位置示意图

无相关 SQL 语句，直接从【使用权宗地】读取。

六、房屋测绘平面图

1.与宗地关联关系

SYQZDATTR.OBJECTID = ZRZATTR.DBZH
ZRZATTR.GROUPID = GEOPY.GROUPID
GEOPY.OBJECTID = 房屋分层分类统计.OBJECTID

2.数据提取语句

表名：房屋分层分类统计

SELECT
    *
    , CASE
        WHEN JSXS = 0 THEN '高度不够' || X'0D' || X'0A' || '不计算面积'
        ELSE CAST(ROUND(JZMJ, 4) AS text) END JZMJ_STR
 FROM
    (SELECT
            RP.DBZH, RP.XBZH, t.OBJECTID, t.DCODE
            , ROW_NUMBER() OVER(PARTITION BY RP.XBZH ORDER BY MC) BH
            , GEOPY.FWCS
            , CASE
                WHEN MC LIKE 'M%' THEN 'M' || ifnull( GEOPY.FWCS, '')
                WHEN MC LIKE 'N%' THEN 'N' || ifnull(GEOPY.FWCS, '')
                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 'M%' OR MC LIKE 'N%' OR MC LIKE 'A%' OR MC LIKE 'B%' 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, xbzh FROM respyattr) RP
        ON t.objectid = RP.objectid
        JOIN GEOPY ON t.objectid = GEOPY.objectid
        WHERE MC IS NOT NULL AND xbzh IS NOT NULL
    ) WHERE JSXS > 0

七、构筑物测绘平面图（依附于建筑物）

1.与宗地关联关系

SYQZDATTR.OBJECTID = ZRZATTR.DBZH
ZRZATTR.GROUPID = FSGZWBZB.FZID
FSGZWBZB.XBZH = GROUPFEATUREPRINTINFO.GROUPVALUE
GROUPFEATUREPRINTINFO.PRINTEXTENTOBJECTID = FEATUREPRINTINFO.OBJECTID
/*模板内关系表*/
FEATUREPRINTINFO.OBJECTID = 层属构筑物明细.PRINTEXTENTOBJECTID

2.数据提取语句

表名：层属构筑物明细

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT
    PrintExtentObjectID
    , dbzh, 名称, YSMC_XS, 长度, 宽度, 高度
    , '<' || 编号 || '>' 编号
    , 厚度, 水平投影面积, 数量, count(*) 坐标数量
    , h.sl 角点数量, 类型, 备注, 关联拍照点ID
    , 总数量, 计量单位, 计量方法
 FROM
    (SELECT
            p.PrintExtentObjectID, YSMC_XS
            , t.DBZH, t.LX AS 类型, t.CD 长度, t.KD 宽度
            , t.GS 高度, t.BH 编号, t.HJ 厚度, t.SPTYMJ 水平投影面积
            , 数量, t.YSMC 名称, t.BZ 备注, t.XBZH
            , t.表名, f.GLPZD_OID 关联拍照点ID
            , s.SL 总数量, s.JLDW 计量单位, s.JLFF 计量方法
        FROM
            (SELECT
                    OBJECTID, GLPZD_OID, '点' AS LX
                    , CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
                    , BZ, DBZH, XBZH, 'CQPT' AS 表名, SL 数量
                FROM CQPT
            UNION
                SELECT
                    OBJECTID, GLPZD_OID, '线' AS LX
                    , CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
                    , BZ, DBZH, XBZH, 'CQLN' AS 表名, SL 数量
                FROM CQLN
            UNION
                SELECT
                    OBJECTID, GLPZD_OID, '面' AS LX
                    , CD, KD, GS, BH, HJ, SPTYMJ, YSMC, YSMC AS YSMC_XS
                    , BZ, DBZH, XBZH, 'CQPY' AS 表名, SL 数量
                FROM CQPY
            ) t
        JOIN (SELECT
                    DISTINCT GroupValue, PrintExtentObjectID
                FROM GroupFeaturePrintInfo
                WHERE GroupValue IS NOT NULL AND GroupValue <> ''
            ) p 
            ON t.xbzh = p.GroupValue
        JOIN (SELECT
                    XBZH, GLPZD_OID
                FROM FSGZWBZB
            ) f
            ON t.XBZH = f.XBZH
        JOIN (SELECT
                    OBJECTID, SL, JLDW, JLFF
                FROM SMJDATTR
            ) s
            ON f.GLPZD_OID = s.OBJECTID
    ) t1
 LEFT JOIN (SELECT
            dyid, count(*) sl
        FROM MBTYANNLN
        WHERE dcode = '910100821' /*坐标注记代码*/
        GROUP BY dyid
    ) h
    ON h.dyid = t1.PrintExtentObjectID
 GROUP BY
    t1.PrintExtentObjectID
    , t1.dbzh, t1.名称, t1.长度, t1.宽度
    , t1.高度, t1.编号, t1.厚度, t1.水平投影面积
    , t1.类型, t1.备注
 ORDER BY 编号

八、构筑物测绘平面图（地面）

1.与宗地关联关系

SYQZDATTR.OBJECTID = ZRZATTR.DBZH
SYQZDATTR.ZDYBH = 地面构筑物拍照点.ZDYBH

2.数据提取语句

表名：地面构筑物拍照点

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT
    ZDYBH, bh, gzwbh, '(' || bh || ')' xbh, ysmc,ysmc AS YSMC_XS, sl 
    || CASE jlff 
        WHEN 1 THEN '㎡'
        WHEN 2 THEN '㎡'
        WHEN 3 THEN '?'
        WHEN 4 THEN 'm'
        ELSE jldw END value
 FROM smjdattr
 WHERE pzdlb = 4 AND gzwglbh <> '' AND gzwglbh NOT NULL

九、房屋照片

1.与宗地关联关系

SYQZDATTR.OBJECTID = ZRZATTR.DBZH
SMJDATTR.OBJECTID = 白云区房屋照片.YS_OBJECTID

2.数据提取语句

表名：白云区房屋照片

SELECT
    sw.*
    , SD.YSMC, SD.PZDLB, SD.ZDYBH, SD.FWBH, SD.BH
    , CASE
        WHEN SW.WJMC LIKE '%门牌照片%' THEN 1
        WHEN SW.WJMC LIKE '%正面照片%' THEN 2
        WHEN SW.WJMC LIKE '%侧面照片%' THEN 3
        WHEN SW.WJMC LIKE '%内部照片%' THEN 4
        ELSE 5 END ZPLX
    , CASE
        WHEN SW.WJMC LIKE '%门牌照片%' THEN '房屋门牌'
        WHEN SW.WJMC LIKE '%正面照片%' THEN '房屋正面'
        WHEN SW.WJMC LIKE '%侧面照片%' THEN '房屋侧面'
        WHEN SW.WJMC LIKE '%内部照片%' THEN '房屋内部'
        ELSE '其他位置' END ZPLXMC
 FROM smjwjxxb sw
 JOIN smjdattr sd
 ON sw.ys_objectid = sd.objectid
 WHERE PZDLB = 1
 ORDER BY fwbh COLLATE zlCompareCharNum

十、其它附着物照片

1.与宗地关联关系

SYQZDATTR.ZDYBH = 白云区构筑物照片1.ZDYBH

2.数据提取语句

表名：白云区构筑物照片1

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT
    sw.*
    , SD.YSMC, SD.YSMC_XS, SD.PZDLB, SD.ZDYBH, SD.BH
    , CASE
        WHEN sw.sl > 1 THEN YSMC_XS || '-' || SXH || '(编号' || BH || ')'
        WHEN sw.sl = 1 THEN YSMC_XS || '(编号' || BH || ')' END YSM
 FROM
    (SELECT
            ROW_NUMBER() OVER(PARTITION BY t.YS_OBJECTID) SXH
            , smjwjxxb .*, t.sl
        FROM smjwjxxb
        JOIN 
         (SELECT
                YS_objectid, count(*) sl
           FROM smjwjxxb
           GROUP BY YS_objectid
            ) t ON smjwjxxb.YS_objectid = t.YS_objectid
    ) sw
 JOIN 
     (SELECT YSMC AS YSMC_XS,* FROM smjdattr) sd
 ON sw.ys_objectid = sd.objectid
 WHERE PZDLB IN(3, 4)

十一、简易房屋照片

1.与宗地关联关系

SYQZDATTR.ZDYBH = 白云区简易房屋照片.ZDYBH

2.数据提取语句

表名：白云区简易房屋照片

【YSMC_XS】 要素名称_显示 为后期使用自定义名称时使用

SELECT
    sw.*
    , SD.YSMC, SD.YSMC AS YSMC_XS, SD.PZDLB, SD.ZDYBH, SD.BH
    , '简易结构' || '(编号' || BH || ')' YSM
 FROM
    smjwjxxb sw
 JOIN smjdattr sd ON
    sw.ys_objectid = sd.objectid
 WHERE
    PZDLB = 2