想要請教 CTE & CTE2 是我的 table 嗎 ??
LoginTime 並沒在我的欄位內

感謝解式

想要請教 CTE & CTE2 是我的 table 嗎 ??

不是，你把它當成變數保存查詢就可以
查詢也可以改成子查詢

select sum(totallogintime+TotalldleTime) /*進行totallogintime & totalidletime加總*/ 
    as totalSum 
from (
	select * from (
		select row_number() over (
			partition by BeginlimePeriodDt --BeginlimePeriodDt同一時間區間分組
			order by Service desc /*Service倒序，以便後面取最大第一筆*/
		) rnk,* 
		from TestTable	
	)T1 where rnk = 1 /*取Service最大第一筆*/
) T2

LoginTime 並沒在我的欄位內

我把LoginTime改成TestTable表格了

測試連結 : SQL Fiddle

Sorry,我是 SQL 門外漢

我用了列語法
select sum(totallogintime) /進行totallogintime & totalidletime加總/
as totalSum
from (
select * from (
select row_number() over (
partition by BeginlimePeriodDt --BeginlimePeriodDt同一時間區間分組
order by Service_id desc /Service倒序，以便後面取最大第一筆/
) rnk,*
from AgentActivitySummary
)T1 where rnk = 1 /取Service最大第一筆/
) T2

出現錯誤訊息
Msg 207, Level 16, State 1, Line 6
Invalid column name 'BeginlimePeriodDt'.

它的訊息說 欄位名稱錯誤
可以貼你正確表格結構上來嗎?
還有說明你是哪個資料庫，哪個版本

這樣可以幫助解題

大小寫問題吧 加個雙引號改成 partition by "BeginlimePeriodDt"試試

您好
我目前是使用 SQL 2012 R2 版本
DB 名稱是 AgentActivitySummary
欄位如下
BeginTimePeriodDt User_Id Service_Id LoginDt WorkGroup_Id EndTimePeriodDt Site_Id TenantId TotalLoginTime TotalIdleTime

DB 欄位長相如下圖

我目前使用的語法如下
select a.user_id
, sum(case when (a.service_id ='0') then a.TotalLoginTime else null end) as TotalLoginTime
, sum(case when (a.service_id ='0') then a.TotalIdleTime else null end) as TotalidleTime
, sum(case whenservice_id not in ('0') and a.service_id not in ('53000462')) then a.TotalLoginTime else null end) as ServiceLoginTime
, sum( case when (a.service_id not in ('0') and a.service_id not in ('53000462')) then a.TotalIdleTime else null end) as ServiceidleTime
--sum (TotalLoginTime) as total_login

from AgentActivitySummary a
where (a.BeginTimePeriodDt>='2018-10-03' and a.BeginTimePeriodDt <='2018-10-04')
--and a.user_id ='RY16013' --and (service_id not in ('0', '53000462'))
group by a.user_id

在第三欄和第四欄查詢的結果
需排除 service_id ('0') and ('53000462')
但其他 service_ID (53000485, 53000577) 我只要取其中一筆就好
希望查詢出來的的結果如下圖

謝謝大家的幫忙

我更新留言回答你新的問題了

to 暐翰
在每個時段補入
('2018-10-02 09:30:00', 'sl10048','53000485', 720, 200),
sl10048 不會出現,
partition by [BeginTimePeriodDt]
似乎要改成 partition by [BeginTimePeriodDt], [User_Id]

感謝，你說的對
我修正了

to 暐翰:
你的 SQL
http://sqlfiddle.com/#!18/722c0/1
我的 SQL
http://sqlfiddle.com/#!18/6772c/1

由 View Execution Plan 結果 , 你的 SQL 成本似乎比較低
讚喔

to 暐翰:
請問幾個問題:
1.我用 max ; 你用 row_number() over (partition by ..order by ..desc <= 取得第一筆前要先做排序
2.我直接 sum ; 你用 sum(case when ....<= sum 之前都要判斷
3.我用 full outer join +兩次 group by , 你用 union + 一次 group by <= 造成我的 SQL 成本較高的原因 ?
4.若此table 有 1 萬筆或是 10 萬筆資料時,跑出來的成本(或時間)會是如何 ? 你有辦法模擬測試嗎 ? 謝謝.

kenkotw iT邦新手 5 級 ‧ 2018-10-06 12:30:20 描述的需求又變了

CREATE TABLE LoginTime
([BeginTimePeriodDt] datetime, [User_Id] nvarchar(7), [Service_Id] nvarchar(8), [TotalLoginTime] int, [TotalIdleTime] int)
;
INSERT INTO LoginTime
([BeginTimePeriodDt], [User_Id],[Service_Id], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 'sl10047','0', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000462', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','0', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000462', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000577', 720, 200),
('2018-10-02 09:45:00', 'sl10047','0', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000462', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000485', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000577', 900, 400),
('2018-10-02 10:00:00', 'sl10047','0', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000462', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000485', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000577', 900, 0),
('2018-10-02 10:15:00', 'sl10047','0', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000462', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000485', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000577', 900, 0)
;

select X.User_Id,X.TotalLoginTime,X.TotalIdleTime,Y.ServiceLoginTime,Y.ServiceIdleTime
from (
select MA.User_Id,
sum(isnull(MA.TotalLoginTime,0)) as TotalLoginTime,
sum(isnull(MA.TotalIdleTime,0)) as TotalIdleTime
from LoginTime as MA
where 1=1
and (MA.BeginTimePeriodDt>='2018-10-01' and MA.BeginTimePeriodDt <='2018-10-04')
and MA.Service_Id='0'
group by MA.User_Id
) as X
full outer join (
select B.User_Id,sum(isnull(B.TotalLoginTime,0)) as ServiceLoginTime,sum(isnull(B.TotalIdleTime,0)) as ServiceIdleTime
from LoginTime as B
inner join (
select A.User_Id,A.BeginTimePeriodDt,
max(A.Service_Id) as Service_Id
from LoginTime as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id <>'0'
and A.Service_Id <>'53000462'
group by A.User_Id,A.BeginTimePeriodDt) as C on C.User_Id=B.User_Id
and C.BeginTimePeriodDt=B.BeginTimePeriodDt
and C.Service_Id=B.Service_Id
where 1=1
group by B.User_Id
) as Y on Y.User_Id=X.User_Id
where 1=1
order by X.User_Id

對，需求又變了...

修正
select X.User_Id,
改為
select
case when X.User_Id is null then Y.User_Id
else X.User_Id end as User_Id,

order by X.User_Id
改為
order by User_Id

感謝各位的協助，我再試試看

rogeryao 您好
我目前使用您提供語法是可以執行的
但目前有發現資料裏同一時間區段內在不同 service_id 會有不同的 Totallogintime,希望可以取 Totallogintime 最大值的那筆
我標示為粗體那筆 Totallogintime 改為 900 秒
語法應如可調整

('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),

目前的語法如下

select X.User_Id,X.TotalLoginTime,X.TotalIdleTime,Y.ServiceLoginTime,Y.ServiceIdleTime
from (
select MA.User_Id,
sum(isnull(MA.TotalLoginTime,0)) as TotalLoginTime,
sum(isnull(MA.TotalIdleTime,0)) as TotalIdleTime
--sum(case when (MA.service_id not in ('53000566','53000567')) then MA.TotalLoginTime else null end) as SACBLoginTime
--sum(case when (MA.service_id not in ('53000566','53000567')) then MA.TotalLoginTime else null end) as SACBidleTime
from AgentActivitySummary as MA
where 1=1
and (MA.BeginTimePeriodDt>='2018-10-03' and MA.BeginTimePeriodDt <='2018-10-04')
and MA.Service_Id='0'
group by MA.User_Id
) as X
full outer join (
select B.User_Id,sum(isnull(B.TotalLoginTime,0)) as ServiceLoginTime,sum(isnull(B.TotalIdleTime,0)) as ServiceIdleTime
from AgentActivitySummary as B
inner join (
select A.User_Id,A.BeginTimePeriodDt,
--max(A.totallogintime) as Service_Id
max(A.Service_Id) as Service_Id
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-03' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485') --不計算 MANUAL, SACB, AutoAgent
group by A.User_Id,A.BeginTimePeriodDt) as C on C.User_Id=B.User_Id
and C.BeginTimePeriodDt=B.BeginTimePeriodDt
--and C.totallogintime=B.totallogintime
and C.Service_Id=B.Service_Id
where 1=1
group by B.User_Id
) as Y on Y.User_Id=X.User_Id
where 1=1
order by X.User_Id

感謝您的協助

需求又變了......快猜不出來你要計算什麼了

假設需求為:每個User_ID在每個時間,不分登入那個Service_Id,其TotalLoginTime 及 TotalIdleTime 皆以最大值,計算總花費時間

CREATE TABLE AgentActivitySummary
([BeginTimePeriodDt] datetime, [User_id] varchar(7), [Service_Id] int, [LoginDt] datetime, [WorkGroup_Id] int, [EndTimePeriodDt] varchar(23)
, [Site_Id] int, [TenantId] int, [TotalLoginTime] int, [TotalIdleTime] int);

INSERT INTO AgentActivitySummary
([BeginTimePeriodDt], [User_id], [Service_Id], [LoginDt], [WorkGroup_Id], [EndTimePeriodDt], [Site_Id], [TenantId], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 90),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000486, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000487, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 20)
;

select distinct V0.User_Id,
V1.TotalLoginTime,V2.TotalIdleTime,
V3.ServiceLoginTime,V4.ServiceIdleTime
from AgentActivitySummary as V0
-- A.Service_Id='0' & TotalLoginTime
left join
(
select K1.User_Id,
sum(K1.TotalLoginTime) as TotalLoginTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalLoginTime) as TotalLoginTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K1
where 1=1
Group by K1.User_Id
) as V1 on V1.User_Id=V0.User_Id
-- A.Service_Id='0' & TotalIdleTime
left join
(
select K2.User_Id,
sum(K2.TotalIdleTime) as TotalIdleTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K2
where 1=1
Group by K2.User_Id
) as V2 on V2.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceLoginTime
left join
(
select K3.User_Id,
sum(K3.TotalLoginTime) as ServiceLoginTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalLoginTime) as TotalLoginTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K3
where 1=1
Group by K3.User_Id
) V3 on V3.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceIdleTime
left join
(
select K4.User_Id,
sum(K4.TotalIdleTime) as ServiceIdleTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K4
where 1=1
Group by K4.User_Id
) V4 on V4.User_Id=V0.User_Id
order by V0.User_Id

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=678100406f83d6a85486202a21e94a45