oracle中自定义函数某字段多值匹配，涉及行转列，列转行等

主表中某一字段为表2 id(主键) 逗号组合，形成一对多关系，同时针对部分关联到的字段求和。
因为sql语句在程序中会拼接order by 语句。所以sql未出现order by 看上去显得繁琐
解决方法：
1，函数将字符串转成列表，再用in求和
2，stuff函数拼接，或函数转换。
使用到的内置函数：
substring(str,start,end), 截取长度
charindex(split,str,start),在str中，从start位置开始查找split分隔符的位置
len(str),str的长度
ltrim(str),去掉左侧空格
rtrim(str)，去掉右侧空格
trim(str)，去掉左右两侧空格 (只适用mysql数据库)
sum(x)，求和
struff
for xml path()


第一步：拼接站名，函数
逻辑关系：使用‘，’分隔字段，循环，从表二查询，赋给变量，再用‘，’重新拼接

ALTER FUNCTION SUMNEMBER
(@str nvarchar(max))
returns nvarchar(max)--返回构造好的[ennm]
AS 
BEGIN 
DECLARE @Result nvarchar(max)
DECLARE @UserName nvarchar(64)
DECLARE @Pos int
DECLARE @NextPos int
declare @id int
declare @count1 int
SET @Pos = 1 
SET @Result=''

 WHILE(@Pos <= LEN(@str)) 
 BEGIN 
  SELECT @NextPos = CHARINDEX(',', @str, @Pos)
  IF (@NextPos = 0 OR @NextPos IS NULL)
    begin
  SELECT @NextPos = LEN(@str) + 1 
    end
    set @id = RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))
    select @count1=count(*) from dbo.t_ycp_viliage where [id]=@id
    if(@count1>0)
     begin 

    SELECT @UserName=[ennm] FROM [dbo].[t_ycp_viliage] WHERE [id]=@id        
  SET @Result = @Result + @UserName +','
    end
  SELECT @Pos = @NextPos+1 
 END
 if(len(@Result)>0)
 set @Result = SUBSTRING(@Result, 1, len(@Result)-1)
RETURN @Result
END


第二步：使用stuff等内置函数拼接（一二步方法不一样，性质差不多）
jczname = stuff((select ','+stnm from IRRB_STINFO_B where CHARINDEX(','+CONVERT(VARCHAR,IRRB_STINFO_B.id),','+a.jczid)>0 for xml path('')),1,1,'')

ALTER FUNCTION StrToTable(@str varchar(100))
Returns @tableName table
(
    str2table varchar(50)
)
As
--该函数用于把一个逗号分割的多个数据字符串变成一个表的一列
Begin
set @str = @str +','
Declare @insertStr varchar(10) --截取后的第一个字符
Declare @newstr varchar(100)  --截取第一个字符串剩余字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,CHARINDEX(',', @str),'')
Insert @tableName VALUES(@insertStr)
while(len(@newstr)>0)
begin
    set @insertStr = left(@newstr,CHARINDEX(',', @newstr)-1)
    insert @tableName values(@insertStr)
    set @newstr = stuff(@newstr,1,CHARINDEX(',', @newstr),'')
    
end
Return
end



第三步：编写字段转为列的函数


select a.*,dbo.SUMNEMBER(a.xcid) xcname,jczname = stuff((select ','+stnm from IRRB_STINFO_B where CHARINDEX(','+CONVERT(VARCHAR,IRRB_STINFO_B.id),','+a.jczid)>0 for xml path('')),1,1,''),(select sum(b.zrk) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) population,(select sum(b.jths) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) household,(select sum(b.fws) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) house,(select sum(b.lshsxxrk) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) hulpopulation,(select sum(b.lshsxxjths) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) hulhousehold,(select sum(b.lshsxxfws) from t_ycp_viliage b where b.id in ( select str2table from StrToTable(a.xcid) ) ) hulhouse from t_ycp_xhlyqk a