返回多行的子查询
返回多行的子查询
提问于 2018-04-12 22:32:29
我对此已经有一段时间了。查看fe选项，以显示以下结果集。想要显示的ProdOp，OpSUM，然后PCT (我们将从总时间减去t.opcode LL。这是漫长的一天，我想这可能是因为潜艇不能返回多行？如果没有其他方法的话？交叉加入？
预期结果(PCT与实际百分比)，我们从TimeSUM查询中得到百分比，即7:59，然后除以每个OpSUM。
ProdOp OpSUM PCT
BB 0:20 4.2%
DF 1:15 15.6%
HF 0:10 2.1%
HR 0:25 5.2%
JT 0:14 2.9%
MM 0:27 5.6%
NW 0:39 8.1%
PE 0:21 4.4%
PX 0:45 9.4%
SP 2:52 35.9%
SS 0:31 6.5%当前的方法.
DECLARE
@Now DATETIME ,
@Start DATETIME ,
@End DATETIME ,
@Final VARCHAR (50),
@Offset INT
SET @Offset = 1
SET @Now = GETDATE()-@Offset
SET @Start = dateadd(day, datediff(day, 0, getdate()), -@Offset)
SET @End = dateadd(day, datediff(day, 0, getdate()), -@Offset)
select [ProdOp], [OpSUM], [TimeSUM], Cast(Cast((OpSUM/TimeSUM)*100 as decimal(10,1)) as varchar(5)) + ' %' as [PCT]
from
select(
(select t.OpCode
FROM MaintTimeLog t
join AssociateInfo ai
on t.ID = ai.ID
where t.EventDate >= @Start and t.EventDate <= @End and t.OpCode NOT IN ('SS', 'LL', 'BB', 'MM')
group by ai.FirstName, t.OpCode
) as ProdOp,
(select right(space(5)+rtrim(right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':'
+ right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2)),5)
FROM MaintTimeLog t
join AssociateInfo ai
on t.ID = ai.ID
where t.EventDate >= @Start and t.EventDate <= @End and ai.FirstName = 'R' and t.OpCode NOT IN('SS', 'LL', 'BB', 'MM')
group by ai.FirstName, t.OpCode
) as OpSUM,
(select right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':'
+ right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2)
FROM MaintTimeLog t
join AssociateInfo ai
on t.ID = ai.ID
where t.EventDate >= @Start and t.EventDate <= @End and ai.FirstName = 'R' and t.opcode <>'LL'
group by ai.FirstName
) as TimeSUM
from MaintTimeLog t
join AssociateInfo ai
on t.ID = ai.ID
where EventDate >= @Start and EventDate <= @End and ai.FirstName = 'R'
group by ai.FirstName
)qMaintTimeLog**sample数据**
CREATE TABLE [dbo].[MaintTimeLog](
[EventDate] [varchar](15) NOT NULL,
[ID] [varchar](7) NOT NULL,
[DeptCode] [varchar](2) NOT NULL,
[OpCode] [varchar](2) NOT NULL,
[StartTime] [time](0) NOT NULL,
[FinishTime] [time](0) NOT NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MaintTimeLog] WITH CHECK ADD CONSTRAINT [CK_Start_LessThan_Finsih_Maint] CHECK (([StartTime]<[FinishTime]))
GO
ALTER TABLE [dbo].[MaintTimeLog] CHECK CONSTRAINT [CK_Start_LessThan_Finsih_Maint]
GO
INSERT INTO [dbo].[MaintTimeLog]([EventDate],[ID] ,[DeptCode] ,[OpCode] ,[StartTime] ,[FinishTime]) VALUES
('4/11/2018', '57524', 'SS', 'SS', '6:00:00', '6:31:00'),
('4/11/2018', '57524', 'FP', 'SP', '6:31:00', '7:01:00'),
('4/11/2018', '57524', 'MM', 'MM', '7:01:00', '7:24:00'),
('4/11/2018', '57524', 'FS', 'SP', '7:24:00', '7:33:00'),
('4/11/2018', '57524', 'RC', 'JT', '7:33:00', '7:47:00'),
('4/11/2018', '57524', 'FS', 'SP', '7:47:00', '7:50:00'),
('4/11/2018', '57524', 'HP', 'SP', '7:50:00', '9:40:00'),
('4/11/2018', '57524', 'BB', 'BB', '9:40:00', '10:00:00'),
('4/11/2018', '57524', 'RQ', 'SP', '10:00:00', '10:20:00'),
('4/11/2018', '57524', 'DS', 'NW', '10:20:00', '10:59:00'),
('4/11/2018', '57524', 'FC', 'PE', '10:59:00', '11:20:00'),
('4/11/2018', '57524', 'MT', 'HF', '11:20:00', '11:30:00'),
('4/11/2018', '57524', 'LL', 'LL', '11:30:00', '12:01:00'),
('4/11/2018', '57524', 'TW', 'PX', '12:01:00', '12:46:00'),
('4/11/2018', '57524', 'MM', 'MM', '12:46:00', '12:50:00'),
('4/11/2018', '57524', 'FS', 'HR', '12:50:00', '13:15:00'),
('4/11/2018', '57524', 'HD', 'DF', '13:15:00', '14:30:00') AssociateInfo 样例数据
INSERT INTO [dbo].[AssociateInfo]([ID],[FirstName]) VALUES
('57524', 'R')回答 2
Stack Overflow用户
发布于 2018-04-14 23:29:58
我的计算是不同的，但我认为他们是正确的-我检查了他们与excel枢轴表，以获得一个百分比的总和。试试这个：
SELECT
OpCode,
convert(varchar, dateadd(s,sum(datediff(second,StartTime,FinishTime)), 0), 114),
SUM(datediff(second, StartTime, FinishTime)) * 100.0 / convert(decimal, t.total)
FROM
MaintTimeLog
CROSS JOIN (SELECT SUM(datediff(second,StartTime,FinishTime)) as total FROM MaintTimeLog) t
GROUP BY
OpCode,
t.totalconvert函数本身使用dateadd函数，只需要对结果进行格式化。您最初的尝试是对格式化结果的组件进行数学计算，而此版本则使用转换函数以格式化值的形式返回结果，而不修改基础值。Format 114指定所需的日期格式。
Stack Overflow用户
发布于 2018-04-15 02:50:00
@艾伦，我会给你充分的信任。我玩弄了你的方法，并能够产生我想要的东西。我想使用下面的日期时间格式的原因是出于干净的报告原因。我很感激你对此的帮助。我一开始以为需要交叉连接。再次感谢和欢呼！
DECLARE
@Start DATETIME ,
@End DATETIME ,
@Offset INT
SET @Offset = 3
SET @Start = dateadd(day, datediff(day, 0, getdate()), -@Offset)
SET @End = dateadd(day, datediff(day, 0, getdate()), -@Offset)
SELECT
ProdOp = OpCode,
OpSUM = right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':'
+ right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2),
PCT = cast(cast(SUM(datediff(second, StartTime, FinishTime)) * 100.0 / convert(decimal, t.total)as decimal(10,1)) as varchar(5)) + ' %'
FROM
MaintTimeLog
CROSS JOIN (SELECT SUM(datediff(second,StartTime,FinishTime)) as total FROM MaintTimeLog where EventDate >= @Start and EventDate <= @End and ID = 57524 and OpCode <> 'll') t
where EventDate >= @Start and EventDate <= @End and ID = 57524 and OpCode <> 'll'
GROUP BY
OpCode,
t.total
ProdOp OpSUM PCT
BB 0:20 4.2 %
DF 1:15 15.7 %
HF 0:10 2.1 %
HR 0:25 5.2 %
JT 0:14 2.9 %
MM 0:27 5.6 %
NW 0:39 8.1 %
PE 0:21 4.4 %
PX 0:45 9.4 %
SP 2:52 35.9 %
SS 0:31 6.5 % 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49806799
复制相关文章
相似问题
腾讯云开发者
