sql - 如何在 SQL Server 中围绕动态数量的组对值进行分组

Question

我已经阅读了许多/大多数关于范围分组的帖子，但似乎没有一个帖子涵盖了我的困境，即根据动态值范围对日期进行分组，所以我希望有人可以帮助或至少指出我方向正确。

首先，我有一组范围(但这些范围可能会发生变化，并且在该组中看到 2 到 9 行是正常的，当然数字也会发生变化):

CREATE TABLE [Ranges] ([label] [varchar](160), [score] [int])

INSERT INTO [Ranges] ([label],[score]) VALUES('Red',    33)
INSERT INTO [Ranges] ([label],[score]) VALUES('Amber',  66)
INSERT INTO [Ranges] ([label],[score]) VALUES('Green', 100)

然后是实际数据本身，存储在此处:

CREATE TABLE [TableData] ([Name] varchar(160), [Value] int)

具有以下值:

INSERT INTO [TableData] ([Name],[value]) VALUES('Cat', 100)
INSERT INTO [TableData] ([Name],[value]) VALUES('Dog', 37)
INSERT INTO [TableData] ([Name],[value]) VALUES('Bat', 16)
INSERT INTO [TableData] ([Name],[value]) VALUES('Mole', 87)
INSERT INTO [TableData] ([Name],[value]) VALUES('Hen', 55)
INSERT INTO [TableData] ([Name],[value]) VALUES('Dove', 28)
INSERT INTO [TableData] ([Name],[value]) VALUES('Rat', 6)

这个想法是将 [Ranges] 数据排序到一个连续的范围中，例如:

if value <= 33 then Red
if value > 33 and value <= 66 then Amber
if value > 66  and value <= 100 then Green

并将其插入到将返回 [TableData] 行数的分组查询中，在本例中:

Red    Amber    Green
 3       2        2

但正如我所说，假设可能的话，我不知道如何动态地进行它。

提前致谢。

---

这就是我用来获取每个级别的最大值和最小值的方法。我还没有在超过 3 个条目上对其进行测试。如前所述，它最多需要在 9 个上运行。

SELECT TOP 100 PERCENT cx.label AS label,
    -- when this one is the lowest one, go to the lowest in the range
    CASE 
        WHEN (SELECT TOP 1 cy.label 
                FROM Ranges cy 
                ORDER BY cy.score ASC) = cx.label 
            THEN 0

        WHEN cx.score > 0 AND cx.score < 100 
            THEN (SELECT TOP 1 cy.score + 1 
                    FROM Ranges cy
                    AND cy.score < cx.score 
                    ORDER BY cy.score ASC)

        ELSE (SELECT TOP 1 cy.score + 1
                FROM Ranges cy
                AND cy.score <> cx.score 
                ORDER BY cy.score DESC) END AS scoreMin,

    cx.score AS scoreMax,
    cx.color
FROM Ranges cx
GROUP BY cx.label,
         cast(cx.score AS int),
         cx.color
ORDER BY cast(cx.score AS int) ASC

使用它，我将它插入到带有枢轴的选定选项中，它适用于 3 个选项，但我仍在寻找一种动态填充该列列表的方法。

Answer 1

我建议在 Ranges 表中设置下限和上限。

示例数据

DECLARE @Ranges TABLE ([label] [varchar](160), [scoreMin] [int], [scoreMax] [int]);

INSERT INTO @Ranges ([label],[scoreMin],[scoreMax]) VALUES('Red',  0,  33)
INSERT INTO @Ranges ([label],[scoreMin],[scoreMax]) VALUES('Amber',34, 66)
INSERT INTO @Ranges ([label],[scoreMin],[scoreMax]) VALUES('Green',67, 100)


DECLARE @TableData TABLE ([Name] varchar(160), [Value] int)

INSERT INTO @TableData ([Name],[value]) VALUES('Cat', 100)
INSERT INTO @TableData ([Name],[value]) VALUES('Dog', 37)
INSERT INTO @TableData ([Name],[value]) VALUES('Bat', 16)
INSERT INTO @TableData ([Name],[value]) VALUES('Mole', 87)
INSERT INTO @TableData ([Name],[value]) VALUES('Hen', 55)
INSERT INTO @TableData ([Name],[value]) VALUES('Dove', 28)
INSERT INTO @TableData ([Name],[value]) VALUES('Rat', 6)

查询

SELECT
    R.label
    ,COUNT(*) AS CountRows
FROM
    @TableData AS T
    INNER JOIN @Ranges AS R
        ON  T.Value >= R.scoreMin
        AND T.Value <= R.scoreMax
GROUP BY R.label
ORDER BY R.label;

结果

+-------+-----------+
| label | CountRows |
+-------+-----------+
| Amber |         2 |
| Green |         2 |
| Red   |         3 |
+-------+-----------+

如果您希望将结果集作为具有动态列数的一行，则搜索“动态数据透视表”。传统上，这种转换是在客户端的报告子系统中完成的。