我有一個表，它只存盤針對用戶的登錄和注銷日期以及他們的時間和他們的資料型別是datetime.
日期以 格式存盤2021-12-17 14:05:25.597。
我能夠獲得登錄和注銷日期之間的總持續時間，但我需要將特定用戶的所有行與某個日期相加。
以下是一些示例資料：
Sno USERID Name LoginDateAndTime LogOutDateAndTime TimeDuration Total
-----------------------------------------------------------------------------------
1 222 abc 17 Dec 2021 1:40PM 17 Dec 2021 1:40PM 0:0:9
2 222 abc 17 Dec 2021 2:05PM 17 Dec 2021 3:07PM 1:2:4
3 444 xyz 18 Dec 2021 3:37PM 18 Dec 2021 3:37PM 0:0:6
4 444 xyz 16 Dec 2021 1:51PM 16 Dec 2021 2:22PM 0:30:24
5 444 xyz 16 Dec 2021 10:17PM 16 Dec 2021 10:18PM 0:1:20
這是我的查詢：
SELECT
u.userid, u.Name,
CONVERT(varchar, la.loginDate, 106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20), la.loginDate, 100), 7)) AS LoginDateAndTime,
CONVERT(varchar, la.logoutDate,106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20), la.logoutDate, 100), 7)) AS LogOutDateAndTime,
CONVERT(varchar(5), DATEDIFF(s, loginDate, logoutDate) / 3600) ':'
CONVERT(varchar(5), DATEDIFF(s, loginDate, logoutDate) % 3600 / 60) ':'
CONVERT(varchar(5), (DATEDIFF(s, loginDate, logoutDate) % 60)) AS 'TimeDuration',
FROM
login la
LEFT JOIN
users u ON u.userid = la.id
現在我想顯示一個新列，該列匯總了相同 ID 和相同日期的小時數和分鐘數。
我進行了多次搜索，但我仍然感到困惑。
例如：
針對
Id = 222有兩個條目具有相同的日期，這樣總數將0:0:9加上1:2:4這是1:2:13反對
Id = 444有兩個不同日期的三個條目，因此反對 Sno3總數將是相同的，0:0:6反對 Sno4，5這兩個將添加 ie0:30:24和0:1:20。
我怎樣才能做到這一點？任何人都可以幫助我嗎？它相當復雜
uj5u.com熱心網友回復：
根據您的小提琴，我也創建了一個，但目前我無法發表評論，因此將其發布為答案：
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3980a6b2eec812d82fe9ed43339981c2
--with-clause begins
--pre as (...) is the first query within the with-clause
with pre as (
select
USERID,
Name,
CAST(loginDate AS date) AS LoginData,
convert(varchar,loginDate,106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20),loginDate, 100), 7)) as LoginDateAndTime,
convert(varchar,logoutDate,106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20),logoutDate, 100), 7)) as LogOutDateAndTime,
convert(varchar(5),DateDiff(s, loginDate, logoutDate)/3600) ':' convert(varchar(5),DateDiff(s, loginDate, logoutDate)%3600/60) ':' convert(varchar(5),(DateDiff(s, loginDate, logoutDate)%60)) as 'Time Duration Of Login [hh:mm:ss]'
from
login
),
--total as (...) is the second query within the with-clause
total as (
SELECT
userid,
Name,
CAST(loginDate AS date) AS LoginData,
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) / 3600) ':'
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 3600 / 60) ':'
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 60) AS TotalDuration
FROM
login
GROUP BY
userid,
Name,
CAST(loginDate AS date)
)
--with-clause ends
select
pre.*,
total.totalduration
from
pre
join
total
on
pre.userid = total.userid
and total.LoginData = pre.loginData
詳細解釋請參考https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
簡而言之，with-clauses用于組織和簡化復雜的sql陳述句，例如：使用select-statements擺脫子查詢；so with-clauses '使 sql 陳述句更易于閱讀'
因此with-clause提供一個或多個臨時命名結果集（一個或多個），也被稱為公共表運算式（CTE），是在單個的執行范圍內定義的select，insert，update，delete或merge陳述句
在我發布的宣告中，我結合了您的查詢（預）和@Charlieface 的查詢（總計）
@Charlieface s query (total) sums up the loginduration for each userid, name and logindate。
如果我猜對了，您想實作一個查詢，該查詢回傳由每個日期和每個用戶（ID 和名稱）的總登錄持續時間擴展的登錄表中的所有行
這就是上面的陳述句所做的。
如果我弄錯了，并且您想實作一個查詢，該查詢回傳由每個用戶（ID 和名稱）的總登錄持續時間總日期擴展的登錄表中的所有行，我按如下方式自定義陳述句，我的自定義 - 與上面的陳述句 - 是陳述句中的注釋行：
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d2240f4ce7364ceccbcc8eec88dfada6
with pre as (
select
USERID,
Name,
--CAST(loginDate AS date) AS LoginData,
convert(varchar,loginDate,106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20),loginDate, 100), 7)) as LoginDateAndTime,
convert(varchar,logoutDate,106) ' ' LTRIM(RIGHT(CONVERT(VARCHAR(20),logoutDate, 100), 7)) as LogOutDateAndTime,
convert(varchar(5),DateDiff(s, loginDate, logoutDate)/3600) ':' convert(varchar(5),DateDiff(s, loginDate, logoutDate)%3600/60) ':' convert(varchar(5),(DateDiff(s, loginDate, logoutDate)%60)) as 'Time Duration Of Login [hh:mm:ss]'
from
login
),
total as (
SELECT
userid,
Name,
--CAST(loginDate AS date) AS LoginData,
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) / 3600) ':'
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 3600 / 60) ':'
CONVERT(varchar(5), SUM(DATEDIFF(s, loginDate, logoutDate)) % 60) AS TotalDuration
FROM
login
GROUP BY
userid,
Name
--,
--CAST(loginDate AS date)
)
select
pre.*,
total.totalduration
from
pre
join
total
on
pre.userid = total.userid
--and total.LoginData = pre.loginData
uj5u.com熱心網友回復：
看起來您只需要按用戶 ID 和日期分組即可。
不清楚你為什么在
LEFT JOIN這里有一個，為什么不是一個INNER
SELECT
times.userid,
times.Name,
times.LoginDate,
CONVERT(varchar(5), TotalDurationS / 3600) ':'
CONVERT(varchar(5), TotalDurationS % 3600 / 60) ':'
CONVERT(varchar(5), TotalDurationS % 60) AS TotalDuration
FROM
(
SELECT
u.userid,
u.Name,
CAST(la.loginDate AS date) AS LoginDate,
SUM(DATEDIFF(s, loginDate, logoutDate)) AS TotalDurationS
FROM login la
JOIN users u ON u.userid = la.id
GROUP BY
u.userid,
u.Name,
CAST(la.loginDate AS date)
) times;
- 我們首先按
userid和日期分組（我們通過強制轉換得到as date） - 然后，我們得到
SUM的的DATEDIFF計算 - 然后我們將其格式化為時間
uj5u.com熱心網友回復：
也許答案是@Charlieface 的最初評論，在添加了從秒到 H:M:S 之類的dateAdd(ss, yourTotalSeconds, 0)轉換之后，最后轉換為time(0)
樣本資料：
declare @tb table (
Sno int not null identity
, userid int not null
, name varchar(30)
, loginDate datetime
, logoutDate datetime)
insert @tb values
(222, 'abc', '20211217 13:40:10', '20211217 13:40:19'),
(222, 'abc', '20211217 14:05:20', '20211217 15:07:24'),
(444, 'xyz', '20211218 15:37:30', '20211218 15:37:36'),
(444, 'xyz', '20211216 13:51:40', '20211218 14:22:04'),
(444, 'xyz', '20211216 22:17:30', '20211218 22:18:50')
解決方案：
;with CTE as (
select userid
, cast(loginDate as date) DateLogged
, cast(dateAdd(ss, sum(dateDiff(ss, loginDate, logoutDate)), 0) as time(0)) TimeLogged
from @tb
group by userid, cast(loginDate as date))
select t.*
, cast(logoutDate - loginDate as time(0)) TimeDuration
, TimeLogged Total
from @tb t
join CTE c on (t.userid = c.userid and cast(t.loginDate as date) = c.DateLogged)
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/387991.html
標籤：sql-server 加入 通过...分组 和