数据深加工处理

wwfxgm 2018-12-15 05:20:05
我工作中遇到一个问题,要以book_id 归类, 但是其中去重复很难搞定。
我提供下面的测试数据和需要的结果数据。

测试数据如下:
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B', N'于子涵'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'C', N'穆纨'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'A', N'郑桂兰'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'B', N'郑启芬'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'分析1', N'C##学院', N'C', N'班明露'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'D', N'何鑫琳'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'E', N'王枝'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'F', N'王枝'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'G', N'金青'); GO
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验D', N'C##学院', N'H', N'杨琦'); GO





我需要的结果是:

INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B/C', N'于子涵/穆纨'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C/分析1/实验A1/实验E/实验D', N'C##学院', N'A/B/C/D/E/F/G/H', N'郑桂兰/郑启芬/班明露/何鑫琳/王枝/金青/杨琦'); GO
...全文
1108 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
棋牌游戏可以破解吗?
小友之家 2018-12-17
  • 打赏
  • 举报
回复
大神,我想问ping-f不拆分是什么意思。为什么可以解决最大路径的问题呀
小黑蛇 2018-12-17
  • 打赏
  • 举报
回复
加油一楼下!
二月十六 2018-12-16
  • 打赏
  • 举报
回复
引用 8 楼 wwfxgm 的回复:
[quote=引用 7 楼 二月十六 的回复:]
SELECT BOOK_ID,
       Semester,
       STUFF(
       (
           SELECT '/' + course_name
           FROM
           (
               SELECT DISTINCT
                   b.course_name
               FROM dbo.Sheet2222 b
               WHERE a.BOOK_ID = b.BOOK_ID
           ) t
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS course_name,
       a.department_name,
       STUFF(
       (
           SELECT '/' + Class_name
           FROM
           (
               SELECT DISTINCT
                   b.Class_name
               FROM dbo.Sheet2222 b
               WHERE a.BOOK_ID = b.BOOK_ID
           ) t
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS Class_name,
       STUFF(
       (
           SELECT '/' + Teacher_name
           FROM
           (
               SELECT DISTINCT
                   b.Teacher_name
               FROM dbo.Sheet2222 b
               WHERE a.BOOK_ID = b.BOOK_ID
           ) t
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS Teacher_name
FROM [Sheet2222] a
GROUP BY BOOK_ID,
         Semester,
         a.department_name;
版主大神,您这个是符合要求的,但是为什么不用string_agg? 如果使用string_agg (SELECT DISTINCT b.Class_name FROM dbo.Sheet2222 b WHERE a.BOOK_ID = b.BOOK_ID,'/') 这个为啥出不来啊。 使用stuff倒是可以出来了。[/quote] 我这版本不支持这个函数
qq_17172005 2018-12-16
  • 打赏
  • 举报
回复
围观看看,有点难度。
wwfxgm 2018-12-16
  • 打赏
  • 举报
回复
引用 7 楼 二月十六 的回复:
SELECT BOOK_ID,
Semester,
STUFF(
(
SELECT '/' + course_name
FROM
(
SELECT DISTINCT
b.course_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS course_name,
a.department_name,
STUFF(
(
SELECT '/' + Class_name
FROM
(
SELECT DISTINCT
b.Class_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS Class_name,
STUFF(
(
SELECT '/' + Teacher_name
FROM
(
SELECT DISTINCT
b.Teacher_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS Teacher_name
FROM [Sheet2222] a
GROUP BY BOOK_ID,
Semester,
a.department_name;




版主大神,您这个是符合要求的,但是为什么不用string_agg?
如果使用string_agg (SELECT DISTINCT
b.Class_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID,'/') 这个为啥出不来啊。 使用stuff倒是可以出来了。
weixin_44122020 2018-12-16
  • 打赏
  • 举报
回复
顶帖,谢谢分享
二月十六 2018-12-15
  • 打赏
  • 举报
回复
SELECT BOOK_ID,
Semester,
STUFF(
(
SELECT '/' + course_name
FROM
(
SELECT DISTINCT
b.course_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS course_name,
a.department_name,
STUFF(
(
SELECT '/' + Class_name
FROM
(
SELECT DISTINCT
b.Class_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS Class_name,
STUFF(
(
SELECT '/' + Teacher_name
FROM
(
SELECT DISTINCT
b.Teacher_name
FROM dbo.Sheet2222 b
WHERE a.BOOK_ID = b.BOOK_ID
) t
FOR XML PATH('')
),
1,
1,
''
) AS Teacher_name
FROM [Sheet2222] a
GROUP BY BOOK_ID,
Semester,
a.department_name;


wwfxgm 2018-12-15
  • 打赏
  • 举报
回复
引用 5 楼 吉普赛的歌 的回复:
USE tempdb
GO
IF OBJECT_ID('[Sheet2222]') IS NOT NULL DROP TABLE [Sheet2222]
GO
CREATE TABLE [Sheet2222](
[BOOK_ID] INT,
[Semester] VARCHAR(20),
[course_name] NVARCHAR(30),
[department_name] NVARCHAR(20),
[Class_name] NVARCHAR(10),
[Teacher_name] NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B', N'于子涵')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'C', N'穆纨')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'A', N'郑桂兰')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'B', N'郑启芬')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'分析1', N'C##学院', N'C', N'班明露')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'D', N'何鑫琳')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'E', N'王枝')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'F', N'王枝')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'G', N'金青')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验D', N'C##学院', N'H', N'杨琦')
GO
SELECT BOOK_ID
,[Semester]
,STUFF((SELECT '/'+course_name FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS course_name
,a.department_name
,STUFF((SELECT '/'+[Class_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Class_name]
,STUFF((SELECT '/'+[Teacher_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Teacher_name]
FROM [Sheet2222] AS a
GROUP BY BOOK_ID,[Semester],a.department_name








版主您好,我主要是要实现:course_name 里面的结果是不重复的。
根据你的代码,course_name 还是会有重复值出现 。比如 这条记录 实验C/实验C/分析1/实验A1/实验A1/实验E/实验E/实验D

我简单的语句,基本会写了,我用了 sqlserver2017版本自带的 string_agg ,合成是没有问题,但是删除重复值,实在超出我的能力范围了,想了好几天不得其解 。

吉普赛的歌 2018-12-15
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('[Sheet2222]') IS NOT NULL DROP TABLE [Sheet2222]
GO
CREATE TABLE [Sheet2222](
	[BOOK_ID] INT, 
	[Semester] VARCHAR(20), 
	[course_name] NVARCHAR(30), 
	[department_name] NVARCHAR(20), 
	[Class_name] NVARCHAR(10), 
	[Teacher_name] NVARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B', N'于子涵')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'C', N'穆纨')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'A', N'郑桂兰')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'B', N'郑启芬')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'分析1', N'C##学院', N'C', N'班明露')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'D', N'何鑫琳')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'E', N'王枝')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'F', N'王枝')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'G', N'金青')
INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验D', N'C##学院', N'H', N'杨琦')
GO
SELECT BOOK_ID
,[Semester]
,STUFF((SELECT '/'+course_name FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS course_name
,a.department_name
,STUFF((SELECT '/'+[Class_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Class_name]
,STUFF((SELECT '/'+[Teacher_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Teacher_name]
FROM [Sheet2222] AS a
GROUP BY BOOK_ID,[Semester],a.department_name

wwfxgm 2018-12-15
  • 打赏
  • 举报
回复



这个是原始数据模式,具体我放在一楼的测试数据脚本里面。
wwfxgm 2018-12-15
  • 打赏
  • 举报
回复
引用 2 楼 二月十六 的回复:
两表的表结构也贴出来吧


版主,我的测试数据,不能算数据结构吗?
二月十六 2018-12-15
  • 打赏
  • 举报
回复
两表的表结构也贴出来吧
wwfxgm 2018-12-15
  • 打赏
  • 举报
回复



这个是需要的结果图片

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧