選擇查詢 - 分組依據和子分組依據

CREATE TABLE [dbo].[temptable]
(
   [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
   [uid] INT NOT NULL, 
   [pid] INT NOT NULL, 
   [event] INT NOT NULL, 
   [timeStamp] DateTime NOT NULL 
)

我想選擇 uid 計數（按 pid 分組），其中 event = @event AND timeStamp =

$$ MAX of timestamp $$（按 uid 分組）。 注意pid..uid是1..many關係

Id,uid,pid,event,timeStamp
1,20,300,100,'yesterday'
2,20,300,200,'today'
3,20,300,400,'daybefore'
4,30,300,100,'yesterday'
5,30,300,400,'daybefore'
6,40,300,100,'today'
7,40,300,200,'yesterday'
8,40,300,400,'daybefore'

對於上述數據，如果@event = 200，我應該得到以下結果

pid, count
300, 1

它不應該計算 uid 30，因為 uid 30 沒有事件 200。它也不應該計算 uid 40，因為eventmax(timestamp) ietoday是 100，它與 @event ie 200 不匹配。

TimeStamp是DateTime欄位，但為簡單起見，我在範例數據中保持相對。

如果我計算正確，那麼您所追求的是：

; WITH cte AS
 ( SELECT pid, uid, event, timeStamp,
          maxts = MAX(timeStamp) OVER (PARTITION BY pid, uid)
   FROM tableX
 )
SELECT pid, cnt = COUNT(*)
FROM cte
WHERE maxts = timeStamp
 AND event = 200 
GROUP BY pid ;

如果您有 2 行具有相同的 pid、uid 和時間戳，則需要替換COUNT(*)為COUNT(DISTINCT uid).

引用自：https://dba.stackexchange.com/questions/64831