求sql语句，有点复杂，先合并再分组再按条件只取其中一条记录

kdg2000 2014-11-03 11:11:15
1，先合并，
2，再分组
3，再只取一条记录

例如，表 T1
name start_date end_date
A 2014.1.1 2014.3.3
A 2014.5.5 2014.6.6
A 2014.3.3 2014.5.5
B 2014.1.1 2014.3.3
B 2014.5.5 2014.6.6
B 2014.6.6 2014.7.7

按name分组，先合并，（稍微有点复杂，我尽量描述的清晰点）

1，
name=A的记录有三条，
如果这三条记录的start_date 和 end_date能接起来（例如第三条的end_date>=第一条记录的start_date，之所以没用第二条记录跟第一条记录比较，是因为第三条记录的sart_date和第一条记录的end_date最接近）,那么把这两条记录合并为一条记录，也就是变成下面这样（两条记录），
name start_date end_date
A 2014.1.1 2014.5.5
A 2014.5.5 2014.6.6
然后同理，剩下这两条记录也能接起来，变成一条记录
name start_date end_date
A 2014.1.1 2014.6.6

name=A的记录合并完成，因为最后只剩下一条记录，所以不用分组，

2.
但是name=B的记录有点复杂，因为并不是所有记录都能接起来
a. 先找到日期最早的记录，即，第一条记录，那么和他最接近的记录是第二条，但是第二条和第一条记录不满足条件（第二条的end_date 和 第一条记录的start_date之间有空隙)，所以不能合并，
b. 那么第二条记录和第三条记录之间能合并，所以最后变成了两条记录：

name start_date end_date
B 2014.1.1 2014.3.3
B 2014.5.5 2014.7.7

因为最后针对每个name只想取一条记录（end_date最大的），其实到这里我就知道了，可以用row_number() over(partition by name order by end_date desc) rowNumber，然后取rowNumber=1的记录。

请大牛们支招（实在没分了，谢谢各位）
...全文
626 34 打赏 收藏 转发到动态 举报
写回复
用AI写文章
34 条回复
切换为时间正序
请发表友善的回复…
发表回复
北沉丶 2014-11-04
  • 打赏
  • 举报
 回复
楼主为何不讲时间用时间戳存起来，那样不是更方便吗？
zhrongr 2014-11-04
  • 打赏
  • 举报
 回复
先按name分组，再把end_date=start_date的行数据查询出来。再从查询的结果中，查询出start_date的最小，和end_date的最大。
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
引用 12 楼 roy_88 的回复:
改改这样写性能高一点 ;
WITH a
AS
(
SELECT  [name],[start_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND end_date =a.[start_date])
),b AS 
(                                                   
SELECT  [name],[end_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND [start_date] =a.[end_date])
)
SELECT  a.[name]
       ,a.[start_date]
       ,[end_date] = MIN(b.[end_date])
FROM    a
        INNER JOIN b ON a.[name] = b.[name] AND a.[start_date]<=b.[end_date]
GROUP BY a.[name]
       ,a.[start_date]
/*
name	start_date	end_date
A	2014-01-01 00:00:00.000	2014-06-06 00:00:00.000
B	2014-01-01 00:00:00.000	2014-03-03 00:00:00.000
B	2014-05-05 00:00:00.000	2014-07-07 00:00:00.000
*/
版主，你的这段代码可不可以稍微改动一下变成我最后想要的最后结果（合并完之后如果有多条记录，就取end_date最大的那条）这样我就不用再去PARTITION一次了， name start_date end_date A 2014-01-01 00:00:00.000 2014-06-06 00:00:00.000 B 2014-05-05 00:00:00.000 2014-07-07 00:00:00.000
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
引用 14 楼 roy_88 的回复:
6楼只适用于整个表的连续性判断
很感谢，可不可以帮我解释一下a和b这两个CTE的作用是干什么的， 因为我的情况是，如果第二条的start_date如果在上一条记录的end_date之前，也算连续（只要时间之间没空隙就算连续，不一定要完全相等），这个也cover了吧？如果是的话，是在CTE里处理的还是在a.[start_date]<=b.[end_date]这里处理的，非常感谢
中国风 2014-11-03
  • 打赏
  • 举报
 回复
6楼只适用于整个表的连续性判断
中国风 2014-11-03
  • 打赏
  • 举报
 回复
6楼方法有Bug,请用12楼方法
中国风 2014-11-03
  • 打赏
  • 举报
 回复
改改这样写性能高一点 ;
WITH a
AS
(
SELECT  [name],[start_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND end_date =a.[start_date])
),b AS 
(                                                   
SELECT  [name],[end_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND [start_date] =a.[end_date])
)
SELECT  a.[name]
       ,a.[start_date]
       ,[end_date] = MIN(b.[end_date])
FROM    a
        INNER JOIN b ON a.[name] = b.[name] AND a.[start_date]<=b.[end_date]
GROUP BY a.[name]
       ,a.[start_date]
/*
name	start_date	end_date
A	2014-01-01 00:00:00.000	2014-06-06 00:00:00.000
B	2014-01-01 00:00:00.000	2014-03-03 00:00:00.000
B	2014-05-05 00:00:00.000	2014-07-07 00:00:00.000
*/
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
引用 9 楼 roy_88 的回复:
[quote=引用 7 楼 kdg2000 的回复:] [quote=引用 3 楼 roy_88 的回复:] 
SELECT  [name]
       ,[start_date] = MIN([start_date])
       ,[end_date] = MAX([end_date])
FROM    T1
GROUP BY [name]
谢谢，不过如果时间不连续是不能合并的，也就是会出现B记录那种3条记录合并之后变成 2条的情况[/quote] 6楼有提供方法[/quote] 看到了，谢谢，我放到我的逻辑代码中调试一下
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
谢谢楼上两位高手，我试一下，因为实际情况更复杂，性能问题不得不考虑进去，这小段逻辑实际上只是整个方法逻辑中的一小部分，但是代码量已经超过整个方法的代码量了，唉
中国风 2014-11-03
  • 打赏
  • 举报
 回复
引用 7 楼 kdg2000 的回复:
[quote=引用 3 楼 roy_88 的回复:] 
SELECT  [name]
       ,[start_date] = MIN([start_date])
       ,[end_date] = MAX([end_date])
FROM    T1
GROUP BY [name]
谢谢，不过如果时间不连续是不能合并的，也就是会出现B记录那种3条记录合并之后变成 2条的情况[/quote] 6楼有提供方法
中国风 2014-11-03
  • 打赏
  • 举报
 回复
连续性方法直接用语句比用CTE性能要高
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
引用 3 楼 roy_88 的回复:
SELECT  [name]
       ,[start_date] = MIN([start_date])
       ,[end_date] = MAX([end_date])
FROM    T1
GROUP BY [name]
谢谢，不过如果时间不连续是不能合并的，也就是会出现B记录那种3条记录合并之后变成 2条的情况
中国风 2014-11-03
  • 打赏
  • 举报
 回复 
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([name] nvarchar(1),[start_date] Datetime,[end_date] Datetime)
Insert #T1
select N'A','2014.1.1','2014.3.3' union all
select N'A','2014.5.5','2014.6.6' union all
select N'A','2014.3.3','2014.5.5' union all
select N'B','2014.1.1','2014.3.3' union all
select N'B','2014.5.5','2014.6.6' union all
select N'B','2014.6.6','2014.7.7'
Go
;WITH a
AS
(
SELECT  [name],[start_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND [start_date] <a.[start_date]
                            AND NOT EXISTS ( SELECT 1
                                             FROM   #T1
                                             WHERE  [start_date]< a.[start_date]
                                                    AND [start_date] >b.[start_date] ) )
),b AS 
(                                                   
SELECT  [name],[end_date]
FROM    #T1 AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   #T1 AS b
                     WHERE  [name] = a.[name]
                            AND [start_date] > a.[start_date]
                            AND NOT EXISTS ( SELECT 1
                                             FROM   #T1
                                             WHERE  [start_date] > a.[start_date]
                                                    AND [start_date] < b.[start_date] ) )
)
SELECT  a.[name]
       ,a.[start_date]
       ,[end_date] = MIN(b.[end_date])
FROM    a
        INNER JOIN b ON a.[name] = b.[name] AND a.[start_date]<=b.[end_date]
GROUP BY a.[name]
       ,a.[start_date]
/*
name	start_date	end_date
A	2014-01-01 00:00:00.000	2014-06-06 00:00:00.000
B	2014-01-01 00:00:00.000	2014-03-03 00:00:00.000
B	2014-05-05 00:00:00.000	2014-07-07 00:00:00.000
*/
Tiger_Zhao 2014-11-03
  • 打赏
  • 举报
 回复 
WITH t1([name],start_date,end_date) AS (

    SELECT 'A','2014.1.1','2014.3.3' UNION ALL

    SELECT 'A','2014.5.5','2014.6.6' UNION ALL

    SELECT 'A','2014.3.3','2014.5.5' UNION ALL

    SELECT 'B','2014.1.1','2014.3.3' UNION ALL

    SELECT 'B','2014.5.5','2014.6.6' UNION ALL

    SELECT 'B','2014.6.6','2014.7.7'

)

,t2 AS (

    SELECT [name],

           start_date,

           ROW_NUMBER() OVER (PARTITION BY name ORDER BY start_date) n

      FROM t1

     WHERE NOT EXISTS (SELECT *

                         FROM t1 t

                        WHERE t.[name] = t1.[name]

                          AND t.end_date = t1.start_date

                      )

)

,t3 AS (

    SELECT [name],

           end_date,

           ROW_NUMBER() OVER (PARTITION BY name ORDER BY start_date) n

      FROM t1

     WHERE NOT EXISTS (SELECT *

                         FROM t1 t

                        WHERE t.[name] = t1.[name]

                          AND t.start_date = t1.end_date

                      )

)

SELECT t2.[name],

       t2.start_date,

       t3.end_date

  FROM t2

  JOIN t3

    ON t3.[name] = t2.[name]

   AND t3.n = t2.n

name start_date end_date

---- ---------- --------

A    2014.1.1   2014.6.6

B    2014.1.1   2014.3.3

B    2014.5.5   2014.7.7
中国风 2014-11-03
  • 打赏
  • 举报
 回复
看结果集，取开始的最小，取结束的最大就行了
中国风 2014-11-03
  • 打赏
  • 举报
 回复 
SELECT  [name]
       ,[start_date] = MIN([start_date])
       ,[end_date] = MAX([end_date])
FROM    T1
GROUP BY [name]
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
实际上最后的结果应该为： name start_date end_date A 2014.1.1 2014.6.6 B 2014.5.5 2014.7.7 只不过到上面那个结果之后我就知道怎么得到最后的这个结果了，如果大牛们直接写出到最后这步的代码也更好
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
name start_date end_date A 2014.1.1 2014.3.3 A 2014.5.5 2014.6.6 A 2014.3.3 2014.5.5 B 2014.1.1 2014.3.3 B 2014.5.5 2014.6.6 B 2014.6.6 2014.7.7 最后结果为： name start_date end_date A 2014.1.1 2014.6.6 B 2014.1.1 2014.3.3 B 2014.5.5 2014.7.7
Tiger_Zhao 2014-11-03
  • 打赏
  • 举报
 回复
不用谢。
主要是得有个反馈，否则以为又碰上语文不是一个次元的老师教的、理解错了。
kdg2000 2014-11-03
  • 打赏
  • 举报
 回复
引用 30 楼 Tiger_Zhao 的回复:
难道我 #22 不正确？
不好意思，刚才刷新页面的时候没看到，实在抱歉 测试了一下，你的22楼代码也可以，学习了，非常谢谢，明天一并给分
加载更多回复（14）
如何使用sql语句对数据库数据分组合并
目录一、目的二、解决方法2.1 Mysql2.1.1 分组合并字符串2.1.2 分组合并取最大的一行2.2 Hive 一、目的 当我们面对如下数据时，想要将每个客户的物品用一个一行来存储，物品间使用“，”分隔，如何才能做到？ id...
SQL中遇到多条相同内容只取一条的最简单实现
SQL中经常遇到如下情况，在一张表中...针对time字段相同时有不同total和name的情形，每当遇到相同的则只取其中一条数据，最简单的实现方法有两种 每当遇到相同的则只取其中一条数据，最简单的实现方法有两种   1、sel
SQL实现将多行记录合并成一行
SQL实现将多行记录合并成一行
sql 合并多条sql语句
1.最近用到将多条数据合并成一行的案例。整理以至于不在忘记。（sql语句如下） SELECT student_name ,student_no ,SUM(CASE WHEN subject_name='数学' THEN score ELSE '' END) as '数学' ,SUM(CASE WHEN ...
SQL server把多条记录查找结果合并成一条记录
SQL server 把多条记录结果合并成一条记录表 users 的属性有 name, dept_id等。我们要查找部门所属的员工有哪些，使用以下代码块，查询结果为： select dept.dname 部门名称, users.name 员工名称 from dept, users...
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章