求助，我该如何用 sql 实现这种报表排版？

KagamineLenKai2 2016-10-28 05:38:22
node | apply_id | time | operator_name
1 | 10000 | 2016/10/28 15:24 | Clarkson
2 | 10000 | 2016/10/28 15:25 | May
3 | 10000 | 2016/10/28 15:26 | May
1 | 10000 | 2016/10/28 15:27 | Clarkson
2 | 10000 | 2016/10/28 15:28 | May
3 | 10000 | 2016/10/28 15:29 | May
1 | 10000 | 2016/10/28 15:30 | Clarkson
2 | 10000 | 2016/10/28 15:31 | May
3 | 10000 | 2016/10/28 15:32 | May
1 | 10000 | 2016/10/28 15:33 | Clarkson
2 | 10000 | 2016/10/28 15:34 | May

这是我需要查询的原表，实际业务中使用的表要复杂很多。 node 1 为起点， 1 结束后进入 2 ，如果 2 退回 1 的话会生成 3

apply_id | 1_time | 1_operator_name | 2_time | 2_operator_name | 3_time | 3_operator_name
10000 | 2016/10/28 15:24 | Clarkson | 2016/10/28 15:25 | May | 2016/10/28 15:26 | May
10000 | 2016/10/28 15:27 | Clarkson | 2016/10/28 15:28 | May | 2016/10/28 15:29 | May
10000 | 2016/10/28 15:30 | Clarkson | 2016/10/28 15:31 | May | 2016/10/28 15:32 | May
10000 | 2016/10/28 15:33 | Clarkson | 2016/10/28 15:34 | May

这是我想生成的表。但是我不知道语句该怎么写才能排成这个样子……
现在的做法是先把原表按 node 分成 1 2 3 三个表，再 left join on apply_id
不过这样出来的结果是三个表排列组合的所有结果，一共 4×4×3=48 行，所以大概要在 time 那里加个判断？
请教各位 dalao 该怎么实现…蟹蟹…/(ㄒoㄒ)/~~
...全文
320 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
KagamineLenKai2 2016-10-28
  • 打赏
  • 举报
 回复
引用 4 楼 roy_88 的回复:
固定值node只有1，2，3时这样写 
SELECT  [apply_id] ,
        [1_time] = MAX(CASE WHEN [node] = 1
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [1_operator_name] = MAX(CASE WHEN [node] = 1 THEN [operator_name]
                                     ELSE ''
                                END) ,
        [2_time] = MAX(CASE WHEN [node] = 2
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [2_operator_name] = MAX(CASE WHEN [node] = 2 THEN [operator_name]
                                     ELSE ''
                                END) ,
        [3_time] = MAX(CASE WHEN [node] = 3
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [3_operator_name] = MAX(CASE WHEN [node] = 3 THEN [operator_name]
                                     ELSE ''
                                END)
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY apply_id, node ORDER BY [time] ) AS Grp
          FROM      #T
        ) AS t
GROUP BY [apply_id] ,
        Grp;
好好，我看一下，蟹蟹/(ㄒoㄒ)/~~
中国风 2016-10-28
  • 打赏
  • 举报
 回复
固定值node只有1，2，3时这样写 
SELECT  [apply_id] ,
        [1_time] = MAX(CASE WHEN [node] = 1
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [1_operator_name] = MAX(CASE WHEN [node] = 1 THEN [operator_name]
                                     ELSE ''
                                END) ,
        [2_time] = MAX(CASE WHEN [node] = 2
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [2_operator_name] = MAX(CASE WHEN [node] = 2 THEN [operator_name]
                                     ELSE ''
                                END) ,
        [3_time] = MAX(CASE WHEN [node] = 3
                            THEN CONVERT(VARCHAR(16), [time], 120)
                            ELSE ''
                       END) ,
        [3_operator_name] = MAX(CASE WHEN [node] = 3 THEN [operator_name]
                                     ELSE ''
                                END)
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY apply_id, node ORDER BY [time] ) AS Grp
          FROM      #T
        ) AS t
GROUP BY [apply_id] ,
        Grp;
KagamineLenKai2 2016-10-28
  • 打赏
  • 举报
 回复
引用 1 楼 roy_88 的回复:
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([node] int,[apply_id] int,[time] Datetime,[operator_name] nvarchar(28))
Insert #T
select 1,10000,'2016/10/28 15:24',N'Clarkson' union all
select 2,10000,'2016/10/28 15:25',N'May' union all
select 3,10000,'2016/10/28 15:26',N'May' union all
select 1,10000,'2016/10/28 15:27',N'Clarkson' union all
select 2,10000,'2016/10/28 15:28',N'May' union all
select 3,10000,'2016/10/28 15:29',N'May' union all
select 1,10000,'2016/10/28 15:30',N'Clarkson' union all
select 2,10000,'2016/10/28 15:31',N'May' union all
select 3,10000,'2016/10/28 15:32',N'May' union all
select 1,10000,'2016/10/28 15:33',N'Clarkson' union all
select 2,10000,'2016/10/28 15:34',N'May'
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+',[time'+RTRIM([node])+']=max(case when [node]='+RTRIM([node])+' then convert(varchar(16),[time],120) else '''' end)'
+',[operator_name'+RTRIM([node])+']=max(case when [node]='+RTRIM([node])+' then [operator_name] else '''' end)'
 from #T GROUP BY [node]

 EXEC('SELECT [apply_id]'+@Sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY apply_id,node ORDER BY [time]) AS Grp FROM #T) as t group by [apply_id],Grp')

 /*
 apply_id	time1	operator_name1	time2	operator_name2	time3	operator_name3
10000	2016-10-28 15:24	Clarkson	2016-10-28 15:25	May	2016-10-28 15:26	May
10000	2016-10-28 15:27	Clarkson	2016-10-28 15:28	May	2016-10-28 15:29	May
10000	2016-10-28 15:30	Clarkson	2016-10-28 15:31	May	2016-10-28 15:32	May
10000	2016-10-28 15:33	Clarkson	2016-10-28 15:34	May		
*/
这就是传说中的动态SQL吗，不过我是用navicat手写查询的，这么多select大概是写不来…(lll￢ω￢)
中国风 2016-10-28
  • 打赏
  • 举报
 回复
调整栏位名显示，把数字显示在前 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([node] int,[apply_id] int,[time] Datetime,[operator_name] nvarchar(28))
Insert #T
select 1,10000,'2016/10/28 15:24',N'Clarkson' union all
select 2,10000,'2016/10/28 15:25',N'May' union all
select 3,10000,'2016/10/28 15:26',N'May' union all
select 1,10000,'2016/10/28 15:27',N'Clarkson' union all
select 2,10000,'2016/10/28 15:28',N'May' union all
select 3,10000,'2016/10/28 15:29',N'May' union all
select 1,10000,'2016/10/28 15:30',N'Clarkson' union all
select 2,10000,'2016/10/28 15:31',N'May' union all
select 3,10000,'2016/10/28 15:32',N'May' union all
select 1,10000,'2016/10/28 15:33',N'Clarkson' union all
select 2,10000,'2016/10/28 15:34',N'May'
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+',['+RTRIM([node])+'_time]=max(case when [node]='+RTRIM([node])+' then convert(varchar(16),[time],120) else '''' end)'
+',['+RTRIM([node])+'_operator_name]=max(case when [node]='+RTRIM([node])+' then [operator_name] else '''' end)'
 from #T GROUP BY [node]

 EXEC('SELECT [apply_id]'+@Sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY apply_id,node ORDER BY [time]) AS Grp FROM #T) as t group by [apply_id],Grp')

 /*
 apply_id	1_time	1_operator_name	2_time	2_operator_name	3_time	3_operator_name
10000	2016-10-28 15:24	Clarkson	2016-10-28 15:25	May	2016-10-28 15:26	May
10000	2016-10-28 15:27	Clarkson	2016-10-28 15:28	May	2016-10-28 15:29	May
10000	2016-10-28 15:30	Clarkson	2016-10-28 15:31	May	2016-10-28 15:32	May
10000	2016-10-28 15:33	Clarkson	2016-10-28 15:34	May		
*/
中国风 2016-10-28
  • 打赏
  • 举报
 回复 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([node] int,[apply_id] int,[time] Datetime,[operator_name] nvarchar(28))
Insert #T
select 1,10000,'2016/10/28 15:24',N'Clarkson' union all
select 2,10000,'2016/10/28 15:25',N'May' union all
select 3,10000,'2016/10/28 15:26',N'May' union all
select 1,10000,'2016/10/28 15:27',N'Clarkson' union all
select 2,10000,'2016/10/28 15:28',N'May' union all
select 3,10000,'2016/10/28 15:29',N'May' union all
select 1,10000,'2016/10/28 15:30',N'Clarkson' union all
select 2,10000,'2016/10/28 15:31',N'May' union all
select 3,10000,'2016/10/28 15:32',N'May' union all
select 1,10000,'2016/10/28 15:33',N'Clarkson' union all
select 2,10000,'2016/10/28 15:34',N'May'
GO
DECLARE @Sql NVARCHAR(max)=''
Select @Sql=@Sql+',[time'+RTRIM([node])+']=max(case when [node]='+RTRIM([node])+' then convert(varchar(16),[time],120) else '''' end)'
+',[operator_name'+RTRIM([node])+']=max(case when [node]='+RTRIM([node])+' then [operator_name] else '''' end)'
 from #T GROUP BY [node]

 EXEC('SELECT [apply_id]'+@Sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY apply_id,node ORDER BY [time]) AS Grp FROM #T) as t group by [apply_id],Grp')

 /*
 apply_id	time1	operator_name1	time2	operator_name2	time3	operator_name3
10000	2016-10-28 15:24	Clarkson	2016-10-28 15:25	May	2016-10-28 15:26	May
10000	2016-10-28 15:27	Clarkson	2016-10-28 15:28	May	2016-10-28 15:29	May
10000	2016-10-28 15:30	Clarkson	2016-10-28 15:31	May	2016-10-28 15:32	May
10000	2016-10-28 15:33	Clarkson	2016-10-28 15:34	May		
*/
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章