sql-server - 将列值附加到重复记录中的某些记录并删除重复记录
问题描述
这是创建表和一些数据的脚本。
--Students table ---------
CREATE TABLE [Students](
[ID] [int] NOT NULL,
[SubjectID] [int] NULL,
[StudentName] [nvarchar](50) NULL,
[ConcatTo] [bit] NULL
) ON [PRIMARY]
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (1, 1, N'Mary', 1)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (2, 1, N'Brown', NULL)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (3, 2, N'Lily2', NULL)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (4, 2, N'Michilin2', 1)
GO
INSERT [Students] ([ID], [SubjectID], [StudentName], [ConcatTo]) VALUES (5, 2, N'Joshua2', NULL)
GO
select *from Students;
SELECT Main.SubjectID, main.Students As "Students"
FROM
(
SELECT DISTINCT t2.SubjectID,
(
SELECT t1.StudentName + ' ' AS [text()]
FROM dbo.Students t1
WHERE t1.SubjectID = t2.SubjectID
FOR XML PATH ('')
) Students
FROM dbo.Students t2
) Main
从表中选择会有这个
最多我只知道这样选择,但我不知道如何像这样更新我的学生表
这是我对预期结果的截图。
如何使用“Lily2 Michilin2 Joshua2”更新 StudentName 列,其中 ConcatTo = 1 就像我的选择语句一样?然后去掉 Lily2 和 Joshua2 行?
解决方案
您可以使用 aCTE
来包装现有查询,然后使用它来加入原始查询并更新它
WITH CTE AS
(
SELECT ID, Main.SubjectID, Main.Students As Students
FROM
(
SELECT ID = MIN(CASE WHEN t2.ConcatTo IS NOT NULL THEN t2.ID END), t2.SubjectID,
(
SELECT t1.StudentName + ' ' AS [text()]
FROM Students t1
WHERE t1.SubjectID = t2.SubjectID
FOR XML PATH ('')
) Students
FROM Students t2
GROUP BY t2.SubjectID
) Main
)
UPDATE s
SET StudentName = c.Students
FROM Students s
inner join CTE c ON s.ID = c.ID
对于第二部分,只需删除ConcatTo
为 null
DELETE s
FROM Students s
WHERE s.ConcatTo IS NULL
推荐阅读
- javascript - 导入第三方js库
- javascript - 解构 JavaScript 对象时如何只选择几个字段?
- jquery - 单击 div 外部或单击后代时隐藏 div
- kubernetes - 在 sts 副本中使用不同的 Secret
- opencl - 是否可以将异步回调/延续附加到 SYCL 内核?
- python - 检查 shell 命令是否已成功执行的最佳方法
- arrays - 选择我创建的数组时下标超出范围
- gremlin - Gremlin select multiple vertices 给出的输出没有具有空值的属性
- php - php 7.4 在写入变量中的 Twig 问题
- build - scons: *** [out/linux/x86_64/release/service/resource-container/librcs_container.so] 错误 1