TSQL GROUP BY 字符串的一部分

Question

我有一个简单的查询。像这样的东西:

SELECT l.list_name, COUNT(order_id) 
FROM orders o JOIN lists l ON l.order_id=o.order_id 
    WHERE l.list_name LIKE 'orders_1%' or l.list_name LIKE 'orders_2%'
GROUP BY l.list_name

情况看起来是这样的:一个存储过程正在更新列表表，但如果有超过 1000 个订单，它会将列表分成几部分。

如果我有 1200 个带有标准或列表“orders_1”的订单，那么我的程序将创建两个列表:“orders_1_1”和“orders_1_2”，第一个有 1,000 个订单，第二个有 200 个订单。

因此，当我运行查询来计算这些订单时，我会得到如下结果:

list_name                  count

orders_1                   100
orders_1_more_than_100_1   1000       
orders_1_more_than_100_2   200
orders_2                   400 
orders_3_1                 1000       
orders_3_2                 1000
orders_3_3                 420  
orders_3_more_than_100_1   1000       
orders_3_more_than_100_2   900
orders_3_more_than_200_1   1000       
orders_3_more_than_200_2   1000
orders_3_more_than_200_3   100      
orders_4                   200
orders_4_more_than_300     200 

我想得到的结果应该是这样的:

list_name                  count

orders_1                   100
orders_1_more_than_100     1200       
orders_2                   400 
orders_3                   2420 
orders_3_more_than_100     1900 
orders_3_more_than_200     2100     
orders_4                   200 
orders_4_more_than_300     200 

这样它就会对以相同开头的所有列表求和。

对此有何想法？ :)

这些是我在 list_names 列中的确切值:

WYS_AUT_PISMO_NR_6
WYS_AUT_PISMO_NR_5_POWYZEJ_240
WYS_AUT_PISMO_NR_5_DO_240
WYS_AUT_PISMO_NR_4_POWYZEJ_240_5
WYS_AUT_PISMO_NR_4_POWYZEJ_240_4
WYS_AUT_PISMO_NR_4_POWYZEJ_240_3
WYS_AUT_PISMO_NR_4_POWYZEJ_240_2
WYS_AUT_PISMO_NR_4_POWYZEJ_240_1
WYS_AUT_PISMO_NR_4_DO_240
WYS_AUT_PISMO_NR_3_POWYZEJ_240
WYS_AUT_PISMO_NR_3_DO_240
WYS_AUT_PISMO_NR_2_POWYZEJ_240
WYS_AUT_PISMO_NR_2_DO_240
WYS_AUT_PISMO_NR_1

我想要的是像这样将它们分组:

WYS_AUT_PISMO_NR_6
WYS_AUT_PISMO_NR_5_POWYZEJ_240
WYS_AUT_PISMO_NR_5_DO_240
WYS_AUT_PISMO_NR_4_POWYZEJ_240 /*here I must group those 5 lists*/
WYS_AUT_PISMO_NR_4_DO_240
WYS_AUT_PISMO_NR_3_POWYZEJ_240
WYS_AUT_PISMO_NR_3_DO_240
WYS_AUT_PISMO_NR_2_POWYZEJ_240
WYS_AUT_PISMO_NR_2_DO_240
WYS_AUT_PISMO_NR_1

Answer 1

这个怪异的表达式将隔离从参数开头到最后一个的字符串 _如果有多个下划线:

select case when len (l.list_name) - len (replace (l.list_name, '_', '')) > 1 
            then left(l.list_name, 
                      len (l.list_name) - charindex('_', reverse(l.list_name)))
            else l.list_name
        end

或者，您可以从字符串中删除“orders_”，用点替换下划线并将其转换为浮点数，然后转换为 int 以删除小数，然后使用这个怪物返回到字符串:

select 'orders_' + cast (cast (cast (
        replace (substring (@str, 8, 100), '_', '.') 
        as float) as int) as varchar(100))

为避免重复此 blob，请使用派生表而不是 lists :

SELECT l.TrimmedListName, COUNT(order_id) 
FROM orders o 
JOIN 
(
   select lists.*,
       -- Remove optional list continuation number
          case when len (list_name) - len (replace (list_name, '_', '')) > 1 
                then left(list_name, 
                          len (list_name) - charindex('_', reverse(list_name)))
                else list_name
            end AS TrimmedListName
     from lists
) l ON l.order_id=o.order_id 
WHERE (l.list_name LIKE 'orders_1%' or l.list_name LIKE 'orders_2%')
GROUP BY l.TrimmedListName