基于相同的datetime合并Sql行
基于相同的datetime合并Sql行
提问于 2015-07-22 18:55:41
我正在努力完成以下任务。我有一张这样的桌子：
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+
| Id | Assigned Date | Assigned Starttime | Assigned Endtime | lesson_subject_id | lesson_teacher_id | lesson_classes_id |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+
| 116 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK | Adlberger | CL_HEB 15/I B |
| 112 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK | Jakob | CL_HEB 15/I A |
| 113 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK | Jakob | CL_HEB 15/I A |
| 117 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK | Adlberger | CL_HEB 15/I B |
| 118 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK | Adlberger | CL_HEB 15/I B |
| 114 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK | Jakob | CL_HEB 15/I A |
| 115 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK | Jakob | CL_HEB 15/I A |
| 119 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK | Adlberger | CL_HEB 15/I B |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+正如您所看到的，有一些条目同时开始和结束。
我需要做的是合并单元格，删除不需要的行。
结果应该如下所示
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+
| Id | Assigned Date | Assigned Starttime | Assigned Endtime | lesson_subject_id | lesson_teacher_id | lesson_classes_id |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+
| 116 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK | Adlberger, Jakob | CL_HEB 15/I A, CL_HEB 15/I B |
| 113 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK | Adlberger, Jakob | CL_HEB 15/I A, CL_HEB 15/I B |
| 118 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK | Adlberger, Jakob | CL_HEB 15/I A, CL_HEB 15/I B |
| 115 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK | Adlberger, Jakob | CL_HEB 15/I A, CL_HEB 15/I B |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+你知道我怎样才能做到这一点吗？我在使用Sql Server2008 R2
关于Lukas
Stack Overflow用户
发布于 2015-07-22 19:18:27
如果你想要像你建议的那样更新你的表，而不是进入解决方案:这看起来像是糟糕的数据库(重新)设计，imo。
第一个问题是你为什么要这样做？
您可以在UI上对数据进行分组(需要进行一些开发)，也可以通过拆分lesson_subjects和classes_classes/teachers表并建立它们之间的关系来更改设计。抱歉，这可能不是您需要的答案:)
编辑：我看到其他人也有类似的问题，所以我很想找到答案。在oracle中实现这一点的一种方法(基于Kibadachi的答案构建)：
SELECT MIN(ID),
ASSIGNED_DATE ,
ASSIGNED_STARTTIME,
ASSIGNED_STARTTIME,
LESSON_SUBJECT_ID,
LISTAGG(TRIM(LESSON_TEACH_ID) , ',') WITHIN GROUP (ORDER BY LESSON_TEACH_ID ) AS LESSON_TEACH_ID,
LISTAGG(TRIM(LESSON_CLASS_ID) , ',') WITHIN GROUP (ORDER BY LESSON_CLASS_ID ) AS LESSON_CLASS_ID
FROM ATABLE
GROUP BY ASSIGNED_DATE,
ASSIGNED_STARTTIME,
ASSIGNED_STARTTIME,
LESSON_SUBJECT_ID;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31561124
复制相关文章
相似问题
腾讯云开发者
