带group by子句的SQL查询，但将两个不同的值当作相同的值来计数

I have a simple table with two columns, like the one below:

我有一个两列的简单表格，如下所示:

Id | Name
 0 |   A
 1 |   A
 2 |   B
 3 |   B
 4 |   C
 5 |   D
 6 |   E
 7 |   E

I want to make a SQL query which will count how many times each "Name" appears on the table. However, I need a few of these values to count as if they were the same. For example, a normal group by query would be:

我想做一个SQL查询，它将计算每个“名称”出现在表上的次数。但是，我需要其中的一些值作为它们是相同的。例如，一个普通的按查询的组将是:

  select Name, count(*)
    from table
group by Name

The above query would produce the result:

上述查询将产生以下结果:

Name | Count
  A  |   2
  B  |   2
  C  |   1
  D  |   1
  E  |   2

but I need the query to count "A" and "B" as if they were only "A", and to count "D" and "E" as if they were only "D", so that the result would be like:

但我需要查询将“A”和“B”当作“A”，将“D”和“E”当作“D”，这样结果就会是:

Name | Count
  A  |   4            // (2 "A"s + 2 "B"s)
  C  |   1
  D  |   3            // (1 "D"  + 2 "E"s)

How can I make this kind of query?

我该如何进行这种查询?

4 个解决方案

#1

You can make translation with case. Also, you can use subquery or CTE so you don't have to repeat yourself:

你可以用大小写翻译。此外，你也可以使用子查询或CTE，这样你就不必重复自己:

with cte as (
    select
        case Name
            when 'B' then 'A'
            when 'E' then 'D'
            else Name
        end as Name
    from table
)
select Name, count(*)
from cte
group by Name

or with with online translation table:

或与在线翻译表:

select
    isnull(R.B, t.Name), count(*)
from table as t
    left outer join (
        select 'A', 'B' union all
        select 'E', 'D'
    ) as R(A, B) on R.A = t.Name
group by isnull(R.B, t.Name)

#2

If you need A and B, D and E, to count the same, you can build a query like this:

如果您需要A和B、D和E进行相同的计数，您可以构建如下查询:

SELECT
    CASE Name WHEN 'B' THEN 'A' WHEN 'E' THEN 'D' ELSE Name END as Name
,   COUNT(*)
FROM table
GROUP BY CASE Name WHEN 'B' THEN 'A' WHEN 'E' THEN 'D' ELSE Name END

Demo on sqlfiddle.

sqlfiddle演示。

#3

With a layer of abstraction and a CASE (SQL Fiddle example):

有一个抽象层和一个CASE (SQL Fiddle示例):

;WITH x AS
(
    SELECT CASE Name WHEN 'B' THEN 'A'
                     WHEN 'E' THEN 'D'
                     ELSE Name
           END AS Name
    FROM Table1
)
SELECT Name, COUNT(1)
FROM x
GROUP BY Name

With a translation table (SQL Fiddle):

使用翻译表(SQL Fiddle):

CREATE TABLE Translate(FromName char(1), ToName char(1));

INSERT INTO Translate VALUES ('B', 'A'), ('E', 'D');

SELECT COALESCE(t.ToName, a.Name) Name, COUNT(1)
FROM Table1 a
LEFT OUTER JOIN Translate t ON a.Name = t.FromName
GROUP BY COALESCE(t.ToName, a.Name)

FWIW, you can also do this with a VALUES derived table instead of a real table (SQL Fiddle):

FWIW，您还可以使用派生的值表而不是实际的表(SQL Fiddle):

SELECT COALESCE(t.ToName, a.Name) Name, COUNT(1)
FROM Table1 a
LEFT OUTER JOIN 
(
    VALUES ('B', 'A'), 
           ('E', 'D')
) t(FromName, ToName) ON a.Name = t.FromName
GROUP BY COALESCE(t.ToName, a.Name)

#4

this works

这是

 select t.a,count(t.id) from  (
       select case name when 'A' then 'A' when 'B' then 'A' 
                        when 'C' then 'C' when 'D' then 'C'
                        when 'D' then 'D' when 'E' then 'D' end as A,id
        from test) as t
    group by A;

#1