SQL语句问题

[x_maizi]

现在有一张监控表以下字段：事务类型(TrType)，事务ID(TrId)，任务名称(TaskId)，任务状态(TaskStatus)，时间(暂时以int代替)；
每个事务下有多个子任务顺序执行（以时间为参考顺序），子任务数量不固定；



1、现在想要得到按类型、统计每个节点消耗时间的均值：最好如下

事务类型 、子任务1（平均时间），子任务2（平均时间），子任务3（平均时间），事务总体（平均时间）
A， 1， 1， 1， 3
B，……



备：初始化语句：

--创建表
CREATE TABLE [dbo].[TrTask](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TrType] [nvarchar](50) NULL,
	[TrId] [int] NULL,
	[TaskName] [nvarchar](50) NULL,
	[TaskStatus] [nvarchar](50) NULL,
	[Score] [int] NULL,
 CONSTRAINT [PK_TrTask] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



--初始化数据

insert into [dbo].[TrTask]
values
(	'A',	1,	'事务开始',	'allstart',	101)
,(	'A',	1,	'子任务1',	'start',	102)
,(	'A',	1,	'子任务1',	'end',	103)
,(	'A',	1,	'子任务2',	'start',	104)
,(	'A',	1,	'子任务2',	'end',	105)
,(	'A',	1,	'子任务3',	'start',	106)
,(	'A',	1,	'子任务3',	'end',	107)
,(	'A',	1,	'事务结束',	'allend',	108)
,(	'A',	2,	'事务开始',	'allstart',	109)
,(	'A',	2,	'子任务1',	'start',	110)
,(	'A',	2,	'子任务1',	'end',	111)
,(	'A',	2,	'子任务2',	'start',	112)
,(	'A',	2,	'子任务2',	'end',	113)
,(	'A',	2,	'子任务3',	'start',	114)
,(	'A',	2,	'子任务3',	'end',	115)
,(	'A',	2,	'事务结束',	'allend',	116)
,(	'B',	3,	'事务开始',	'allstart',	109)
,(	'B',	3,	'子任务1',	'start',	110)
,(	'B',	3,	'子任务1',	'end',	111)
,(	'B',	3,	'子任务2',	'start',	112)
,(	'B',	3,	'子任务2',	'end',	113)
,(	'B',	3,	'子任务3',	'start',	114)
,(	'B',	3,	'子任务3',	'end',	115)
,(	'B',	3,	'事务结束',	'allend',	116)

[二月十六]

DECLARE @sql VARCHAR(MAX)
SET @sql = 'select TrType'
SELECT  @sql = @sql + ',max(case TaskName when ''' + TaskName
        + ''' then Score else 0 end)[' + TaskName + ']'
FROM    ( SELECT DISTINCT
                    TaskName
          FROM      [TrTask]
		  WHERE TaskName LIKE '子%'
        ) a
SET @sql = @sql
    + ',avg(Score) as 事务总体 from [TrTask] group by TrType'
EXEC(@sql)

[x_maizi]

引用 2 楼 二月十六 的回复:

DECLARE @sql VARCHAR(MAX)
SET @sql = 'select TrType'
SELECT  @sql = @sql + ',max(case TaskName when ''' + TaskName
        + ''' then Score else 0 end)[' + TaskName + ']'
FROM    ( SELECT DISTINCT
                    TaskName
          FROM      [TrTask]
		  WHERE TaskName LIKE '子%'
        ) a
SET @sql = @sql
    + ',avg(Score) as 事务总体 from [TrTask] group by TrType'
EXEC(@sql)

谢谢，但是问题之处在于不能用like ’子‘ 这样的，名字没有规律的；还有一个子任务消耗时间是end-start；

[二月十六]

引用 3 楼 x_maizi 的回复:

[quote=引用 2 楼 二月十六 的回复:]

DECLARE @sql VARCHAR(MAX)
SET @sql = 'select TrType'
SELECT  @sql = @sql + ',max(case TaskName when ''' + TaskName
        + ''' then Score else 0 end)[' + TaskName + ']'
FROM    ( SELECT DISTINCT
                    TaskName
          FROM      [TrTask]
		  WHERE TaskName LIKE '子%'
        ) a
SET @sql = @sql
    + ',avg(Score) as 事务总体 from [TrTask] group by TrType'
EXEC(@sql)

谢谢，但是问题之处在于不能用like ’子‘ 这样的，名字没有规律的；还有一个子任务消耗时间是end-start；[/quote] 行转列的基本写法就是这样，至于具体读取哪个like 什么，或者干脆不like根据实际数据进行处理

[tom毛毛]

记性差很难受

[邹小青]

《SQL难点解决：集合及行号》这里有一系列关于sql难点的文章，看看能否帮到您？

[Dear SQL(燊)]

--创建表
CREATE TABLE [dbo].[TrTask](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TrType] [nvarchar](50) NULL,
    [TrId] [int] NULL,
    [TaskName] [nvarchar](50) NULL,
    [TaskStatus] [nvarchar](50) NULL,
    [Score] [int] NULL,
 CONSTRAINT [PK_TrTask] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
 
--初始化数据
 
insert into [dbo].[TrTask]
values
(    'A',    1,    '事务开始',    'allstart',    101)
,(    'A',    1,    '子任务1',    'start',    102)
,(    'A',    1,    '子任务1',    'end',    103)
,(    'A',    1,    '子任务2',    'start',    104)
,(    'A',    1,    '子任务2',    'end',    105)
,(    'A',    1,    '子任务3',    'start',    106)
,(    'A',    1,    '子任务3',    'end',    107)
,(    'A',    1,    '事务结束',    'allend',    108)
,(    'A',    2,    '事务开始',    'allstart',    109)
,(    'A',    2,    '子任务1',    'start',    110)
,(    'A',    2,    '子任务1',    'end',    111)
,(    'A',    2,    '子任务2',    'start',    112)
,(    'A',    2,    '子任务2',    'end',    113)
,(    'A',    2,    '子任务3',    'start',    114)
,(    'A',    2,    '子任务3',    'end',    115)
,(    'A',    2,    '事务结束',    'allend',    116)
,(    'B',    3,    '事务开始',    'allstart',    109)
,(    'B',    3,    '子任务1',    'start',    110)
,(    'B',    3,    '子任务1',    'end',    111)
,(    'B',    3,    '子任务2',    'start',    112)
,(    'B',    3,    '子任务2',    'end',    113)
,(    'B',    3,    '子任务3',    'start',    114)
,(    'B',    3,    '子任务3',    'end',    115)
,(    'B',    3,    '事务结束',    'allend',    116)




select TrType,1 as ttype,[TaskName],sum([Score]*(case when [TaskStatus]='start' then -1 else 1 end))*1.0/count(distinct [TrId]) as times
into #list
from [TrTask] 
where [TaskStatus] in('start','end')
group by TrType,[TaskName]
union all
select TrType,2 as ttype,[TaskName]='总体事务',sum([Score]*(case when [TaskStatus]='start' then -1 else 1 end))*1.0/count(distinct [TrId]) as times
from [TrTask] 
where [TaskStatus] in('start','end')
group by TrType

declare @colname nvarchar(4000)='',@sql nvarchar(max)=''
;
with list as(
	select distinct ttype,[TaskName]
	from #list 
)
select @colname =@colname+',['+[TaskName]+']'
from list
order by ttype,[TaskName] 

set @sql='
select TrType'+@colname+'
from (
	select TrType,[TaskName],times
	from #list) a pivot(sum(times) for [TaskName] in('+RIGHT(@colname,len(@colname)-1)+')) pt

'
exec(@sql)

TrType                                             子任务1                                    子任务2                                    子任务3                                    总体事务
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
A                                                  1.000000000000                          1.000000000000                          1.000000000000                          3.000000000000
B                                                  1.000000000000                          1.000000000000                          1.000000000000                          3.000000000000

[morliz子轩]

继续爬楼