SQL多表联查、自定义函数（字符串分割split）、xml格式输出

记录一个报表的统计，大概内容如下：

多表联查涉及的报表有：房间表、买家表、合同表、交易表、费用表、修改记录表

注意：本项目数据库使用的是sqlserver（mssql），非mysql。

难点1:业主信息（身份证号）存在合同表中，需要通过“;”分割后，把相的联系方式或通讯地址合并显示在一个字段里；

1、创建split函数

--说明：
ALTER function [dbo].[fn_Split](
@String nvarchar (max),
@Delimiter nvarchar (10)
)
RETURNS @ValueTable TABLE ([Value] NVARCHAR(4000),[id] int)
BEGIN
DECLARE @NextString nvarchar(max),
@Pos int,
@NextPos int,
@CommaCheck nvarchar(1),
   @id int

set @id=1

SET @NextString = ''
SET @CommaCheck = right(@String,1)

SET @String = @String + @Delimiter

SET @Pos = CHARINDEX(@Delimiter,@String)
SET @NextPos = 1

WHILE (@pos <> 0)
BEGIN
SET @NextString = SUBSTRING(@String,1,@Pos - 1)

INSERT INTO @ValueTable ( [Value],[id]) VALUES (@NextString,@id)

SET @String = SUBSTRING(@String,@pos +1,LEN(@String))

SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter,@String)

   set @id = @id +1
END

RETURN
END

该函数返回的对象是table，值是value和id。

2、调用split函数

SELECT * FROM dbo.fn_Split('441900197605163536;513821199002186427', ';')

运行效果如下：

调用函数时要注意使用方法，不是select dbo.split()，而是select * from dbo.split()

3、实际运用

把合同表中的身份证号分割出来，多表查询，重复的手机号做重复排查

SELECT DISTINCT Tel+';' FROM dbo.fn_Split('身份证号1;身份证号2',';') b LEFT JOIN s_Buyer a ON a.CardID=b.value for xml path('')

这样就能实现符号分割字符串，再通过for xml path('')把查询结果合并。

难点2:房间可能存在多次认购、取消认购、签署等业务状态，要把该房间的所有状态全部取出来放在一个备注字段；

使用for xml path('')把查询结果合并输出来

详见下面博文：

https://blog.csdn.net/qqqqqwerttwtwe/article/details/144020795

难点3:多表联查（涉及到6个表格）

1、根据实际的业务逻辑，通过左连接（leftjoin）或内连接（innerjoin）查询

2、注意连接条件的唯一性，避免数据重复！

比如RoomGUID是唯一的（一个房间一个）；

但是房子可能有多个合同，每个合同存在多个交易记录；

因此必须要考虑好统计的维度，避免数据重复。

以下是报表的完成SQL代码

SELECT
r.RoomInfo
,CONVERT(VARCHAR(100), t.RGOrderQsDate, 23) AS 认购日期
,c.CstAllName
,c.CstAllCardID
,联系方式=(SELECT DISTINCT Tel+';' FROM dbo.fn_Split(c.CstAllCardID,';') b LEFT JOIN s_Buyer a ON a.CardID=b.value for xml path(''))
,通讯地址=(SELECT DISTINCT Address+';' FROM dbo.fn_Split(c.CstAllCardID,';') b LEFT JOIN s_Buyer a ON a.CardID=b.value for xml path(''))
,c.payformname
,r.BldArea AS 建筑面积
,r.Total AS [成交总价]
,已收房款=(SELECT SUM(Amount) FROM s_Fee WHERE TradeGUID=t.TradeGUID)
,c.discntremark
,c.DiscntValueText
,CONVERT(VARCHAR(100), c.qsdate, 23) AS 签署日期
,c.agreementno
,c.ajbank
,c.ajtotal
,c.ajyear
,c.zygw
,bz=(select  CONVERT(VARCHAR(100), old.ApplyDate, 23)+''+ old.ApplyType +';'from s_SaleModiApply  oldwhere RoomGUID=r.RoomGUIDfor xml  path(''))
,r.Status
FROM s_Room r LEFT JOIN s_Trade t ON r.RoomGUID=t.RoomGUID
LEFT JOIN s_Contract c ON c.RoomGUID=r.RoomGUID AND c.ContractGUID=t.ContractGUID
WHERE r.Status in ('认购','签约') AND t.TradeStatus='激活'