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 intset @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='激活'