自定义组通过和地方的一些记录在一个组(Custom Group By and place some

请考虑数据：

 Id          Group            Value
 -----------------------------------
 1            1                10
 2            1                12
 3            1                10
 4            2                90
 5            2                10
 6            3                30
 7            4                12
 8            4                11
 9            5                10
 10           5                11

我想做Group By对这些数据作为1,2,3发生在一组， 4,5发生在另一组。 如何我可以做到这一点SQL Server ？

谢谢

---

编辑1）

我想这样的结果：

Groups                              Count
-----------------------------------------
Group for 1,2,3                       6
Group for 4,5                         4

Answer 1:

我使用outer apply ，所以你不会复制你的代码组

select
    C.Group_Name, count(*)
from Table1
    outer apply
    (
        select
            case
                when C.[Group] in (1, 2, 3) then 'Group for 1, 2, 3'
                when C.[Group] in (4, 5) then 'Group for 4, 5'
            end as Group_Name
    ) as C
group by C.Group_Name

你也可以使用子查询

select
    C.Group_Name, count(*)
from 
(
    select
        case
            when T.[Group] in (1, 2, 3) then 'Group for 1, 2, 3'
            when T.[Group] in (4, 5) then 'Group for 4, 5'
        end as Group_Name,
        T.Value,
        T.Id
    from Table1 as T
) as C
group by C.Group_Name

Answer 2:

这也可以做你想做的：

SELECT 'Group for 1,2,3' AS GROUPS
   ,   COUNT(Id) AS Count
FROM Foo
WHERE [Group] IN (1,2,3)

UNION ALL

SELECT 'Group for 4,5' AS GROUPS
   ,   COUNT(Id) AS Count
FROM Foo
WHERE [Group] IN (4,5)

http://sqlfiddle.com/#!6/cdb82/2/0

当然，这只有当你知道你想要哪个组。

文章来源: Custom Group By and place some records in one group