sql 数据怎么多行合并成一行 ？

ms sql 2005
Table   T

date                     time                        a                        b                     c                   d     ------------a,b,c确定唯一值，d表示状态（2,1,0）    
datetime           datetime               int                       int                    int                  int  
怎么根据d 的不同将a,b,c 相同的行从d  != 0 开始读取 到d=0 结束的行数据合并成一行（ 包括d(2,1,0)、d(2,0)、d(1,0)三种情况）
若是d(2,1,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3               t3
                                                                                  int           datetime                 int              datetime             int           datetime  
                                                                                  2                                                1                                            0
若是d(2,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3               t3
                                                                                  int           datetime                 int              datetime             int            datetime 
                                                                                  2                                            null                 null                   0 
若是d(1,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3                t3
                                                                                  int           datetime                 int              datetime             int             datetime 
                                                                                  null          null                          1               time2                  0
------------d1,d2,d3表示连续三行的d，t2为(time2-time1)的时间， t3为（time3-time2）的时间，t2,t3用mm:ss表示

28 个解决方案

#1

原数据


效果图

#2

上面的原数据发错了，下面才是

#3

sql里合并相同行的手段主要是distinct，group by， partition by

#4

引用 3 楼 xdashewan 的回复:

sql里合并相同行的手段主要是distinct，group by， partition by

大神，能不能详细点，数据库是个二把刀啊

#5

引用 4 楼 fengyuzhewu 的回复:

大神，能不能详细点，数据库是个二把刀啊

那你得把你表结构和数据先整利索了，想要sql最好把建立表sql和插入数据sql都带上

#6

引用 5 楼 xdashewan 的回复:

Quote: 引用 4 楼 fengyuzhewu 的回复:

大神，能不能详细点，数据库是个二把刀啊

那你得把你表结构和数据先整利索了，想要sql最好把建立表sql和插入数据sql都带上

create table T ([date] datetime,[time] datetime,,a int ,b int ,c int ,d int)
insert into T select '2016/1/21','2016/1/21 14:45:21',7,6,1,2

insert into T select '2016/1/21','2016/1/21 14:48:18';7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:55:10',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:18',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:48:26',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:38',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:55:06',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,2,0

insert into T select '2016/1/21','2016/1/21 15:12:55',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,3,1

insert into T select '2016/1/21','2016/1/21 14:47:50',7,6,3,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,3,2
go

#7

引用 5 楼 xdashewan 的回复:

Quote: 引用 4 楼 fengyuzhewu 的回复:

大神，能不能详细点，数据库是个二把刀啊

那你得把你表结构和数据先整利索了，想要sql最好把建立表sql和插入数据sql都带上

麻烦大神看看

#8

引用 7 楼 fengyuzhewu 的回复:

麻烦大神看看

你效果图和数据怎么对起来，效果图里的值和你数据对不起来

#9

引用 8 楼 xdashewan 的回复:

Quote: 引用 7 楼 fengyuzhewu 的回复:

麻烦大神看看

你效果图和数据怎么对起来，效果图里的值和你数据对不起来

你做出来是什么样的啊？a,b,c确定group ，从d=3到d=0合并，若d=0后面还有一个0，则后一个d=0的行忽略

#10

引用 9 楼 fengyuzhewu 的回复:

你做出来是什么样的啊？a,b,c确定group ，从d=3到d=0合并，若d=0后面还有一个0，则后一个d=0的行忽略

你要先说明如何合并数据，比如从你的sql的那abc=761的那6行数据，如果得到结果，得到怎么样的结果

#11

引用 10 楼 xdashewan 的回复:

Quote: 引用 9 楼 fengyuzhewu 的回复:

你做出来是什么样的啊？a,b,c确定group ，从d=3到d=0合并，若d=0后面还有一个0，则后一个d=0的行忽略

你要先说明如何合并数据，比如从你的sql的那abc=761的那6行数据，如果得到结果，得到怎么样的结果

结果如下图：

根据d 的不同将a,b,c 相同的行从d  != 0 开始读取 到d=0 结束的行数据合并成一行（包括d(2,1,0)、d(2,0)、d(1,0)三种情况）
若是d(2,1,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3               t3
                                                                                  int           datetime                 int              datetime             int           datetime  
                                                                                  2                                                1                                            0
若是d(2,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3               t3
                                                                                  int           datetime                 int              datetime             int            datetime 
                                                                                  2                                            null                 null                   0 
若是d(1,0)，则显示结果如下
date                    a            b                 c                 d1             t1                            d2               t2                        d3                t3
                                                                                  int           datetime                 int              datetime             int             datetime 
                                                                                  null          null                          1               time2                  0

#12

;WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY date,a,b,c ORDER BY date,time) FROM T
)
SELECT  a.date
       ,a.a
       ,a.b
       ,a.c
       ,max(CASE WHEN a.d=1 THEN a.d END) AS d1
	   ,MAX(CASE WHEN a.d=1 THEN CONVERT(VARCHAR(8),a.time,8) END) t1
       ,max(CASE WHEN a.d=2 THEN a.d END) AS d2
	   ,MAX(CASE WHEN a.d=2 THEN CONVERT(VARCHAR(8),a.time,8) END) t2
       ,max(CASE WHEN a.d=0 THEN a.d END) AS d3
	   ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
FROM    Cte AS a
        LEFT JOIN Cte AS b ON b.a = a.a
                              AND b.b = a.b
                              AND b.c = a.c
                              AND b.d = 0
                              AND b.Ord >= a.Ord
                              AND b.Ord = ( SELECT  MIN(Ord)
                                            FROM    Cte
                                            WHERE   a = a.a
                                                    AND b = a.b
                                                    AND c = a.c
                                                    AND d = 0
                                                    AND Ord >= a.Ord
                                          )
GROUP BY a.date
       ,a.a
       ,a.b
       ,a.c
       ,b.Ord;
/*
	date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	NULL	NULL	2	14:45:21	0	00:02:58
2016-01-21 00:00:00.000	7	6	1	1	14:48:42	NULL	NULL	0	00:00:17
2016-01-21 00:00:00.000	7	6	1	1	14:55:10	NULL	NULL	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	1	15:12:55	NULL	NULL	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	NULL	NULL	0	00:00:01
2016-01-21 00:00:00.000	7	6	2	1	14:48:18	NULL	NULL	0	00:00:09
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:48:38	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:55:06	0	00:00:25
2016-01-21 00:00:00.000	7	6	3	NULL	NULL	2	14:48:42	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	3	1	14:45:22	NULL	NULL	0	00:02:29
*/

#13

引用 12 楼 roy_88 的回复:

;WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY date,a,b,c ORDER BY date,time) FROM T
)
SELECT  a.date
       ,a.a
       ,a.b
       ,a.c
       ,max(CASE WHEN a.d=1 THEN a.d END) AS d1
	   ,MAX(CASE WHEN a.d=1 THEN CONVERT(VARCHAR(8),a.time,8) END) t1
       ,max(CASE WHEN a.d=2 THEN a.d END) AS d2
	   ,MAX(CASE WHEN a.d=2 THEN CONVERT(VARCHAR(8),a.time,8) END) t2
       ,max(CASE WHEN a.d=0 THEN a.d END) AS d3
	   ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
FROM    Cte AS a
        LEFT JOIN Cte AS b ON b.a = a.a
                              AND b.b = a.b
                              AND b.c = a.c
                              AND b.d = 0
                              AND b.Ord >= a.Ord
                              AND b.Ord = ( SELECT  MIN(Ord)
                                            FROM    Cte
                                            WHERE   a = a.a
                                                    AND b = a.b
                                                    AND c = a.c
                                                    AND d = 0
                                                    AND Ord >= a.Ord
                                          )
GROUP BY a.date
       ,a.a
       ,a.b
       ,a.c
       ,b.Ord;
/*
	date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	NULL	NULL	2	14:45:21	0	00:02:58
2016-01-21 00:00:00.000	7	6	1	1	14:48:42	NULL	NULL	0	00:00:17
2016-01-21 00:00:00.000	7	6	1	1	14:55:10	NULL	NULL	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	1	15:12:55	NULL	NULL	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	NULL	NULL	0	00:00:01
2016-01-21 00:00:00.000	7	6	2	1	14:48:18	NULL	NULL	0	00:00:09
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:48:38	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:55:06	0	00:00:25
2016-01-21 00:00:00.000	7	6	3	NULL	NULL	2	14:48:42	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	3	1	14:45:22	NULL	NULL	0	00:02:29
*/

版主大大老牛了，回去试下，不行再找你，

#14

with cte as
(
	select *, row_number() over (order by a, b, c) as rowid
	from [T]
)
select d.* from (
select a.a, a.b, a.c, a.d as d1, a.time as time1, b.d as d2, b.time as time2, c.d as d3, c.time as time3, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 2 and b.d = 1
inner join cte c on a.rowid + 2 = c.rowid and a.d = 2 and c.d = 0
union all
select a.a, a.b, a.c, a.d, a.time, null, null, b.d, b.time, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 2 and b.d = 0
union all
select a.a, a.b, a.c, null, null, a.d, a.time, b.d, b.time, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 1 and b.d = 0
inner join cte c on a.rowid - 1 = c.rowid and a.d = 1 and c.d != 2) d
order by d.a, d.b, d.c, d.rowid

#15

引用 14 楼 xdashewan 的回复:

with cte as
(
	select *, row_number() over (order by a, b, c) as rowid
	from [T]
)
select d.* from (
select a.a, a.b, a.c, a.d as d1, a.time as time1, b.d as d2, b.time as time2, c.d as d3, c.time as time3, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 2 and b.d = 1
inner join cte c on a.rowid + 2 = c.rowid and a.d = 2 and c.d = 0
union all
select a.a, a.b, a.c, a.d, a.time, null, null, b.d, b.time, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 2 and b.d = 0
union all
select a.a, a.b, a.c, null, null, a.d, a.time, b.d, b.time, a.rowid from cte a
inner join cte b on a.rowid + 1 = b.rowid and a.d = 1 and b.d = 0
inner join cte c on a.rowid - 1 = c.rowid and a.d = 1 and c.d != 2) d
order by d.a, d.b, d.c, d.rowid

谢谢

#16

引用 12 楼 roy_88 的回复:

	   ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
*/

这有个错误，是不是MSsql2005的问题

#17

引用 16 楼 fengyuzhewu 的回复:

Quote: 引用 12 楼 roy_88 的回复:

	   ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
*/

这有个错误，是不是MSsql2005的问题

无关SQL2005以上版本都支持，SQL2000就生成临时表处理

#18

看看是不是你自己建表数据没改

insert into T select '2016/1/21','2016/1/21 14:48:18' ;7,6,1,0

#19

红色";"改为","

#20

引用 19 楼 roy_88 的回复:

红色";"改为","

我的数据是正常的啊

#21

该回复于2016-07-09 13:35:51被管理员删除

#22

该回复于2016-02-03 09:28:05被管理员删除

#23

你把完整COPY运行试试有没有报错

USE tempdb
GO

create table T ([date] datetime,[time] datetime,a int ,b int ,c int ,d int)
insert into T select '2016/1/21','2016/1/21 14:45:21',7,6,1,2

insert into T select '2016/1/21','2016/1/21 14:48:18',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:55:10',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:18',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:48:26',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:38',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:55:06',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,2,0

insert into T select '2016/1/21','2016/1/21 15:12:55',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,3,1

insert into T select '2016/1/21','2016/1/21 14:47:50',7,6,3,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,3,2
GO
;WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY date,a,b,c ORDER BY date,time) FROM T
)
SELECT  a.date
       ,a.a
       ,a.b
       ,a.c
       ,max(CASE WHEN a.d=1 THEN a.d END) AS d1
       ,MAX(CASE WHEN a.d=1 THEN CONVERT(VARCHAR(8),a.time,8) END) t1
       ,max(CASE WHEN a.d=2 THEN a.d END) AS d2
       ,MAX(CASE WHEN a.d=2 THEN CONVERT(VARCHAR(8),a.time,8) END) t2
       ,max(CASE WHEN a.d=0 THEN a.d END) AS d3
       ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
FROM    Cte AS a
        LEFT JOIN Cte AS b ON b.a = a.a
                              AND b.b = a.b
                              AND b.c = a.c
                              AND b.d = 0
                              AND b.Ord >= a.Ord
                              AND b.Ord = ( SELECT  MIN(Ord)
                                            FROM    Cte
                                            WHERE   a = a.a
                                                    AND b = a.b
                                                    AND c = a.c
                                                    AND d = 0
                                                    AND Ord >= a.Ord
                                          )
GROUP BY a.date
       ,a.a
       ,a.b
       ,a.c
       ,b.Ord;
GO

/*
date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	NULL	NULL	2	14:45:21	0	00:02:58
2016-01-21 00:00:00.000	7	6	1	1	14:48:42	NULL	NULL	0	00:00:17
2016-01-21 00:00:00.000	7	6	1	1	14:55:10	NULL	NULL	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	1	15:12:55	NULL	NULL	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	NULL	NULL	0	00:00:01
2016-01-21 00:00:00.000	7	6	2	1	14:48:18	NULL	NULL	0	00:00:09
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:48:38	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:55:06	0	00:00:25
2016-01-21 00:00:00.000	7	6	3	NULL	NULL	2	14:48:42	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	3	1	14:45:22	NULL	NULL	0	00:02:29
*/

#24

该回复于2016-02-02 08:30:15被管理员删除

#25

该回复于2016-02-03 09:40:10被管理员删除

#26

谢谢分享~~~~~

#27

引用 23 楼 中国风的回复:

你把完整COPY运行试试有没有报错

USE tempdb
GO

create table T ([date] datetime,[time] datetime,a int ,b int ,c int ,d int)
insert into T select '2016/1/21','2016/1/21 14:45:21',7,6,1,2

insert into T select '2016/1/21','2016/1/21 14:48:18',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:55:10',7,6,1,1

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,1,0

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:18',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:48:26',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:48:38',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:48:58',7,6,2,0

insert into T select '2016/1/21','2016/1/21 14:55:06',7,6,2,2

insert into T select '2016/1/21','2016/1/21 14:55:30',7,6,2,0

insert into T select '2016/1/21','2016/1/21 15:12:55',7,6,2,1

insert into T select '2016/1/21','2016/1/21 14:45:22',7,6,3,1

insert into T select '2016/1/21','2016/1/21 14:47:50',7,6,3,0

insert into T select '2016/1/21','2016/1/21 14:48:42',7,6,3,2
GO
;WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY date,a,b,c ORDER BY date,time) FROM T
)
SELECT  a.date
       ,a.a
       ,a.b
       ,a.c
       ,max(CASE WHEN a.d=1 THEN a.d END) AS d1
       ,MAX(CASE WHEN a.d=1 THEN CONVERT(VARCHAR(8),a.time,8) END) t1
       ,max(CASE WHEN a.d=2 THEN a.d END) AS d2
       ,MAX(CASE WHEN a.d=2 THEN CONVERT(VARCHAR(8),a.time,8) END) t2
       ,max(CASE WHEN a.d=0 THEN a.d END) AS d3
       ,RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))/360,2)+':' +RIGHT(100+DATEDIFF(ss,MIN(a.time),MAX(a.time))%360/60,2)+':'++RIGHT(101+DATEDIFF(ss,MIN(a.time),MAX(a.time))%60,2) t3
FROM    Cte AS a
        LEFT JOIN Cte AS b ON b.a = a.a
                              AND b.b = a.b
                              AND b.c = a.c
                              AND b.d = 0
                              AND b.Ord >= a.Ord
                              AND b.Ord = ( SELECT  MIN(Ord)
                                            FROM    Cte
                                            WHERE   a = a.a
                                                    AND b = a.b
                                                    AND c = a.c
                                                    AND d = 0
                                                    AND Ord >= a.Ord
                                          )
GROUP BY a.date
       ,a.a
       ,a.b
       ,a.c
       ,b.Ord;
GO

/*
date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	NULL	NULL	2	14:45:21	0	00:02:58
2016-01-21 00:00:00.000	7	6	1	1	14:48:42	NULL	NULL	0	00:00:17
2016-01-21 00:00:00.000	7	6	1	1	14:55:10	NULL	NULL	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	1	15:12:55	NULL	NULL	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	NULL	NULL	0	00:00:01
2016-01-21 00:00:00.000	7	6	2	1	14:48:18	NULL	NULL	0	00:00:09
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:48:38	0	00:00:21
2016-01-21 00:00:00.000	7	6	2	NULL	NULL	2	14:55:06	0	00:00:25
2016-01-21 00:00:00.000	7	6	3	NULL	NULL	2	14:48:42	NULL	00:00:01
2016-01-21 00:00:00.000	7	6	3	1	14:45:22	NULL	NULL	0	00:02:29
*/

谢谢，抱歉直到现在才结贴

#28

Mark 一下。。