cross apply和表值函数进行关联查询

最新推荐文章于 2025-01-06 15:08:38 发布

KeepSayingNo

最新推荐文章于 2025-01-06 15:08:38 发布

阅读量4.8k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库 MS Sql Server技术分享文章标签： SQL cross apply split 表值函数字符串分割

本文链接：https://blog.youkuaiyun.com/dotnetstudio/article/details/22928977

数据库同时被 2 个专栏收录

20 篇文章

订阅专栏

MS Sql Server技术分享

20 篇文章

订阅专栏

本文介绍了一种通过SQL函数实现的将员工多个爱好字段拆分为单独记录的方法，以便于进行统计分析。提供了两种实用的字符串分割函数及应用示例。

目前场景是有一个兴趣爱好类型表和一个员工爱好表，员工爱好表中的爱好一列，如果这个员工有多个爱好，那么就将这些爱好放到一条记录当中，爱好之间用逗号隔开，现在需求是要将每个员工的这些爱好分隔成一条条记录查询出来，从而可以例如做员工爱好统计等应用。

爱好类型表 t_a

员工爱好表 t_b

需要的结果是

创建一个分隔字符串的表值函数

create function [dbo].[splitIDs](
    @Ids nvarchar(1000),
    @split_char nvarchar(10)
)
returns @t_id TABLE (id bigint)
as
begin
    declare @i int,@j int,@l int,@v bigint;
    set @i = 0;
    set @j = 0;
    set @l = len(@Ids);
    while(@j < @l)
    begin
       set @j = charindex(@split_char,@Ids,@i+1);
       if(@j = 0) set @j = @l+1;
       set @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as bigint);
       INSERT INTO @t_id VALUES(@v)
       set @i = @j;
    end
    return;
end

GO

采用下面的语句即可得到期望的结果

select 
    aid = t_a.id
    ,aname = t_a.name
    ,bid = t_b.id,t_b.name
from t_b
cross apply splitIDs(a_ids,',') tbl_Ids
INNER JOIN t_a ON tbl_Ids .id = t_a.id

分割字符串函数的另一种写法

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 05/08/2014 14:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   FUNCTION [dbo].[Split]   
(   
@c VARCHAR(MAX) ,   
@split VARCHAR(50)   
)   
RETURNS @t TABLE ( col VARCHAR(50) )   
AS  
BEGIN  
    WHILE ( CHARINDEX(@split, @c) <> 0 )   
        BEGIN  
            INSERT  @t( col )   
            VALUES  ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1) )   
            SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')   
        END  
    INSERT  @t( col ) VALUES  ( @c )   
    RETURN  
END

-------------------------------END OF FUNCTION----------------------------------------------

使用方法

select * from Split('1,2,3',',')