代码之家 › 专栏 › 技术社区 › Nathan DeWitt

如何在T-SQL中以分层格式高效地分组数据?

tsql sql-server-2005 sql-server sql

Nathan DeWitt · 技术社区 · 15 年前

我有这样的数据:

Task   | Hours
1.1    |    40
2      |    40
2.1    |    60
2.1.1  |    15
15.9   |    24
16     |     5
19.1   |    40
19.1.1 |     8
19.1.2 |    12
19.2   |     6
19.2.1 |    21
19.2.2 |    15
19.2.3 |     2
19.3   |    64

我想根据任务的前两个级别进行分组,得出以下结果:

Task   | Hours
1.1    |    40
2      |    40
2.1    |    75
15.9   |    24
16     |     5
19.1   |    60
19.2   |    44
19.3   |    64

我不想让16层卷起它下面的部分,但我需要所有其他层卷起。这是SQL Server 2005。通常情况下,我会对小数进行拆分,然后按这种方式进行拆分,但我想知道是否有更好的方法可以在SQL中进行拆分。

4 回复 | 直到 15 年前

Phil Sandler 15 年前

改变模型是一种选择吗?如果您的任务列真的打算表示层次结构,那么您应该在关系模型中正确地表示层次结构。

如果深度级别的数目固定为三个,另一个选项可能是添加三列来独立地表示任务列的每个“部分”。

如果这不是一个选项,我认为您可以通过一系列分析字符串的case语句(加上sum和group by)来实现这一点。

更新:

好吧,这似乎是一个有趣的挑战,所以我想到了:

SELECT
    main_task,
    SUM(hours)
FROM
    (
    SELECT      
        task,
        CASE 
            WHEN 
                LEN(task) + 1 - CHARINDEX('.', REVERSE(task)) = CHARINDEX ('.', task) THEN task
                ELSE LEFT(task, LEN(task) + 1 - CHARINDEX('.', REVERSE(task)) - 1)
            END main_task,
        hours
    FROM 
        #temp
    ) sub
GROUP BY 
      main_task

ChrisLively 15 年前

另一种方法是添加一些计算列,将不同的任务级别分开,然后根据需要分组和求和。

Sparky 15 年前

假设字段任务的结构是一致的,则可以使用以下内容

select left(task,4) as Task,sum(hours) as Hours
from table
group by left(task,4)

这是稍微修改过的版本

select LEFT(task,charindex('.',task+'.')+1),SUM(hours)
from test1
group by LEFT(task,charindex('.',task+'.')+1)

Nathan DeWitt 15 年前

我在回家的路上考虑过这个问题,我想提出这个解决方案:

创建一个存储层次结构的表,然后执行一个join,获取任务的父级。

任务结构表:

task  | task_group
1     | 1
1.1   | 1.1
1.1.1 | 1.1
1.1.2 | 1.1
1.1.3 | 1.1
1.2   | 1.2
1.2.1 | 1.2

然后我可以这样做:

SELECT SUM(d.Hours) AS "Hours", t.task_group
FROM Data d
JOIN TaskStructureTable t ON d.Task = t.task

认为这比做的快 CHARINDEX ?(是的,我可以肯定地测量和知道)