RICHEER COCA 2022-05-08 11:46 采纳率: 93.9%
浏览 51
已结题

如何准确统计各小组的总成绩,请专家答疑解惑

-- 各个选手的成绩表,WID是各个选手的参赛ID,WValue是各自的成绩;
DROP TABLE IF  EXISTS  #TT1
CREATE TABLE #TT1(
[ID] [int] IDENTITY(1,1) NOT NULL,[WID] [int] NULL,[WValue] [int] NULL
)    

INSERT INTO  #TT1
SELECT '1','2'
UNION ALL SELECT '2','12'
UNION ALL SELECT '3','8'
UNION ALL SELECT '4','4'
UNION ALL SELECT '5','11'
UNION ALL SELECT '6','4'
UNION ALL SELECT '7','3'
UNION ALL SELECT '8','2'
UNION ALL SELECT '9','3'
UNION ALL SELECT '10','12'
UNION ALL SELECT '11','3'
UNION ALL SELECT '12','9'
UNION ALL SELECT '13','9'
UNION ALL SELECT '14','2'
UNION ALL SELECT '15','11'
UNION ALL SELECT '16','1'
UNION ALL SELECT '17','3'
UNION ALL SELECT '18','6'
UNION ALL SELECT '19','1'
UNION ALL SELECT '20','1'
UNION ALL SELECT '21','5'
UNION ALL SELECT '22','17'
UNION ALL SELECT '23','3'
UNION ALL SELECT '24','3'
UNION ALL SELECT '25','4'
UNION ALL SELECT '26','2'
UNION ALL SELECT '27','16'
UNION ALL SELECT '28','3'
UNION ALL SELECT '29','2'

-- 已知编组,现在需要统计出 每一个编组的总成绩

DROP TABLE IF  EXISTS  #bianzu
CREATE TABLE  #bianzu(
[GroupID] [int] IDENTITY(1,1), N1 int, N2 int, N3 int, N4 int, N5 int, N6 int, N7 int, N8 int, N9 int, N10 int
) 
INSERT INTO #bianzu
SELECT  '1'N1,'3'N2,'7'N3,'17'N4,'19'N5,'21'N6,'24'N7,'25'N8,'26'N9,'29'N10
UNION ALL SELECT'2','13','17','20','21','23','24','25','27','28'
UNION ALL SELECT'7','11','17','18','20','21','22','23','24','27'
UNION ALL SELECT'12','13','17','20','22','23','24','27','28','29'
UNION ALL SELECT'2','3','7','10','12','13','14','17','18','29'

想要得到的结果如下

img

  • 写回答

2条回答 默认 最新

  • shinger126 2022-05-09 11:16
    关注

    -- 各个选手的成绩表,WID是各个选手的参赛ID,WValue是各自的成绩;
    DROP TABLE IF EXISTS #TT1
    CREATE TABLE #TT1(
    [ID] [int] IDENTITY(1,1) NOT NULL,[WID] [int] NULL,[WValue] [int] NULL
    )

    INSERT INTO #TT1
    SELECT '1','2'
    UNION ALL SELECT '2','12'
    UNION ALL SELECT '3','8'
    UNION ALL SELECT '4','4'
    UNION ALL SELECT '5','11'
    UNION ALL SELECT '6','4'
    UNION ALL SELECT '7','3'
    UNION ALL SELECT '8','2'
    UNION ALL SELECT '9','3'
    UNION ALL SELECT '10','12'
    UNION ALL SELECT '11','3'
    UNION ALL SELECT '12','9'
    UNION ALL SELECT '13','9'
    UNION ALL SELECT '14','2'
    UNION ALL SELECT '15','11'
    UNION ALL SELECT '16','1'
    UNION ALL SELECT '17','3'
    UNION ALL SELECT '18','6'
    UNION ALL SELECT '19','1'
    UNION ALL SELECT '20','1'
    UNION ALL SELECT '21','5'
    UNION ALL SELECT '22','17'
    UNION ALL SELECT '23','3'
    UNION ALL SELECT '24','3'
    UNION ALL SELECT '25','4'
    UNION ALL SELECT '26','2'
    UNION ALL SELECT '27','16'
    UNION ALL SELECT '28','3'
    UNION ALL SELECT '29','2'

    -- 10人为一个编组,现在需要统计出 每一个编组的总成绩

    select (WID-1)/10+1 as GroupID,WID,WValue from #TT1
    直接分组统计:
    select (WID-1)/10+1 as GroupID,sum(WValue) as WValue from #TT1
    group by (WID-1)/10+1

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

问题事件

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

悬赏问题

  • ¥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如何调起应用?
  • ¥15 快手小店商家版APP怎么第三方APP跳转到指定用户聊天界面