RICHEER COCA 2022-04-21 11:59 采纳率: 93.9%
浏览 92
已结题

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

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;
  • 写回答

3条回答 默认 最新

  • Hello World, 2022-04-26 16:14
    关注
    
    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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 5月8日
  • 已采纳回答 5月3日
  • 修改了问题 4月28日
  • 修改了问题 4月28日
  • 展开全部

悬赏问题

  • ¥15 MATLAB yalmip gurobi求解器
  • ¥15 关于#stm32#的问题:第一张图是发送数据代码,第二第三张图是接收数据并进行DAC转换的函数,若用串口一接收数据并进行DAC转换,主函数代码该怎么写
  • ¥20 hc130怎么读写内部flash存储信息
  • ¥15 Axure rp9注册与登录交互
  • ¥15 我下载图形界面重启完就变成这样了,打字也打不了,动也动不了,该怎么解决(操作系统-centos)
  • ¥15 VBA中在窗体中遍历所有checkbox控件,提取出被选中的checkbox的caption值
  • ¥15 在Ubuntu上有什么命令,或者是系统文件能告诉我链接nvme ssd的pcie槽位是不是支持热插拔功能?
  • ¥15 ansys license许可证问题
  • ¥20 QQ号和密码都能正常登录微信 QQ号和密码登录微信显示密码错误
  • ¥15 单片机RTOS Kernel与应用分离开发,Kernel如何调起应用?