在一列中按多个值分组

Question

我的数据表看起来像这样：

客户端   状态
   1       a
   2       b
   3       a
   4       d
   5       d
   6       a
   7       e
   8       b
   9       e
  10       f

假设我想按状态对表进行分组，但是我想将状态 a,b,d（状态 I）作为一组，e,f（状态 II）作为另一组

最终我希望它看起来像这样

状态 计数
   I      7
  II      3

我应该如何编写查询？

- Kristy Yu

---

我的偏好是创建一个参考表，其中将a、b和d映射到状态I，将e和f映射到状态II。这个答案建议使用case语句。https://stackoverflow.com/questions/13269638/custom-group-by-and-place-some-records-in-one-group - undefined

Answer 1

你可以使用CASE语句。

SELECT
  CASE WHEN Status IN ('a', 'b', 'd')
    THEN 'I'
  WHEN Status IN ('e', 'f')
    THEN 'II'
  END AS "Status Group",
  count(*) as Count
FROM Table
GROUP BY 1
ORDER BY 1
;

- Ricky McMaster

Answer 2

你可以将分组和计数合并为一条语句。

SELECT
  CASE WHEN Status IN ('a', 'b', 'd') THEN 'I' ELSE 'II' END AS Status,
  count(*) as count
FROM Table1
GROUP BY  (CASE WHEN Status IN ('a', 'b', 'd') THEN 'I' ELSE 'II' END)
ORDER BY Status

Result:
Status  count
 I      7
II      3

- jose_bacoy

Answer 3

你可以使用带有case语句的group by。

declare @table table ( client INT, STATUS Varchar(10))
insert into @table
select 1  , 'a' union all
select 2 ,  'b' union all
select 3  , 'a' union all
select 4  , 'd' union all
select 5  , 'd' union all
select 6  , 'a' union all
select 7  , 'e' union all
select 8  , 'b' union all
select 9  , 'e' union all
select 10 , 'f'

查询：

select clientgroup, count(*) Count from
(
select case when status in ( 'a','b','d') then 'I' else 'II' end clientgroup from @table 
) x group by clientgroup

- Ven

Answer 4

你不需要像jose_bacoy那样两次写CASE语句。你可以在GROUP BY和ORDER BY子句中引用你给它的名称。

SELECT
  CASE WHEN Status IN ('a', 'b', 'd') THEN 'I' ELSE 'II' END AS Status,
  count(*) as Count
FROM Table1
GROUP BY Status
ORDER BY Status

- jannj