選擇結果出現多次的行

我一直在查看 StackExchange，測試此處列出的各種方法，但沒有任何成功。

我只需要選擇具有多個會話的使用者。這是我開始的 SELECT 語句：

SELECT  spid SPID,
       loginame [Login]
FROM master.dbo.sysprocesses sp 
WHERE (hostname LIKE '%P21%' OR hostname LIKE '%TS%')
   AND PROGRAM_NAME LIKE '%SQLCA%'
ORDER BY Login 

例如，以下內容不起作用：

SELECT  spid SPID,
       count(loginame) as Login
FROM master.dbo.sysprocesses sp 
WHERE (hostname LIKE '%P21%' OR hostname LIKE '%TS%')
   AND PROGRAM_NAME LIKE '%SQLCA%' 
HAVING count(loginame) > 1;

這會導致錯誤：

列 ‘master.dbo.sysprocesses.spid’ 在選擇列表中無效，因為它不包含在聚合函式或 GROUP BY 子句中。

我該怎麼做才能縮小結果範圍，使其僅在登錄名 > 1 時輸出？

編輯：如果我解釋最終目標可能會有所幫助——我們的 ERP 系統允許使用者多次登錄。但是，每次登錄都會佔用我們許可證上的一個席位。我正在尋找創建一個定期執行的 SQL 作業，並為具有多個 spid 的使用者自動殺死最舊的 spid。

第一步是簡單地從具有多個 spid 的使用者那裡獲取一個 spid 列表，這些 spid 符合我的 WHERE 中的標準。之後，我必須將其微調到每個使用者最舊的 spid，然後最終對這些 spid 執行 KILL。我不是要求任何人創建整個腳本。僅獲取具有多個會話的登錄的 spid 列表的任何幫助都將非常棒。

如果您希望每次登錄只有一行，那麼 SPID 在輸出中沒有任何意義，除非您明確定義了您想要的內容（最低值？最高？與最早或最新登錄相關聯的值？逗號分隔的列表他們都是？）。也sysprocesses已被棄用 16 年和 6 個以上的主要版本（更多資訊在這裡）；使用較新的sys.dm_exec_*DMV。

SELECT login_name, session_count = COUNT(*)
 FROM sys.dm_exec_sessions
 WHERE program_name LIKE N'%SQLCA%'
   AND (host_name LIKE N'%P21%' OR host_name LIKE N'%TS%')
 GROUP BY login_name 
 HAVING COUNT(*) > 1;

如果您想要所有單獨的行並包含每個行session_id，則可以使用帶有視窗函式的 CTE 來確定每次登錄的會話數，然後在 CTE 之外進行過濾：

;WITH cte AS 
(
 SELECT session_id, login_name, 
   session_count = COUNT(*) OVER (PARTITION BY login_name)
 FROM sys.dm_exec_sessions
 WHERE program_name LIKE N'%SQLCA%'
   AND (host_name LIKE N'%P21%' OR host_name LIKE N'%TS%')
)
SELECT SPID = session_id,
 login_name, 
 session_count
FROM cte 
WHERE session_count > 1
ORDER BY login_name, session_id;

為了根據座位數殺死會話，我認為殺死除最新會話之外的所有會話更有意義，您可以使用不同的視窗函式以類似的方式確定：

;WITH cte AS 
(
 SELECT session_id, login_name, 
   session_count = COUNT(*) OVER (PARTITION BY login_name),
   rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time DESC)
 FROM sys.dm_exec_sessions
) 
SELECT [Command] = CASE 
 WHEN rn > 1 THEN 
   'KILL ' + CONVERT(varchar(5), session_id) + N';'
 ELSE 
   '-- Leave this one alone' 
 END, *
FROM cte
WHERE session_count > 1 
ORDER BY login_name, rn DESC;

如果您出於某種原因想殺死除最舊的以外的所有內容，請更改：

rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time DESC)

到：

rn = ROW_NUMBER() OVER (PARTITION BY login_name ORDER BY login_time)

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