gpt4 book ai didi

sql - 是否可以按行组合进行分组?

转载 作者:行者123 更新时间:2023-12-04 23:45:38 25 4
gpt4 key购买 nike

我有一个结果,它为我提供了从我的数据库中查询的一系列值:

Start        End
----- ---
1 3
5 6
137 139

根据这些,我需要在数据库中查询该范围内的记录,这可能会返回如下内容:

Id    Name
----- ------
1 foo
2 bar
3 baz

Id Name
----- ------
5 foo
6 baz

Id Name
----- ------
137 foo
138 bar
139 baz

我想对这些结果进行分组,保留任何 id 范围,因为它们与同一事物相关。例如,1-3 与 137-139 相同,因此它的计数为 2,但当然,“范围”可以是 2 中的任何一个:

RangeStart   RangeEnd   Count
---------- -------- -----
137 139 2
5 6 1

另请注意,顺序应更改分组,因此 foo/bar/baz 与 foo/baz/bar 不同。

如何实现?

编辑:我有开始结果(开始、结束),我只关心最终结果(RangeStart、RangeEnd、Count)。我实际上并不需要中间结果,我只是将它们用作解释。

最佳答案

这里有两个查询:

  • 第一个连接字符串根据范围和分组然后显示每个的第一个范围字符串组。它还具有字符串的总次数出现了。
  • 第二个显示串联的字符串及其各自的总数。

设置:

DECLARE @Tags TABLE ( 
TagID INT,
Tag VARCHAR(3)
)

INSERT @Tags
SELECT 1, 'Foo' UNION ALL
SELECT 2, 'Bar' UNION ALL
SELECT 3, 'Baz' UNION ALL
SELECT 4, 'Foo' UNION ALL
SELECT 5, 'Bar' UNION ALL
SELECT 6, 'Baz'

DECLARE @Ranges TABLE (
StartRange INT,
EndRange INT
)

INSERT @Ranges
SELECT 1,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4 UNION ALL
SELECT 4,6

显示第一个范围和结果的查询:

/* Get the first start and end ranges with a match and */
/* the total number of occurences of that match */
SELECT
StartRange,
EndRange,
Total
FROM (
SELECT
StartRange,
EndRange,
Csv,
ROW_NUMBER() OVER (PARTITION BY Csv ORDER BY StartRange ASC) AS RowNum,
ROW_NUMBER() OVER (PARTITION BY Csv ORDER BY StartRange DESC) AS Total
FROM (
/* For each range and its associated Tag values, */
/* Concatenate the tags together using FOR XML */
/* and the STUFF function */
SELECT
StartRange,
EndRange,
(
SELECT STUFF(
(SELECT ',' + Tag
FROM @Tags WHERE TagID BETWEEN r.StartRange AND r.EndRange
ORDER BY TagID
FOR XML PATH('')),1,1,'')
) AS Csv
FROM @Ranges r
) t1
) t2
WHERE RowNum = 1
ORDER BY StartRange, EndRange

/* Results */

StartRange EndRange Total
----------- ----------- -----
1 3 2
2 3 1
3 4 1

显示连接字符串和总数的查询:

/* Get the concatenated tags and their respective totals */ 
SELECT
Csv,
COUNT(*) AS Total
FROM (
/* For each range and its associated Tag values, */
/* Concatenate the tags together using FOR XML */
/* and the STUFF function */
SELECT
StartRange,
EndRange,
(
SELECT STUFF(
(SELECT ',' + Tag
FROM @Tags WHERE TagID BETWEEN r.StartRange AND r.EndRange
ORDER BY TagID
FOR XML PATH('')),1,1,'')
) AS Csv
FROM @Ranges r
) t1
GROUP BY Csv
ORDER BY Csv

/* Results */

Csv Total
------------ -----------
Bar,Baz 1
Baz,Foo 1
Foo,Bar,Baz 2

字符串连接方法由 Jeremiah Peschka 提供

关于sql - 是否可以按行组合进行分组?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3249636/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com