在同一請求中使用STRING_AGG和LAST

如何從 id 的 order 中找到的最后一條記錄中獲取 777777

SELECT 
    STRING_AGG(name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG([numerToCall],'-') as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    '777777' as lastCalled
FROM 
    SimpleTest1

創建表和資料

 CREATE TABLE [dbo].[simpleTest1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [numerToCall] [varchar](50) NOT NULL,
 CONSTRAINT [PK_simpleTest1] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[simpleTest1] ON 
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (1, N'benny', N'555555')
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (2, N'helle', N'999999')
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (3, N'hans', N'777777')
SET IDENTITY_INSERT [dbo].[simpleTest1] OFF

解決方案是

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.numerToCall, '-') as calledNumbers,
    MIN(t.xxxx) as lastCalled
FROM (
    SELECT *,
      LAST_VALUE(t.numerToCall) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as xxxx
    from
    SimpleTest1 t
) t;

謝謝@Charlieface

uj5u.com熱心網友回復：

您可以將視窗函式放在派生表中，然后在其上使用聚合函式（MIN或MAX）

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.numerToCall, '-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    MIN(t.numerToCall]) as lastCalled
FROM (
    SELECT *,
      LAST_VALUE(t.numerToCall) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as lastCalled
    SimpleTest1 t
) t;

uj5u.com熱心網友回復：

您可以使用子查詢

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    (SELECT TOP 1 InTbl.numerToCall FROM SimpleTest1 InTbl ORDER BY id desc) as lastCalled
FROM 
    SimpleTest1 t
GO

或者您可以決議聚合的結果并從字串中獲取結果，而無需另一個查詢（無子查詢）

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    RIGHT(STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id), CHARINDEX('-',REVERSE(STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id)))-1) as lastCalled
FROM 
    SimpleTest1 t
GO

檢查您的特定資料庫中的這兩種解決方案，并選擇一個為您提供更好性能的解決方案（SSMS 執行計劃分析顯示第二個查詢更好，但不要指望它并檢查服務器中的 IO 和 TIME）

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/460708.html

標籤：sql服务器 tsql 聚合函数窗函数 sql-server-2019

上一篇：SQLSum對具有臨時創建的列的條目的多個列進行求和

下一篇：查詢會話不再回應

在同一請求中使用STRING_AGG和LAST_VALUE函式