查找出现的次数

[stevenjin]

表A
姓名，审批人工号

表B
工单编号 审批人工号列表

两表查询，统计出某人在表B出现的次数。输出姓名，次数两个信息。
注：一个工单可能有多人审批。

[二月十六]

--测试数据
if not object_id(N'A') is null
	drop table A
Go
Create table A([姓名] nvarchar(22),[审批人工号] int)
Insert A
select N'张三',1 union all
select N'李四',2
GO
if not object_id(N'B') is null
	drop table B
Go
Create table B([工单号] int,[审批人工号] int)
Insert B
select 1001,1 union all
select 1002,2 union all
select 1003,1
Go
--测试数据结束
SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        B
            ON A.审批人工号 = dbo.B.审批人工号
GROUP BY
    姓名;

[stevenjin]

批人工号是varchar类型，这晨放的是审批人列表呢： 1001 1,2 1002 2 1003 1,2

[二月十六]

引用 2 楼 stevenjin 的回复:

批人工号是varchar类型，这晨放的是审批人列表呢：

1001 1,2
1002 2
1003 1,2

另外一个表的测试数据呢？
把测试数据给全，并写出测试数据对应的结果，不然只能通过你的描述来猜，可能会不准确。

[二月十六]

ALTER FUNCTION dbo.F_Split
 (
     @SplitString nvarchar(max),  --源字符串
     @Separator nvarchar(10)=' '  --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE  --输出的数据表
 (
     [id] int identity(1,1),
     [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);

     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END
GO

--测试数据
if not object_id(N'A') is null
    drop table A
Go
Create table A([姓名] nvarchar(22),[审批人工号] int)
Insert A
select N'张三',1 union all
select N'李四',2
GO
if not object_id(N'B') is null
    drop table B
Go
Create table B([工单号] int,[审批人工号] NVARCHAR(20))
Insert B
select 1001,'1,2' union all
select 1002,'2' union all
select 1003,'1,2'
Go
--测试数据结束
SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B CROSS APPLY(SELECT * FROM dbo.F_Split(审批人工号,','))t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

[RINK_1]

SELECT B.姓名,COUNT(*) AS 次数
FROM A
JOIN B ON ','+A.审批人工号+',' LIKE '%,'+RTRIM(B.审批人工号)+',%'
GROUP BY B.审批人工号,B.姓名

[stevenjin]

我用的是SQL SERVER呢，提示：列名 'value' 无效。 另：我用的函数是你上次给的：f_SplitToNvarchar

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B CROSS APPLY(SELECT * FROM dbo.[f_SplitToNvarchar](审批人工号,','))t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

[二月十六]

引用 6 楼 stevenjin 的回复:

我用的是SQL SERVER呢，提示：列名 'value' 无效。
另：我用的函数是你上次给的：f_SplitToNvarchar

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B CROSS APPLY(SELECT * FROM dbo.[f_SplitToNvarchar](审批人工号,','))t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

额，不记得有过这个啊，用这个吧

ALTER FUNCTION dbo.F_Split
 (
     @SplitString nvarchar(max),  --源字符串
     @Separator nvarchar(10)=' '  --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE  --输出的数据表
 (
     [id] int identity(1,1),
     [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);
 
     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END
GO

[stevenjin]

示例可以了， 但还有一个奇观的现像，如果我在表A加入一个id的字段，会出现：多次为 'b1' 指定了列 'id'。 为什么会这样呀？ 我的表中有id这一列怎么办？

[二月十六]

引用 8 楼 stevenjin 的回复:

示例可以了，
但还有一个奇观的现像，如果我在表A加入一个id的字段，会出现：多次为 'b1' 指定了列 'id'。
为什么会这样呀？
我的表中有id这一列怎么办？

那个函数在处理完数据后会有一列id值，这样试试

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT t.value FROM B CROSS APPLY(SELECT * FROM dbo.F_Split(审批人工号,','))t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

[stevenjin]

不知哪的问题，有的会有多的情况 如果该用户本身就多，sum的次数也更多

[stevenjin]

不好意思，大侠，如果我要对表B做条件过滤怎么办呢，比如查找工单号小于1003的记录。
我这报这个错，“t”附近有语法错误。

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B WHERE 工单号<1003 CROSS APPLY(SELECT * FROM dbo.F_Split(审批人工号,','))t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

[stevenjin]

知道了，我放错位置了：

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B  CROSS APPLY(SELECT * FROM dbo.F_Split(审批人工号,',')  WHERE B.工单号<1003)t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

[二月十六]

引用 12 楼 stevenjin 的回复:

知道了，我放错位置了：

SELECT
    姓名,
    COUNT(1) AS 次数
FROM
    A
    JOIN
        (SELECT * FROM B  CROSS APPLY(SELECT * FROM dbo.F_Split(审批人工号,',')  WHERE B.工单号<1003)t)b1
            ON A.审批人工号 = b1.value
GROUP BY
    姓名;

没问题了记得结贴