SQL Server：临时收集聚合中的值并在同一查询中重用

im9ewurl  于 2022-12-17  发布在  SQL Server
关注(0)|答案(3)|浏览(116)

如何在T-SQL中累加值？AFAIK没有ARRAY类型。
我想在同一个查询中重用这些值，就像这个PostgreSQL示例中使用array_agg()所演示的那样。

SELECT a[1] || a[i] AS foo
     , a[2] || a[5] AS bar  -- assuming we have >= 5 rows for simplicity
FROM  (
    SELECT array_agg(text_col ORDER BY text_col) AS a
        , count(*)::int4 AS i
    FROM   tbl
    WHERE  id BETWEEN 10 AND 100
    ) sub;

使用T-SQL如何最好地解决这个问题？
我能想到的最好的是两个CTE和子选择：

;WITH x AS (
   SELECT row_number() OVER (ORDER BY name) AS rn
        , name AS a
   FROM   #t
   WHERE  id BETWEEN 10 AND 100
   )
, i AS (
   SELECT count(*) AS i
   FROM   x
   )
SELECT (SELECT a FROM x WHERE rn = 1) + (SELECT a FROM x WHERE rn = i) AS foo
     , (SELECT a FROM x WHERE rn = 2) + (SELECT a FROM x WHERE rn = 5) AS bar
FROM   i;

测试装置：

CREATE TABLE #t(
  id   INT PRIMARY KEY
, name NVARCHAR(100))
;

INSERT INTO #t VALUES
  ( 3, 'John')
, ( 5, 'Mary')
, ( 8, 'Michael')
, (13, 'Steve')
, (21, 'Jack')
, (34, 'Pete')
, (57, 'Ami')
, (88, 'Bob')
;

有更简单的方法吗？

sql-server

3条答案

按热度按时间
gwbalxhn

gwbalxhn1#

编辑1：我添加了另一个解决方案，展示了如何在SQL Server上模拟ARRAY_AGG（最后一个答案）。
编辑2：对于解决方案4），我添加了第三种连接方法。

我不确定我是否正确理解了你的问题。
a）在SQL Server中，我不使用数组，而是使用表变量或XML。
B）要连接字符串（在本例中），我将使用SELECT @var = @var + Name FROM tbl语句或XML xqueries。
c）基于CTE和多个子查询（WITH cte AS () FROM SELECT (SELECT * FROM cte.rn=1) + ()...）的解决方案将生成大量扫描和逻辑读取。
解决方案：1）表格变量+ SELECT @var = @var + Name FROM tbl：

--Creating the "array"
DECLARE @Array TABLE
(
    Idx     INT PRIMARY KEY,
    Val     NVARCHAR(100) NOT NULL
);

WITH Base
AS
(
    SELECT  Val = t.name, 
            Idx = ROW_NUMBER() OVER(ORDER BY t.name ASC)
    FROM    #t t
    WHERE  t.id between 10 AND 100
)
INSERT  @Array (Idx, Val)
SELECT  b.Idx, b.Val
FROM    Base b;

--Concatenating all names
DECLARE @AllNames NVARCHAR(4000);
--”Reset”/Init @AllNames
SET     @AllNames = '';
--String concatenation
SELECT  @AllNames = @AllNames + ',' + a.Val
FROM    @Array a;
--Remove first char (',')
SELECT  @AllNames = STUFF(@AllNames, 1, 1, '');
--The final result
SELECT  @AllNames [Concatenating all names - using a table variable];
/*
Concatenating all names - using a table variable
------------------------------------------------
Ami,Bob,Jack,Pete,Steve
*/

--Concatenating Idx=2 and Idx=5
--”Reset” @AllNames value
SET     @AllNames = '';
--String concatenation
SELECT  @AllNames = @AllNames + ',' + a.Val
FROM    @Array a
WHERE   a.Idx IN (2,5) --or a.Idx IN (2, (SELECT COUNT(*) FROM @Array))
ORDER BY a.Idx ASC;
--Remove first char (',')
SELECT  @AllNames = STUFF(@AllNames, 1, 1, '');
--The final result
SELECT  @AllNames [Concatenating Idx=2 and Idx=5 - using a table variable];
/*
Concatenating Idx=2 and Idx=5 - using a table variable
------------------------------------------------------
Bob,Steve
*/

2)表格变量+ PIVOT：

--Concatenating a finite number of elements (names)
SELECT   pvt.[1] + ',' + pvt.[0]    AS [PIVOT Concat_1_and_i(0)]
        ,pvt.[2] + ',' + pvt.[5]    AS [PIVOT Concat_2_and_5]
        ,pvt.*
FROM    
(
        SELECT  a.Idx, a.Val
        FROM    @Array a
        WHERE   a.Idx IN (1,2,5)
        UNION ALL   
        SELECT  0, a.Val --The last element has Idx=0
        FROM    @Array a
        WHERE   a.Idx = (SELECT COUNT(*) FROM @Array)
) src
PIVOT   (MAX(src.Val) FOR src.Idx IN ([1], [2], [5], [0])) pvt;
/*
PIVOT Concat_1_and_i(0) PIVOT Concat_2_and_5
----------------------- --------------------
Ami,Steve               Bob,Steve           
*/

3)XML + XQuery：

SET ANSI_WARNINGS ON;
GO

DECLARE @x XML;
;WITH Base
AS
(
    SELECT  Val = t.name, 
            Idx = ROW_NUMBER() OVER(ORDER BY t.name ASC)
    FROM    #t t
    WHERE   t.id BETWEEN 10 AND 100
)
SELECT  @x = 
(
    SELECT   b.Idx  AS [@Idx]
            ,b.Val  AS [text()]
    FROM    Base b
    FOR XML PATH('Element'), ROOT('Array')
);
/* @x content
<Array>
  <Element Idx="1">Ami</Element>
  <Element Idx="2">Bob</Element>
  <Element Idx="3">Jack</Element>
  <Element Idx="4">Pete</Element>
  <Element Idx="5">Steve</Element>
</Array>
*/

--Concatenating all names (the result is XML, so a cast is needed)
DECLARE @r XML; --XML result
SELECT  @r=@x.query('
(: $e = array element :)
for $e in (//Array/Element)
    return string($e)
');
SELECT  REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating all names - using XML];
/*
Concatenating all names - using XML
-----------------------------------
Ami,Bob,Jack,Pete,Steve
*/

--Concatenating Idx=1 and all names
SELECT  @r=@x.query('
(: $e = array element :)
for $e in (//Array/Element[@Idx=1], //Array/Element)
    return string($e)
');
SELECT  REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=1 and all names - using XML];
/*
Concatenating Idx=1 and all names - using XML
---------------------------------------------
Ami,Ami,Bob,Jack,Pete,Steve
*/

--Concatenating Idx=1 and i(last name)
DECLARE @i INT;
SELECT  @r=@x.query('
(: $e = array element :)
for $e in (//Array/Element[@Idx=1], //Array/Element[@Idx=count(//Array/Element)])
    return string($e)
');
SELECT  REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=1 and i(last name) - using XML];
/*
Concatenating Idx=1 and i(last name) - using XML
------------------------------------------------
Ami,Steve
*/

--Concatenating Idx=2 and Idx=5
SELECT  @r=@x.query('
(: $e = array element :)
for $e in (//Array/Element[@Idx=2], //Array/Element[@Idx=5])
    return string($e)
');
SELECT  REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=2 and Idx=5 - using XML (method 1)];
/*
Concatenating Idx=2 and Idx=5 - using XML (method 1)
----------------------------------------------------
Bob,Steve
*/

--Concatenating Idx=2 and Idx=5
SELECT  @x.value('(//Array/Element)[@Idx=2][1]', 'NVARCHAR(100)')
        + ','
        + @x.value('(//Array/Element)[@Idx=5][1]', 'NVARCHAR(100)') AS [Concatenating Idx=2 and Idx=5 - using XML (method 2)];;
/*
Concatenating Idx=2 and Idx=5 - using XML (method 2)
----------------------------------------------------
Bob,Steve
*/

**4）如果问题是如何在SQL Server上模拟ARRAY_AGG，则一个答案可能是：使用XML。**示例：

SET ANSI_WARNINGS ON;
GO

DECLARE @Test TABLE
(
     Id         INT PRIMARY KEY
    ,GroupID    INT NOT NULL
    ,Name       NVARCHAR(100) NOT NULL
);

INSERT INTO @Test (Id, GroupID, Name)
VALUES
 (3 , 1, 'John')
,(5 , 1, 'Mary')
,(8 , 1, 'Michael')
,(13, 1, 'Steve')
,(21, 1, 'Jack')
,(34, 2, 'Pete')
,(57, 2, 'Ami')
,(88, 2, 'Bob');

WITH BaseQuery
AS
(
        SELECT  a.GroupID, a.Name
        FROM    @Test a
        WHERE   a.Id BETWEEN 10 AND 100 
)
SELECT  x.*
        , CONVERT(XML,x.SQLServer_Array_Agg).query
        ('
        for $e in (//Array/Element[@Idx=1], //Array/Element[@Idx=count(//Array/Element)])
            return string($e)
        ') AS [Concat Idx=1 and Idx=i (method 1)]
        , CONVERT(XML,x.SQLServer_Array_Agg).query('
            let $a :=  string((//Array/Element[@Idx=1])[1])
            let $b :=  string((//Array/Element[@Idx=count(//Array/Element)])[1])
            let $c :=  concat($a , "," , $b) (: " is used as a string delimiter :)
            return $c
        ') AS [Concat Idx=1 and Idx=i (method 2)]
        , CONVERT(XML,x.SQLServer_Array_Agg).query
        ('
        for $e in (//Array/Element[@Idx=(1,count(//Array/Element))])
            return string($e)
        ') AS [Concat Idx=1 and Idx=i (method 3)]
FROM
(
    SELECT  a.GroupID
        ,(SELECT ROW_NUMBER() OVER(ORDER BY b.Name) AS [@Idx]
                ,b.Name AS [text()]
        FROM    BaseQuery b
        WHERE   a.GroupID = b.GroupID 
        ORDER BY b.Name
        FOR XML PATH('Element'), ROOT('Array') ) AS SQLServer_Array_Agg
    FROM    BaseQuery a
    GROUP BY a.GroupID
) x;

结果：

GroupID SQLServer_Array_Agg                                                                                        Concat Idx=1 and Idx=i (method 1) Concat Idx=1 and Idx=i (method 2) Concat Idx=1 and Idx=i (method 3)
------- ---------------------------------------------------------------------------------------------------------- --------------------------------- --------------------------------- ---------------------------------
1       <Array><Element Idx="1">Jack</Element><Element Idx="2">Steve</Element></Array>                             Jack Steve                        Jack,Steve                        Jack Steve
2       <Array><Element Idx="1">Ami</Element><Element Idx="2">Bob</Element><Element Idx="3">Pete</Element></Array> Ami Pete                          Ami,Pete                          Ami Pete
赞(0）分享  回复(0）举报  2022-12-17
j2datikz

j2datikz2#

如果只是收集一些值以供重用，请尝试使用表变量而不是临时表

DECLARE @t TABLE 
(
    id INT PRIMARY KEY,
    name NVARCHAR(100)
)

INSERT @t VALUES (3 , 'John')
-- etc

表变量只在内存中，而不像临时表那样在tempdb数据库中。
有关详细信息，请查看Should I use a #temp table or table variable。

赞(0）分享  回复(0）举报  2022-12-17
2jcobegt

2jcobegt3#

不知道这是否有帮助，但你总是可以...

select * into #MyTempTable from SomeTable
赞(0）分享  回复(0）举报  2022-12-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com