关于#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个数字算一组。
大概的思路是优先借出匹配项最多的，循环处理，直到找不到为止。
下面的代码有点乱，仅供参考。
有些函数（比如string_split）要新版本才能使用，自己替换成自定义函数吧
--- 数据表#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;
有点没看懂问题描述，是要找出满足符合条件的全部结果集合吗？
比如简化版tb1有三个数1 2 3
简化版tb2
①1 2 4
②2 3 4
③1 3 4
统计结果一：包含1、2的记录——①、①②、①③、①②③、②③
统计结果二：包含1、2、3的记录——①②、①③、①②③
是这样吗？
随机凑齐就算还是要顺序凑，或者是用最少记录凑？
找出一个解还是全部解？