查询例子

     var telReg = /^[1][3,4,5,6,7,8,9][0-9]{9}$/; //0\d{2,3}-\d{7,8}
        var telReg1 = /^0\d{2,3}-\d{7,8}$/; //0\d{2,3}-\d{7,8}


--3646
  USE [operationdb]
GO

/****** Object:  StoredProcedure [dbo].[ExportProperty]    Script Date: 2019/2/22/周五 12:32:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:        <hsb>
-- Create date: <20180906>
-- Description:    <物业分期数据>
-- =============================================
ALTER PROCEDURE [dbo].[ExportReNameProc]
    --@startTime varchar(100),
    --@endTime varchar(100)
AS
BEGIN

    BEGIN
        --责任盘数据
SELECT
   A.id 分期ID
  ,B.commName 物业名称
  ,A.reName 分期名称
  ,C.houseSeatNum 楼栋数
  ,T.小区户数 户数
  ,D.有号码户数 有号码户数
  ,T.小区户数-D.有号码户数 没号码户数
  ,Q.deptName 所属区域
  ,E.HouseNum AB类数
  ,C.realAddr 楼盘实际地址
  ,C.propAddr 楼盘产权地址
  ,F.名称 小区专家
  ,P.名称 商圈
  ,C.floatArea 占地面积
  ,C.buildArea 建筑面积
  ,C.houseSeatNum     住宅栋数
  ,C.StoreSeatNum 商业栋数
  ,C.landDueYear 土地使用年限
  ,C.landBelong 土地权属
  ,C.landUseMethod 土地使用方式
  ,C.landLevel 土地级别
  ,C.developerCompany 开发商
  ,C.designCompany 建筑设计公司
  ,C.buildYear 建成年代
  ,C.buildCompany 承建公司
  ,C.HouseDeliveryTime 交房时间
  ,C.testifyTime 出证时间
  ,C.openingAvgPrice 开盘均价
  ,C.assessedPrice 评估价
  ,C.markeyPrice 市场指导价
  ,C.plotRatio 容积率
  ,C.greeningRate 绿化率
  ,C.merit 小区优点
  ,C.defect 小区缺点
  ,C.tradingOwnership 交易权属
  ,C.isSealing 封闭
  ,C.truckSpace 车位数
  ,C.truckSpaceRatio 车位配比
  ,C.parkingFee 停车费
  ,C.isShunt 人车分流
  ,C.parkingIntro 停车管理简介
  ,C.managerMethod 小区管理方式
  ,C.contactWay 物业电话
  ,C.managerCompany 物业公司
  ,CONVERT(VARCHAR(20),C.managementFee1)+'~'+CONVERT(VARCHAR(20),C.managementFee2) '物业费(区间)'
  ,C.managerAddr 物业办公地点
  ,C.managerComIntro 物业公司介绍
  ,C.managerIntro 小区管理介绍
  ,H.imgnum 小区图片数
  ,C.communityIntro 小区介绍
  ,C.safetyIntro 安全防范简介
  ,C.assortIntro 小区配套简介
  ,C.asmosphereIntro 居住氛围简介
  ,C.environmentIntro 内部环境简介
  ,C.ownerIntro 业主特征简介
  ,C.tenantIntro 租客特点简介
  ,K.名称 学区房


  FROM [dbo].[ReNameTbl] A WITH(NOLOCK)
  LEFT JOIN [dbo].[CommunityNameTbl] B WITH(NOLOCK) on A.commId=B.id and B.del=0
  LEFT JOIN [dbo].[ReInfoTbl] C WITH(NOLOCK) on A.id=C.reId 

  LEFT JOIN (select B.reID,COUNT(1) 有号码户数  from  UnitNameTbl A
            left join  HouseInfoTbl B  on A.id=B.unitId  and B.del=0
            where A.del=0 and B.id is not null 
            group by B.reID) D  on A.id=D.reID

  LEFT JOIN (  select B.reName, COUNT(1) HouseNum from [dbo].[HouseInfoTbl]  A
       LEFT JOIN [dbo].[ReNameTbl] B  WITH(NOLOCK) on A.reID=B.id  
       where A.houseRank in(1,2)  and B.del=0 and A.del=0
       group by  A.reID,B.reName) E  on A.reName=E.reName

  LEFT JOIN (select D.id,SUM(D.楼住户数) 小区户数 from(
             SELECT B.id,A.floorNum*A.roomNum 楼住户数
             FROM [dbo].[SeatNameTbl] A
             INNER JOIN dbo.ReNameTbl B ON A.reId=B.id and B.del=0
             where A.del=0 ) D
       group by D.id) T on A.id=T.id

--==========小区专家====
  LEFT JOIN (
  select 
    J.reID, 
    名称 = (
        stuff((select '，' + CONVERT(VARCHAR(100),perAllInfo) from (  select A.reID,B.perAllInfo from CommReExpertTbl  A 
 left join dbo.PerAllInfoTbl B on A.perID=B.perid  and B.isDel=0
 where A.isDel=0 
                                                            ) t where t.reID = J.reID 
        for xml path('')),1,1,'')
           ) 
from (  select A.reID,B.perAllInfo from CommReExpertTbl  A 
 left join dbo.PerAllInfoTbl B on A.perID=B.perid  and B.isDel=0
 where A.isDel=0 ) as J 
group by J.reID
) F on A.id=F.reID 




  LEFT JOIN ( select A.reName,COUNT(A.reName) imgnum from [ReNameTbl] A
              left join  [dbo].[CommImgTbl]  B on A.id=B.dataId
              where B.typeId=2 and B.isDel=0 and A.del=0
              group by A.reName) H   on A.reName=H.reName

--    --==========学区房=============

  LEFT JOIN (select 
    I.id, 
    名称 = (
        stuff((select '|' + CONVERT(VARCHAR(200),name)+'('
        +J.名称
        +')' from (select A.id,C.name from [ReNameTbl] A   
                                                            left join [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0
                                                            left join  [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0
                                                            where  A.del=0 
                                                            group by A.id,C.name
                                                            ) t where t.id = I.id 
        for xml path('')),1,1,'')
           ) 
from (select A.id ,C.name  from [ReNameTbl] A   
      left join  [dbo].[CommReSchoolTbl] B on A.id=B.reID and B.isDel=0
      left join  [dbo].[FixedSchoolNameTbl] C on B.schoolID=C.id and C.del=0
      where  A.del=0 
      group by A.id,C.name) as I 
      LEFT join (select J.reid,J.名称
from(
  select 
    J.reid,
    名称 = (
        stuff((select '，' + CONVERT(VARCHAR(20),seatName) from ( select A.reID,D.seatname from CommReSchoolTbl A 
left join FixedSchoolNameTbl B on A.schoolID=B.id AND B.del=0 
left join CommSeatSchoolTbl C on A.id = C.rsID AND C.isDel=0 
inner join SeatNameTbl D on D.id = C.seatID AND D.del=0
where A.isDel=0 
                                                            ) t where t.reid = J.reid 
        for xml path('')),1,1,'')
           ) 
from ( select A.reID from CommReSchoolTbl A 
left join FixedSchoolNameTbl B on A.schoolID=B.id AND B.del=0 
left join CommSeatSchoolTbl C on A.id = C.rsID AND C.isDel=0 
inner join SeatNameTbl D on D.id = C.seatID AND D.del=0
where A.isDel=0   ) as J 
group by J.reid
) J) J on I.id=J.reID
group by I.id,J.名称
) K on A.id=K.id

----===========商圈===========
 LEFT JOIN ( 
    select 
    J.reId,
    名称 = (
        stuff((select '，' + CONVERT(VARCHAR(200),name) from ( select A.reID, B.name from commrebusinessdisttbl A
                                                             left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id  and B.isDel=0
                                                             where  A.isDel=0
                                                            ) t where t.reId = J.reId 
        for xml path('')),1,1,'')
           ) 
    from ( select A.reID, B.name from commrebusinessdisttbl A
           left join AccountManager..BaseBusinessDistrictTbl B on A.businessDistId=B.id  and B.isDel=0
           where  A.isDel=0 )  J
    group by J.reId) P on A.id=P.reId

  LEFT JOIN (select B.id,C.deptName from DutyAllotInfoTbl  A
             left join ReNameTbl B ON A.dataId2=B.id and B.del=0
             left join accountmanager..Base_Department_Tbl C On A.allotId=C.id and C.del=0
             where A.isAllot=1 AND A.daType=2 and A.isDel=0) Q on A.id=Q.id

 where A.del=0  


        



    END
END




GO