RICHEER COCA 2021-10-20 15:45 采纳率: 93.9%
浏览 77
已结题

关于#sql#统计两个数据表的问题,请各位专家解答!


 --- 数据表#tb1
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
create table #tb1(sno varchar(10),[n1] int null,[n2] int null,[n3] int null,[n4] int null,[n5] int null,[n6] int null,[n7] int null,[n8] int null,[n9] int null,[n10] int null,[n11] int null,[n12] int null,[n13] int null,[n14] int null,[n15] int null,[n16] int null,[n17] int null,[n18] int null,[n19] int null,[n20] int null,[n21] int null,[n22] int null,[n23] int null,[n24] int null,[n25] int null,[n26] int null,[n27] int null,[n28] int null,[n29] int null,[n30] int null,[n31] int null,[n32] int null,[n33] int null)
go
insert #tb1
select'1003321','14','1','26','22','32','6','17','20','18','8','7','27','2','9','10','4','19','30','13','12','5','3','16','11','25','15','29','23','21','31','24','28','33'

 --- 数据表#tb2
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 
go 
create table #tb2(NoText varchar(20))
go
insert #tb2
select'04 05 09 13 17 29'union all
select'01 07 08 22 32 33'union all
select'10 11 12 19 22 27'union all
select'27 28 29 30 31 32'union all
select'02 12 14 18 19 31'union all
select'07 14 15 23 31 33'union all
select'22 23 24 25 26 27'union all
select'03 09 16 24 28 29'union all
select'20 22 23 24 25 26'union all
select'04 05 06 15 26 27'union all
select'19 20 21 22 23 24'union all
select'04 13 21 22 23 30'union all
select'11 12 13 14 15 16'union all
select'10 11 17 20 25 30'union all
select'28 29 30 31 32 33'union all
select'03 07 18 20 23 28'union all
select'01 06 15 16 24 32'union all
select'02 08 21 25 26 30' 

---用SQL代码找出满足条件的全部结果集合
统计结果一 表#tb2的记录 ,包含了表#tb1里n1到n19的所有数字,凑满19个数字算一组。

统计结果二 表#tb2的记录 , 包含了表#tb1里n1到n33的所有数字,凑满33个数字算一组。

img

  • 写回答

3条回答 默认 最新

  • Hello World, 2021-10-22 11:16
    关注

    大概的思路是优先借出匹配项最多的,循环处理,直到找不到为止。
    下面的代码有点乱,仅供参考。
    有些函数(比如string_split)要新版本才能使用,自己替换成自定义函数吧

    img

     
     --- 数据表#tb1
    if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
    create table #tb1(sno varchar(10),[n1] int null,[n2] int null,[n3] int null,[n4] int null,[n5] int null,[n6] int null,[n7] int null,[n8] int null,[n9] int null,[n10] int null,[n11] int null,[n12] int null,[n13] int null,[n14] int null,[n15] int null,[n16] int null,[n17] int null,[n18] int null,[n19] int null,[n20] int null,[n21] int null,[n22] int null,[n23] int null,[n24] int null,[n25] int null,[n26] int null,[n27] int null,[n28] int null,[n29] int null,[n30] int null,[n31] int null,[n32] int null,[n33] int null)
    go
    insert #tb1
    select'1003321','14','1','26','22','32','6','17','20','18','8','7','27','2','9','10','4','19','30','13','12','5','3','16','11','25','15','29','23','21','31','24','28','33'
     --- 数据表#tb2
    if object_id('tempdb.dbo.#tb2') is not null drop table #tb2 
    go 
    create table #tb2(NoText varchar(20))
    go
    insert #tb2
    select'04 05 09 13 17 29'union all
    select'01 07 08 22 32 33'union all
    select'10 11 12 19 22 27'union all
    select'27 28 29 30 31 32'union all
    select'02 12 14 18 19 31'union all
    select'07 14 15 23 31 33'union all
    select'22 23 24 25 26 27'union all
    select'03 09 16 24 28 29'union all
    select'20 22 23 24 25 26'union all
    select'04 05 06 15 26 27'union all
    select'19 20 21 22 23 24'union all
    select'04 13 21 22 23 30'union all
    select'11 12 13 14 15 16'union all
    select'10 11 17 20 25 30'union all
    select'28 29 30 31 32 33'union all
    select'03 07 18 20 23 28'union all
    select'01 06 15 16 24 32'union all
    select'02 08 21 25 26 30' 
    
    DECLARE @i INT=1,@sql NVARCHAR(MAX), @str1 VARCHAR(100)='',@str2 VARCHAR(100)=''
    
    WHILE @i<34
    BEGIN
        SET @sql='select @str1 =@str1 + '' '' +  RIGHT(''0'' + cast(n' + CAST(@i AS VARCHAR(2)) + ' as varchar(2)),2),@str2 =case when @i<20 then @str2 + '' '' +  RIGHT(''0'' + cast(n' + CAST(@i AS VARCHAR(2)) + ' as varchar(2)),2) else @str2 end from #tb1'
        --PRINT @sql
        EXEC sp_executesql @sql, N'@i int, @str1 VARCHAR(100) output, @str2 varchar(100) output',@i, @str1 OUTPUT,@str2 OUT --此处必须加上ouput,不然无法取到值
    
        SET @i=@i+1
    END
    
    DECLARE @tb2 TABLE (NoText varchar(20),Val VARCHAR(10), IsExixts INT,ExixtsCount INT)
    DECLARE @result2 TABLE(GroupID INT, NoText VARCHAR(20))
    --SELECT @str1,@str2
    DECLARE @selectItem VARCHAR(20),@GroupID INT=1;
    
    INSERT  @tb2
        (
            NoText, Val, IsExixts, ExixtsCount
        )
    SELECT  a.NoText,
            b.value,
            CASE WHEN CHARINDEX(b.value, @str2) > 0 THEN 1 ELSE 0 END AS IsExixts,
            SUM(CASE WHEN CHARINDEX(b.value, @str2) > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY a.NoText) ExixtsCount
    FROM    #tb2 a
            OUTER APPLY (SELECT * FROM STRING_SPLIT(a.NoText, ' ')) b;
    
    WHILE EXISTS (SELECT    * FROM @tb2 WHERE  CHARINDEX(Val, @str2) > 0)
        BEGIN
            DECLARE @str VARCHAR(100);
            SET @str = @str1;--这里可以换成@str2则计算19个数字的
            UPDATE  b
            SET     b.IsExixts = a.IsExixts, b.ExixtsCount = a.ExixtsCount
            FROM    (   SELECT  NoText,
                                Val,
                                IsExixts = CASE WHEN CHARINDEX(Val, @str) > 0 THEN 1 ELSE 0 END,
                                ExixtsCount = SUM(CASE WHEN CHARINDEX(Val, @str) > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NoText)
                        FROM    @tb2) a
                    INNER JOIN @tb2 b ON b.NoText = a.NoText AND   b.Val = a.Val;
    
            --SELECT 'str is: ' + @str;
            --SELECT * FROM @tb2
            WHILE EXISTS (SELECT    * FROM @tb2 WHERE  IsExixts = 1) AND   @str <> ''
                BEGIN
                    --选择最多匹配的项        
                    SELECT  TOP (1) @selectItem = NoText FROM   @tb2 ORDER BY   ExixtsCount DESC;
    
                    --SELECT  @selectItem;
                    --SELECT  * FROM  @tb2 WHERE  NoText = @selectItem;
                    --删除已经匹配的数字
                    UPDATE  a SET   @str = REPLACE(@str, ' ' + a.Val, '')FROM   @tb2 a WHERE NoText = @selectItem;
                    --SELECT  @str;
                    --删除已经匹配的项
                    DELETE  FROM @tb2 WHERE NoText = @selectItem;
                    --记录已经选中的项
                    INSERT  @result2 (GroupID, NoText) VALUES (@GroupID, @selectItem);
                    UPDATE  b
                    SET     b.IsExixts = a.IsExixts, b.ExixtsCount = a.ExixtsCount
                    FROM    (   SELECT  NoText,
                                        Val,
                                        IsExixts = CASE WHEN CHARINDEX(Val, @str) > 0 THEN 1 ELSE 0 END,
                                        ExixtsCount = SUM(CASE WHEN CHARINDEX(Val, @str) > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NoText)
                                FROM    @tb2) a
                            INNER JOIN @tb2 b ON b.NoText = a.NoText AND   b.Val = a.Val;
    
                --SELECT  * FROM  @tb2;
                END;
            IF @str <> ''
                BEGIN
                    DELETE  FROM @result2 WHERE GroupID = @GroupID;
                    GOTO lblEnd;
                END;
            ELSE
                SET @GroupID = @GroupID + 1;
        END;
    
    lblEnd:        SELECT  * FROM  @result2;
    
    
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

问题事件

  • 系统已结题 10月31日
  • 已采纳回答 10月23日
  • 修改了问题 10月21日
  • 修改了问题 10月21日
  • 展开全部

悬赏问题

  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题
  • ¥15 主机可以ping通路由器但是连不上网怎么办
  • ¥15 数据库一张以时间排好序的表中,找出多次相邻的那些行
  • ¥50 关于DynamoRIO处理多线程程序时候的问题
  • ¥15 kubeadm部署k8s出错
  • ¥15 Abaqus打不开cae文件怎么办?
  • ¥15 小程序准备上线,软件开发公司需要提供哪些资料给甲方