当前位置： 文江博客话题详情

T-SQL 根据条件合并记录

发布于 2024-10-07 05:58:34 字数 13392 浏览 0 评论 0原文

在 SQL Server 2008 中，我有下表分数。我想同时显示同一学生的 Score1 和 Score2。姓名和电子邮件组合起来唯一标识学生（姓名或电子邮件也可能丢失，例如 Jack 和 [电子邮件受保护]）。预期输出显示为 T_Combined。

Score

Name    Email   Score1  Score2
John    '[email protected]'    75  
Peter   '[email protected]'   34  
Nina    '[email protected]'    45  
Joseph  '[email protected]'  76  
Tom '[email protected]' 43  
Sam '[email protected]' 76  
Nancy   '[email protected]'   12  
Tina    '[email protected]'    56  
John    '[email protected]' 98  
Peter   '[email protected]'    12  
Nina    '[email protected]' 45  
Joseph  '[email protected]'   87  
Tom '[email protected]'  67  
Sam '[email protected]'  99  
Nancy   '[email protected]'    33  
Tina    '[email protected]' 23  
John    '[email protected]'        86
Peter   '[email protected]'       56
Nina    '[email protected]'        98
Joseph  '[email protected]'      78
Tom '[email protected]'     12
Sam '[email protected]'     45
Nancy   '[email protected]'       76
Tina    '[email protected]'        78
John    '[email protected]'     98
Peter   '[email protected]'        45
Nina    '[email protected]'     76
Joseph  '[email protected]'       12
Tom '[email protected]'      84
Sam '[email protected]'      27
Nancy   '[email protected]'        54
Tina    '[email protected]'     50
Jack        10  
'[email protected]'     20

T_Combined

Name    Email   Score1  Score2
John    '[email protected]'        86
Peter   '[email protected]'       56
Nina    '[email protected]'        98
Joseph  '[email protected]'  78  
Tom '[email protected]' 43  12
Sam '[email protected]' 76  45
Nancy   '[email protected]'   12  
Tina    '[email protected]'    56  
John    '[email protected]' 98  
Peter   '[email protected]'    12  
Nina    '[email protected]' 45  76
Joseph  '[email protected]'   87  12
Tom '[email protected]'  67  84
Sam '[email protected]'  99  27
Nancy   '[email protected]'    33  54
Tina    '[email protected]' 23  50
Jack        10  
'[email protected]'     20

非常感谢

创建表 Score (Name varchar(20),Email varchar(20),Score1 int,Score2 int)

insert into Score (Name,Email,Score1)values('John','[电子邮件受保护]',75) 插入 Score (姓名,电子邮件,Score1)values('Peter','[电子邮件受保护]',34) 插入分数 (姓名,电子邮件,Score1)values('Nina','[电子邮件受保护]',45) 插入 Score (姓名,电子邮件,Score1)values('Joseph','[电子邮件受保护] ]',76) 插入 Score (姓名,电子邮件,Score1)values('Tom','[电子邮件受保护]',43) 插入 Score (姓名,电子邮件,Score1)values('Sam','[电子邮件受保护]',76) 插入 Score (姓名,电子邮件,Score1)values('Nancy','[电子邮件受保护]',12) 插入 Score (姓名,电子邮件,Score1)values('Tina','[电子邮件受保护]',56) 插入 Score (姓名,电子邮件,Score1)values('John','[电子邮件受保护]',98) 插入 Score (姓名,电子邮件,Score1)values('Peter','[电子邮件受保护]',12) 插入分数 (姓名,电子邮件,Score1)values('Nina','[电子邮件受保护]',45) 插入 Score (姓名,电子邮件,Score1)values('Joseph','[电子邮件受保护]',87) 插入 Score (姓名,电子邮件,Score1)values('Tom','[电子邮件受保护]',67) 插入 Score (姓名,电子邮件,Score1)values('Sam','[电子邮件受保护]',99) 插入 Score (姓名,电子邮件,Score1)values('Nancy','[电子邮件受保护]',33) 插入 Score (姓名,电子邮件,Score1)values('Tina','[电子邮件受保护]',23)

插入 Score (姓名,电子邮件,Score2)values('John','[电子邮件受保护]',86) 插入 Score (姓名,电子邮件,Score2)values('Peter','[电子邮件受保护]',56) 插入分数 (姓名,电子邮件,Score2)values('Nina','[电子邮件受保护]',98) 插入 Score (姓名,电子邮件,Score2)values('Joseph','[电子邮件受保护] ]',78) 插入 Score (姓名,电子邮件,Score2)values('Tom','[电子邮件受保护]',12) 插入 Score (姓名,电子邮件,Score2)values('Sam','[电子邮件受保护]',45) 插入分数 (姓名,电子邮件,Score2)values('Nancy','[电子邮件受保护]',76) 插入 Score (姓名,电子邮件,Score2)values('Tina','[电子邮件受保护]',78) 插入 Score (姓名,电子邮件,Score2)values('John','[电子邮件受保护]',98) 插入 Score (姓名,电子邮件,Score2)values('Peter','[电子邮件受保护]',45) 插入分数 (姓名,电子邮件,Score2)values('Nina','[电子邮件受保护]',76) 插入 Score (姓名,电子邮件,Score2)values('Joseph','[电子邮件受保护]',12) 插入 Score (姓名,电子邮件,Score2)values('Tom','[电子邮件受保护]',84) 插入 Score (姓名,电子邮件,Score2)values('Sam','[电子邮件受保护]',27) 插入分数 (姓名,电子邮件,Score2)values('Nancy','[电子邮件受保护]',54) 插入 Score (姓名,电子邮件,Score2)values('Tina','[电子邮件受保护]',50)

插入 Score (Name,Score1)values('Jack',10) 插入 Score (Email,Score2)values('[email protected]' ,20)

原文

In SQL server 2008, I have below table Score. I want to show Score1 and Score2 together for same student. Name and Email combined uniquely identifies a student (Name or Email may be missing too, like Jack and [email protected]). The expected output shown as T_Combined.

Score

Name    Email   Score1  Score2
John    '[email protected]'    75  
Peter   '[email protected]'   34  
Nina    '[email protected]'    45  
Joseph  '[email protected]'  76  
Tom '[email protected]' 43  
Sam '[email protected]' 76  
Nancy   '[email protected]'   12  
Tina    '[email protected]'    56  
John    '[email protected]' 98  
Peter   '[email protected]'    12  
Nina    '[email protected]' 45  
Joseph  '[email protected]'   87  
Tom '[email protected]'  67  
Sam '[email protected]'  99  
Nancy   '[email protected]'    33  
Tina    '[email protected]' 23  
John    '[email protected]'        86
Peter   '[email protected]'       56
Nina    '[email protected]'        98
Joseph  '[email protected]'      78
Tom '[email protected]'     12
Sam '[email protected]'     45
Nancy   '[email protected]'       76
Tina    '[email protected]'        78
John    '[email protected]'     98
Peter   '[email protected]'        45
Nina    '[email protected]'     76
Joseph  '[email protected]'       12
Tom '[email protected]'      84
Sam '[email protected]'      27
Nancy   '[email protected]'        54
Tina    '[email protected]'     50
Jack        10  
'[email protected]'     20

T_Combined

Name    Email   Score1  Score2
John    '[email protected]'        86
Peter   '[email protected]'       56
Nina    '[email protected]'        98
Joseph  '[email protected]'  78  
Tom '[email protected]' 43  12
Sam '[email protected]' 76  45
Nancy   '[email protected]'   12  
Tina    '[email protected]'    56  
John    '[email protected]' 98  
Peter   '[email protected]'    12  
Nina    '[email protected]' 45  76
Joseph  '[email protected]'   87  12
Tom '[email protected]'  67  84
Sam '[email protected]'  99  27
Nancy   '[email protected]'    33  54
Tina    '[email protected]' 23  50
Jack        10  
'[email protected]'     20

Many thanks

Create table Score (Name varchar(20),Email varchar(20),Score1 int,Score2 int)

insert into Score (Name,Email,Score1)values('John','[email protected]',75)
insert into Score (Name,Email,Score1)values('Peter','[email protected]',34)
insert into Score (Name,Email,Score1)values('Nina','[email protected]',45)
insert into Score (Name,Email,Score1)values('Joseph','[email protected]',76)
insert into Score (Name,Email,Score1)values('Tom','[email protected]',43)
insert into Score (Name,Email,Score1)values('Sam','[email protected]',76)
insert into Score (Name,Email,Score1)values('Nancy','[email protected]',12)
insert into Score (Name,Email,Score1)values('Tina','[email protected]',56)
insert into Score (Name,Email,Score1)values('John','[email protected]',98)
insert into Score (Name,Email,Score1)values('Peter','[email protected]',12)
insert into Score (Name,Email,Score1)values('Nina','[email protected]',45)
insert into Score (Name,Email,Score1)values('Joseph','[email protected]',87)
insert into Score (Name,Email,Score1)values('Tom','[email protected]',67)
insert into Score (Name,Email,Score1)values('Sam','[email protected]',99)
insert into Score (Name,Email,Score1)values('Nancy','[email protected]',33)
insert into Score (Name,Email,Score1)values('Tina','[email protected]',23)

insert into Score (Name,Email,Score2)values('John','[email protected]',86)
insert into Score (Name,Email,Score2)values('Peter','[email protected]',56)
insert into Score (Name,Email,Score2)values('Nina','[email protected]',98)
insert into Score (Name,Email,Score2)values('Joseph','[email protected]',78)
insert into Score (Name,Email,Score2)values('Tom','[email protected]',12)
insert into Score (Name,Email,Score2)values('Sam','[email protected]',45)
insert into Score (Name,Email,Score2)values('Nancy','[email protected]',76)
insert into Score (Name,Email,Score2)values('Tina','[email protected]',78)
insert into Score (Name,Email,Score2)values('John','[email protected]',98)
insert into Score (Name,Email,Score2)values('Peter','[email protected]',45)
insert into Score (Name,Email,Score2)values('Nina','[email protected]',76)
insert into Score (Name,Email,Score2)values('Joseph','[email protected]',12)
insert into Score (Name,Email,Score2)values('Tom','[email protected]',84)
insert into Score (Name,Email,Score2)values('Sam','[email protected]',27)
insert into Score (Name,Email,Score2)values('Nancy','[email protected]',54)
insert into Score (Name,Email,Score2)values('Tina','[email protected]',50)

insert into Score (Name,Score1)values('Jack',10)
insert into Score (Email,Score2)values('[email protected]',20)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

不如归去 2024-10-14 05:58:34
select Name, Email, isnull(SUM(Score1),'') as Score1, isnull(SUM(Score2),'') as Score2
    from Score 
    group by Name, Email

select Name, Email, isnull(SUM(Score1),'') as Score1, isnull(SUM(Score2),'') as Score2
    from Score 
    group by Name, Email
回复 原文
~没有更多了~

关于作者

薄荷梦

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

qq_FjTq5B

文章 0 评论 0

18273202778

文章 0 评论 0

WordPress小学生

文章 0 评论 0

〃温暖了心ぐ

文章 0 评论 0

迷乱花海

文章 0 评论 0

niuniu

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文