采纳后 谢送 答主一杯105元拿铁咖啡

Question

QY_RID代表班级，
三个班级选手分别是
F1班级选手21名，WID是各自的参赛ID,WValue是各自的成绩；
M1班级选手17名，WID是各自的参赛ID,WValue是各自的成绩；
L1班级选手22名，WID是各自的参赛ID,WValue是各自的成绩；
根据成绩统计表#tb，选出20人参加下一轮比赛，
挑选规则A：F1班可以选 4 到 11人，M1班可以选 4到11人，L1班可以选 4 到11人，总的人数为20人，
挑选规则B：这20人的成绩总和要求为63~~99。 其中
F1班选 4 到 11人的成绩总和要求为17 到 22。
L1班选 4 到 11人的成绩总和要求为44 到49。
M1班选 4 到 11人的成绩总和要求为13~到15。

挑选规则C：WValue 设定取值范围为 [2，8]，选取 设定取值WValue的个数不超过15个，而且个数不小于10。
问题：,
按照条件（挑选规则A、B和C），选出20人参加下一轮比赛，请问如何完成挑选规则B ？

IF object_id('[tempdb]..#tb') is not null drop table #tb;
go
create table #tb (WID INT,WValue INT,QY_RID nvarchar(5) )
go
insert into #tb ---   select* from  #tb ORDER BY QY_RID 
select 1,2,'L1'UNION all select 2,5,'F1'UNION all select 3,3,'F1'UNION all select 4,8,'L1'UNION all select 5,3,'M1'UNION all select 6,2,'L1'
UNION all select 7,3,'M1'UNION all select 8,3,'L1'UNION all select 9,2,'F1'UNION all select 10,2,'F1'UNION all select 11,13,'M1'UNION all select 12,2,'F1'
UNION all select 13,5,'F1'UNION all select 15,11,'F1'UNION all select 16,10,'F1'UNION all select 19,3,'F1'UNION all select 20,5,'L1'UNION all select 21,4,'M1'
UNION all select 22,11,'L1'UNION all select 24,3,'L1'UNION all select 25,5,'L1'UNION all select 26,3,'L1'UNION all select 27,3,'F1'UNION all select 28,2,'L1'
UNION all select 29,12,'L1'UNION all select 32,4,'M1'UNION all select 33,7,'L1'UNION all select 34,11,'F1'UNION all select 35,11,'L1'UNION all select 36,4,'M1'
UNION all select 38,6,'L1'UNION all select 39,4,'M1'UNION all select 41,3,'M1'UNION all select 43,2,'F1'UNION all select 44,7,'M1'UNION all select 45,2,'L1'
UNION all select 46,7,'L1'UNION all select 47,8,'L1'UNION all select 50,6,'F1'UNION all select 51,8,'F1'UNION all select 54,5,'F1'UNION all select 56,3,'M1'
UNION all select 58,12,'L1'UNION all select 59,2,'F1'UNION all select 61,2,'M1'UNION all select 62,8,'F1'UNION all select 63,2,'F1'UNION all select 65,6,'M1'
UNION all select 66,2,'M1'UNION all select 67,2,'F1'UNION all select 68,2,'L1'UNION all select 69,4,'M1'UNION all select 70,2,'L1'UNION all select 71,9,'M1'
UNION all select 72,4,'L1'UNION all select 73,3,'M1'UNION all select 74,5,'L1'UNION all select 75,3,'F1'UNION all select 78,4,'F1'UNION all select 79,2,'M1'

DECLARE @times INT = 0; 
DECLARE @GroupID INT = 1;
DECLARE @Result TABLE(GroupId INT,WID INT, KID VARCHAR(5))
 
Start:
    DROP TABLE IF EXISTS #Result;
    DECLARE @Top10To15 INT=RAND()*6+10--前10到15个随机
    --2到8分的数据
    SELECT TOP(@Top10To15) * INTO #Result FROM #tb WHERE WValue BETWEEN 2 AND 8 ORDER BY NEWID()
 
    --不在2到8分的数据，补足20个 
    INSERT #Result ( WID, WValue, QY_RID)
    SELECT  TOP (20 - @Top10To15)  WID, WValue, QY_RID FROM  #tb WHERE WValue NOT BETWEEN 2 AND 8 ORDER BY NEWID();
 
    IF EXISTS (   SELECT    1
                  FROM      #Result
                  HAVING    SUM(WValue) BETWEEN 78 AND 78 --分数63到99区间
                            AND SUM(CASE WHEN QY_RID = 'F1' THEN 1 ELSE 0 END) BETWEEN 7 AND 7-- F1班拟定选中7个队员
                            AND SUM(CASE WHEN QY_RID = 'L1' THEN 1 ELSE 0 END) BETWEEN 5 AND 5-- L1班拟定选中5个队员
                            AND SUM(CASE WHEN QY_RID = 'M1' THEN 1 ELSE 0 END) BETWEEN 8 AND 8-- F1班拟定选中8个队员
                            --AND SUM(WValue) EXISTS (select  sum(WVALUE) from #tb  where QY_RID='F1')-- F1班选中7个队员的分数和 17到18区间
                            --AND SUM(WValue) EXISTS (select  sum(WVALUE) from #tb  where QY_RID='L1')-- L1班选中5个队员的分数和 48到48区间
                            --AND SUM(WValue) EXISTS (select  sum(WVALUE) from #tb  where QY_RID='M1')-- F1班选中8个队员的分数和 13到113区间
                            
                            ) -- 每组可取4到11个人，但总的的人数为20
        BEGIN
            INSERT @Result (GroupId, WID, KID)        
            SELECT @GroupID, WID, 'N' + CAST(ROW_NUMBER() OVER (ORDER BY WID) AS VARCHAR(5)) AS KID FROM  #Result;
 
            SET @GroupID=@GroupID+1;
            IF @GroupID>20
                BEGIN
                    SELECT * FROM  @Result PIVOT (MIN(WID) FOR KID IN (N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20)) p
                    RETURN
                END
            ELSE
                GOTO Start;
        END;
    ELSE
        GOTO Start;

Answer 1

IF object_id('[tempdb]..#tb') is not null drop table #tb;
DROP TABLE IF EXISTS #ListWithSeq
DROP TABLE IF EXISTS #Result
go
create table #tb (WID INT,WValue INT,QY_RID nvarchar(5) )
go
insert into #tb 
select 2,16,'F1' union all select 3,2,'F1' union all select 9,7,'F1' union all select 12,13,'F1' union all select 13,13,'F1'
UNION all select 15,5,'F1' union all select 16,4,'F1' union all select 19,2,'F1' union all select 23,3,'F1' union all select 27,1,'F1'
UNION all select 30,1,'F1' union all select 34,5,'F1' union all select 43,1,'F1' union all select 44,1,'F1' union all select 48,4,'F1'
UNION all select 50,6,'F1' union all select 51,2,'F1' union all select 52,1,'F1' union all select 53,1,'F1' union all select 54,8,'F1'
UNION all select 59,9,'F1' union all select 1,2,'L1' union all select 4,2,'L1' union all select 6,4,'L1' union all select 8,2,'L1'
UNION all select 18,10,'L1' union all select 20,5,'L1' union all select 22,5,'L1' union all select 24,1,'L1' union all select 25,3,'L1'
UNION all select 26,2,'L1' union all select 28,7,'L1' union all select 29,6,'L1' union all select 31,2,'L1' union all select 33,1,'L1'
UNION all select 35,5,'L1' union all select 38,2,'L1' union all select 45,4,'L1' union all select 46,1,'L1' union all select 47,2,'L1'
UNION all select 55,2,'L1' union all select 58,6,'L1' union all select 5,1,'M1' union all select 7,7,'M1' union all select 10,1,'M1'
UNION all select 11,7,'M1' union all select 14,1,'M1' union all select 17,7,'M1' union all select 21,1,'M1' union all select 32,6,'M1'
UNION all select 36,4,'M1' union all select 37,2,'M1' union all select 39,4,'M1' union all select 40,11,'M1' union all select 41,3,'M1'
UNION all select 42,1,'M1' union all select 49,1,'M1' union all select 56,17,'M1' union all select 57,6,'M1' union all select 60,1,'M1'
UNION all select 61,6,'M1'

SELECT ROW_NUMBER() OVER(ORDER BY WID) Seq, * INTO #ListWithSeq FROM #tb

Start:
DECLARE @Top10To15 INT=RAND()*6+10--前10到15个随机
--2到8分的数据
SELECT TOP(@Top10To15) * INTO #Result FROM #ListWithSeq WHERE WValue BETWEEN 2 AND 8 ORDER BY NEWID()


INSERT #Result (Seq, WID, WValue, QY_RID)
SELECT  TOP (20 - @Top10To15)   Seq, WID, WValue, QY_RID FROM  #ListWithSeq WHERE WValue NOT BETWEEN 2 AND 8
ORDER BY NEWID();

IF EXISTS (   SELECT    1
              FROM      #Result
              HAVING     SUM(WValue) BETWEEN 63 AND 99--分数63到99区间
                        AND   SUM(CASE WHEN QY_RID = 'F1' THEN 1 ELSE 0 END) BETWEEN 5 AND 8
                        AND  SUM(CASE WHEN QY_RID = 'L1' THEN 1 ELSE 0 END) BETWEEN 5 AND 8
                        AND  SUM(CASE WHEN QY_RID = 'M1' THEN 1 ELSE 0 END) BETWEEN 5 AND 8) --每组取5到8个人，原给出的人数最少值已经超过20
    SELECT  * FROM  #Result;
ELSE
    BEGIN
        DROP TABLE IF EXISTS #Result;
        DROP TABLE IF EXISTS #List2To8;
        GOTO Start;
    END;