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

ms sql 2005
Table   T

date                     time                        a                        b                     c                   d     ------------a,b,c确定唯一值，d表示状态    
datetime           datetime               int                       int                    int                  int  
怎么根据d 的不同将a,b,c 相同的行 每三行合并成一行，达到如下效果

date                    a            b                 c                 d1             t1                            d2               t2                        d3
                                                                                  int           datetime                 int              datetime             int

------------d1,d2,d3表示连续三行的d，t1为(time2-time1)的时间， t2为（time3-time2）的时间，t1,t2用mm:ss表示

求大神指教啊

19 个解决方案

#1

贴出模拟数据和想要的效果

#2

date                       time            a                        b                     c                   d   -------------模拟数据
2016/1/21            time1          7                         6                      1                  2 (d1)
2016/1/21            time2          7                         6                      1                  1   (d2)                  
2016/1/21            time3          7                         6                      1                   0  (d3)
2016/1/21            time4          7                         6                      1                  2  (d4)
2016/1/21            time5          7                         6                      1                  1  (d5)
2016/1/21            time6          7                         6                      1                  0  (d6)
date                time            a            b                 c                 d1                  t1                            d2               t2                        d3--------------想要的效果
2016/1/21      time1        7             6                 1                 2           (time2-time1)               1        (time2-time1)                  0
2016/1/21      time4        7             6                 1                 2           (time5-time4)               1        (time6-time5)                  0

#3

引用 1 楼 roy_88 的回复:

贴出模拟数据和想要的效果

date                       time            a                        b                     c                   d   -------------模拟数据
2016/1/21            time1          7                         6                      1                  2 (d1)
2016/1/21            time2          7                         6                      1                  1   (d2)                  
2016/1/21            time3          7                         6                      1                   0  (d3)
2016/1/21            time4          7                         6                      1                  2  (d4)
2016/1/21            time5          7                         6                      1                  1  (d5)
2016/1/21            time6          7                         6                      1                  0  (d6)
date                time            a            b         c          d1                  t1                            d2               t2                d3--------------想要的效果
2016/1/21      time1        7             6         1          2           (time2-time1)               1        (time2-time1)       0
2016/1/21      time4        7             6          1          2           (time5-time4)               1        (time6-time5)      0

#4

DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''

SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
	SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
EXEC(';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [date] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c ')

#5

快来人啊

#6

引用 5 楼 fengyuzhewu 的回复:

快来人啊

没看到#4回复么，我也是醉了

#7

引用 6 楼 roy_88 的回复:

Quote: 引用 5 楼 fengyuzhewu 的回复:

快来人啊

没看到#4回复么，我也是醉了

抱歉抱歉，没刷出来，谢谢

#8

引用 4 楼 roy_88 的回复:

DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''

SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
	SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
EXEC(';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [date] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c ')

这个好像有问题，一直在查询

#9

DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
 
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
 
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
PRINT ';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c '

--EXEC(';WITH Cte
--AS
--(
--SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
--)
--SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c ')

把生成语句执行看看
[date],a,b,c,[time]--建上索引

#10

引用 9 楼 roy_88 的回复:

DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
 
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
 
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
PRINT ';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c '

--EXEC(';WITH Cte
--AS
--(
--SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
--)
--SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c ')

把生成语句执行看看
[date],a,b,c,[time]--建上索引

版主大大，还是不行啊，一直在查询

#11

引用 10 楼 fengyuzhewu 的回复:

版主大大，还是不行啊，一直在查询

数据量多少？

有没有建上索引？

T--是不是有其它连接正在操作
生成临时再出结果看看

IF OBJECT_ID('Tempdb..#Cte') IS NOT NULL
	DROP TABLE #Cte
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) INTO #Cte FROM T WITH(NOLOCK)

CREATE INDEX IX_#Cte ON #Cte([date],a,b,c,[time])

DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
  
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'
DECLARE @Cols NVARCHAR(4000),@Sql VARCHAR(3)
SELECT @Cols='',@Sql=''
  
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN d END),[t'+@Cols+']=max(CASE WHEN .Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'


EXEC('SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM #Cte AS a LEFT JOIN #Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a,b,c ')
 

 
IF OBJECT_ID('Tempdb..#Cte') IS NOT NULL
	DROP TABLE #Cte

#12

create table T ([date] datetime,[time] varchar(100),a int ,b int ,c int ,d int)
insert into T select '2016/1/21','time1',7,6,1,2
insert into T select '2016/1/21','time2',7,6,1,1
insert into T select '2016/1/21','time3',7,6,1,0
insert into T select '2016/1/21','time4',7,6,1,2
insert into T select '2016/1/21','time5',7,6,1,1
insert into T select '2016/1/21','time6',7,6,1,0
go

select identity(int,0,1) as id,[date],[time] as [time],a,b,c,convert (varchar,d)as d into #T from T

declare @id varchar(100),@d varchar(100),@time varchar(100)

update #T set 
@d=case when @id=id/3 then @d+'.'+d else d end,
@time=case when @id=id/3 then @time+'.'+[time] else [time] end,
d=@d,
[time]=@time ,
@id=id/3


select [date],parsename([time],3) as [time],A,B,c,d 
       ,parsename(d,3) as d1
       ,parsename([time],2)+'-'+parsename([time],3) t1
       ,parsename(d,2) as d2
       ,parsename([time],1)+'-'+parsename([time],2) t1
       ,parsename(d,1) as d3
from #t where id%3=2

go
drop table t
drop table #T
/*
date time A B c d d1 t1 d2 t1 d3
2016-01-21 00:00:00.000 time1 7 6 1 2.1.0 2 time2-time1 1 time3-time2 0
2016-01-21 00:00:00.000 time4 7 6 1 2.1.0 2 time5-time4 1 time6-time5 0
*/

#13

上面语句有敲错几个位置，等等检查后贴出来
[time] varchar(100)--这是字符串？不可以做直接做加减

#14

引用 12 楼 xueke_123 的回复:

create table T ([date] datetime,[time] varchar(100),a int ,b int ,c int ,d int)
insert into T select '2016/1/21','time1',7,6,1,2
insert into T select '2016/1/21','time2',7,6,1,1
insert into T select '2016/1/21','time3',7,6,1,0
insert into T select '2016/1/21','time4',7,6,1,2
insert into T select '2016/1/21','time5',7,6,1,1
insert into T select '2016/1/21','time6',7,6,1,0
go

select identity(int,0,1) as id,[date],[time] as [time],a,b,c,convert (varchar,d)as d into #T from T

declare @id varchar(100),@d varchar(100),@time varchar(100)

update #T set 
@d=case when @id=id/3 then @d+'.'+d else d end,
@time=case when @id=id/3 then @time+'.'+[time] else [time] end,
d=@d,
[time]=@time ,
@id=id/3


select [date],parsename([time],3) as [time],A,B,c,d 
       ,parsename(d,3) as d1
       ,parsename([time],2)+'-'+parsename([time],3) t1
       ,parsename(d,2) as d2
       ,parsename([time],1)+'-'+parsename([time],2) t1
       ,parsename(d,1) as d3
from #t where id%3=2

go
drop table t
drop table #T
/*
date time A B c d d1 t1 d2 t1 d3
2016-01-21 00:00:00.000 time1 7 6 1 2.1.0 2 time2-time1 1 time3-time2 0
2016-01-21 00:00:00.000 time4 7 6 1 2.1.0 2 time5-time4 1 time6-time5 0
*/

谢谢，但是你没看清问题。time是datetime类型，time1--time6为类似“15:05:28”的时间，t1,t2为两个时间相减的值（datetime）

#15

--[time] varchar(100)显示为字符串起来，这栏位只有为datetime/date/time 格式才能直接

create table T ([date] datetime,[time] varchar(100),a int ,b int ,c int ,d int)
insert into T select '2016/1/21','time1',7,6,1,2
insert into T select '2016/1/21','time2',7,6,1,1
insert into T select '2016/1/21','time3',7,6,1,0
insert into T select '2016/1/21','time4',7,6,1,2
insert into T select '2016/1/21','time5',7,6,1,1
insert into T select '2016/1/21','time6',7,6,1,0
GO

DECLARE @Cols NVARCHAR(3),@Sql VARCHAR(4000)
SELECT @Cols='',@Sql=''
   
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN a.d END),[t'+@Cols+']=max(CASE WHEN a.Ord='+@Cols+' THEN ISNULL(a.time+''-''+b.time,a.time) END)'+@Sql,@Cols=@Cols-1
	PRINT @Sql
exec( ';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a.[date],a.a,a.b,a.c ')
 
 /*
 date	a	b	c	d1	t1	d2	t2	d3	t3	d4	t4	d5	t5	d6	t6
2016-01-21 00:00:00.000	7	6	1	2	time1	1	time2-time1	0	time3-time2	2	time4-time3	1	time5-time4	0	time6-time5
*/

#16

datetime类型，红色位置改改，看看是不是这样显示

DECLARE @Cols NVARCHAR(3),@Sql VARCHAR(4000)
SELECT @Cols='',@Sql=''
   
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN a.d END),[t'+@Cols+']=max(CASE WHEN a.Ord='+@Cols+' THEN ISNULL( a.time-b.time,a.time) END)'+@Sql,@Cols=@Cols-1

exec( ';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a.[date],a.a,a.b,a.c ')
 

#17

引用 16 楼 roy_88 的回复:

datetime类型，红色位置改改，看看是不是这样显示

DECLARE @Cols NVARCHAR(3),@Sql VARCHAR(4000)
SELECT @Cols='',@Sql=''
   
SELECT TOP 1 @Cols=COUNT(1) FROM T GROUP BY [date],a,b,c ORDER BY COUNT(1) DESC
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN a.d END),[t'+@Cols+']=max(CASE WHEN a.Ord='+@Cols+' THEN ISNULL( a.time-b.time,a.time) END)'+@Sql,@Cols=@Cols-1

exec( ';WITH Cte
AS
(
SELECT *,Ord=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC) FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a.[date],a.a,a.b,a.c ')
 

版主，这个能运行正常了，只是排序后没有每隔三行合并一次，

#18

DECLARE @Cols NVARCHAR(3),@Sql VARCHAR(4000)
SELECT @Cols=3,@Sql=''
    
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN a.d END),[t'+@Cols+']=max(CASE WHEN a.Ord='+@Cols+' THEN ISNULL(a.time+''-''+b.time,a.time) END)'+@Sql,@Cols=@Cols-1
    PRINT @Sql
exec( ';WITH Cte
AS
(
SELECT *,Ord=(ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC)-1)%3+1,Grp=(ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC)-1)/3 FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord=b.Ord+1 GROUP BY a.[date],a.a,a.b,a.c,a.Grp ')
  

  /*
date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	2	time1	1	time2-time4	0	time3-time5
2016-01-21 00:00:00.000	7	6	1	2	time4	1	time5-time4	0	time6-time5
*/

#19

--[time] DATETIME
是这样，看看显示结果

time2-time1--显示格式是？列出现有数据格式和显示效果

如果上一条没有time1是怎么显示？

这里提供方法，自己处理显示多式

create table T ([date] datetime,[time] DATETIME,a int ,b int ,c int ,d int)
insert into T select '2016/1/21',DATEADD(hh,-5,GETDATE()-1),7,6,1,2
insert into T select '2016/1/21',DATEADD(hh,-4,GETDATE()-1),7,6,1,1
insert into T select '2016/1/21',DATEADD(hh,-3,GETDATE()-1),7,6,1,0
insert into T select '2016/1/21',DATEADD(hh,-2,GETDATE()-1),7,6,1,2
insert into T select '2016/1/21',DATEADD(hh,-1,GETDATE()-1),7,6,1,1
insert into T select '2016/1/21',GETDATE(),7,6,1,0
GO

DECLARE @Cols NVARCHAR(3),@Sql VARCHAR(4000)
SELECT @Cols=3,@Sql=''
     
WHILE @Cols>0
    SELECT @Sql=',[d'+@Cols+']=MAX(CASE WHEN a.Ord='+@Cols+' THEN a.d END),[t'+@Cols+']=max(CASE WHEN a.Ord='+@Cols+' THEN ISNULL(a.time-b.time,a.time) END)'+@Sql,@Cols=@Cols-1
    PRINT @Sql
exec( ';WITH Cte
AS
(
SELECT *,Ord2=ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC),Ord=(ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC)-1)%3+1,Grp=(ROW_NUMBER()OVER(PARTITION BY [date],a,b,c ORDER BY [time] ASC)-1)/3 FROM T
)
SELECT a.[date],a.a,a.b,a.c'+@Sql+' FROM Cte AS a LEFT JOIN Cte AS b ON a.[date]=b.[date] and a.a=b.a AND a.b=b.b AND a.c=b.c AND a.Ord2=b.Ord2+1 GROUP BY a.[date],a.a,a.b,a.c,a.Grp ')
 
 /*
date	a	b	c	d1	t1	d2	t2	d3	t3
2016-01-21 00:00:00.000	7	6	1	2	2016-01-21 11:44:11.897	1	1900-01-01 01:00:00.000	0	1900-01-01 01:00:00.003
2016-01-21 00:00:00.000	7	6	1	2	1900-01-01 01:00:00.000	1	1900-01-01 01:00:00.000	0	1900-01-02 01:00:00.003
*/
--DROP TABLE T

#20

• SQL 多行拼成一行 (3种解决方案)
• sql实现多行合并一行