如何计算所有满足MySQL条件的不同值？

I'm trying to write a query to find distinct values in a particular field, count the number of occurrences of that value where for all instances of that particular value another column value is satisfied, and then display the results as follows (more explanation to follow):

我正在尝试编写一个查询来查找特定字段中的不同值，计算该值的出现次数，其中该特定值的所有实例都满足另一个列值，然后显示结果如下（更多解释为跟随）：

Example db:

示例db：

RowId    Status       MemberIdentifier
-----    ------       ----------------
1       In Progress   111111111
2       Complete      123456789
3       Not Started   146782452
4       Complete      111111111
5       Complete      123456789
6       Not Started   146782452
7       Complete      111111111

Desired Result:

期望的结果：

Status         MemberIdentifierCount 
------         ---------------------- 
Not Started    1
In Progress    1
Complete       1

In the above query, the number of distinct MemberIdentifiers with a given Status are counted and displayed. If a MemberIdentifier has two rows with Status 'Complete' but one with Status 'In Progress,' it is grouped and counted as in progress (i.e., MemberIdentifier= 111111111). For a MemberIdentifier to be grouped and counted as complete, all of its rows must have a Status of 'Complete' (i.e., MemberIdentifier= 123456789). Any insight would be appreciated (MySQL newbie).

在上面的查询中，计算并显示具有给定状态的不同MemberIdentifier的数量。如果MemberIdentifier有两行，状态为“完成”，但其中一行状态为“正在进行”，则将其分组并计为正在进行中（即，MemberIdentifier = 111111111）。要将MemberIdentifier分组并计为完成，其所有行的状态必须为“Complete”（即MemberIdentifier = 123456789）。任何见解将不胜感激（MySQL新手）。

8 个解决方案

#1

Per MemberIdentifier find the status you consider appropriate, e.g. 'In Progress' wins over 'Complete' and 'Not Started'. 'Not Started' wins over 'Complete'. Use conditional aggregation for this.

每个MemberIdentifier找到您认为合适的状态，例如“进行中”赢得“完成”和“未开始”。 '未开始'胜过'完成'。为此使用条件聚合。

select status, count(*)
from
(
  select 
    case when sum(status = 'In Progress') > 0 then 'In Progress'
         when sum(status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from mytable
  group by memberidentifier
) statuses
group by status;

#2

SELECT max_status AS Status
     , COUNT(*) AS ct
    FROM (
        SELECT MAX(Status) AS max_status
            FROM tbl
            GROUP BY MemberIdentifier
         ) AS a
    GROUP BY max_status;

This takes advantage of how these strings compare: "In Progress" > "Complete". In doing so, it does random things to any other member with multiple Statuses.

这利用了这些字符串的比较：“进行中”>“完成”。在这样做时，它会对具有多个状态的任何其他成员执行随机操作。

#3

SQL

SQL

SELECT AdjustedStatus AS Status,
       COUNT(*) AS MemberIdentifierCount
FROM
(SELECT IF(Status='Complete',
           IF(EXISTS(SELECT Status
                     FROM tbl t2
                     WHERE t2.Status = 'In Progress'
                       AND t2.MemberIdentifier = t1.MemberIdentifier),
              'In Progress',
              'Complete'),
           Status) AS AdjustedStatus,
        MemberIdentifier
 FROM tbl t1
 GROUP BY AdjustedStatus, MemberIdentifier) subq
GROUP BY AdjustedStatus;

Online demo

在线演示

http://rextester.com/FFGM6300

http://rextester.com/FFGM6300

Explanation

说明

The first IF() function checks whether the status is "Complete" and if so, checks for the existence of another record with the same MemberIdentifier but with a status of "In Progress": This is done via IF(EXISTS(SELECT...))). If found, a status of "In Progress" is assigned to the AdjustedStatus field, otherwise the AdjustedStatus is set from the (unadjusted) Status value.

第一个IF（）函数检查状态是否为“Complete”，如果是，则检查是否存在具有相同MemberIdentifier但状态为“In Progress”的另一个记录：这是通过IF（EXISTS（SELECT ..）完成的。 ）））。如果找到，则将“正在进行”状态分配给AdjustedStatus字段，否则从（未调整的）状态值设置AdjustedStatus。

With the adjusted status having been derived like this for each of the rows in the table, GROUP BY the AdjustedStatus and MemberIdentifier in order to get all unique combinations of these two field values. This is then made into a subquery - aliased as subq. Then aggregate over (GROUP BY) the AdjustedStatus and count the number of occurrences, i.e. the number of unique MemberIdentifiers for each.

对于表中的每一行，调整后的状态都是这样导出的，GROUP BY AdjustedStatus和MemberIdentifier，以获得这两个字段值的所有唯一组合。然后将其作为子查询 - 别名为subq。然后聚合（GROUP BY）AdjustedStatus并计算出现次数，即每个的唯一MemberIdentifier的数量。

#4

I assume you have 2 tables as below

我假设您有2个表格如下

CREATE TABLE table1 (RowId INT PRIMARY KEY, MemberIdentifier VARCHAR(255));
INSERT INTO table1 (RowId, MemberIdentifier)
VALUES
(1,'111111111'), (2, '123456789'), (3, '146782452'), (4, '111111111'),(5,'123456789'), (6,'146782452'), (7,'111111111');


CREATE TABLE table2 (RowId INT PRIMARY KEY, Status VARCHAR(255));
INSERT INTO table2 (RowId, Status)
VALUES
(1,'In Progress'), (2,'Complete'   ), (3,'Not Started'), (4,'Complete'   ), (5,'Complete'   ), (6,'Not Started'), (7,'Complete'   );

Assuming you don't have millions of records in these tables, you can use the query below to achieve what you want.

假设您在这些表中没有数百万条记录，您可以使用下面的查询来实现您想要的目标。

SELECT CASE WHEN not_started.Status = 'Not Started' 
            THEN 'Not Started' 
            WHEN in_progress.Status = 'In Progress' 
            THEN 'In Progress' 
            WHEN complete.Status = 'Complete' 
            THEN 'Complete' 
       END AS over_all_status,
       COUNT(*) AS MemberIdentifierCount
  FROM  (SELECT DISTINCT t1.MemberIdentifier
          FROM table1 t1) main
        LEFT OUTER JOIN   
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'In Progress') in_progress
            ON (main.MemberIdentifier = in_progress.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Not Started') not_started
        ON (main.MemberIdentifier = not_started.MemberIdentifier)
        LEFT OUTER JOIN
            (SELECT DISTINCT t1.MemberIdentifier, t2.Status
              FROM table1 t1,
                   table2 t2 
             WHERE t1.RowId = t2.RowId
               AND t2.Status = 'Complete') complete
        ON (main.MemberIdentifier = complete.MemberIdentifier)
GROUP BY over_all_status;

Basically the query creates one record per MemberIdentifier containing all three statuses possible. It then groups the result based on the overall status and outputs the count.

基本上，查询为每个MemberIdentifier创建一条记录，其中包含所有可能的三种状态。然后，它根据总体状态对结果进行分组并输出计数。

Output from the query is

查询的输出是

#5

use the following code to get the status of MemberIdentifier

select MemberIdentifier
,case 
when total = cn then 'Complete' 
when total < cn then 'In Progress' 
when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier,case status when 'In Progress' then -1 
                                    when 'Complete' Then 1 
                                    when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

use the following code to get the count of MemberIdentifiers in particular status.

Select count(fstatus) counts,fstatus from (
select MemberIdentifier
,case when total = cn then 'Complete' 
      when total < cn then 'In Progress' 
      when total is null then 'Not Started' END as Fstatus
 from 
(
select sum(stat) total,MemberIdentifier,(select count(MemberIdentifier) as cnt from tbldata t1
     where t1.MemberIdentifier = C.MemberIdentifier
     group by MemberIdentifier) as cn
from (
select MemberIdentifier
,case status when 'In Progress' then -1 when 'Complete' Then 1 when 'Not Started' then null End as Stat from tbldata 
 ) C
 group by MemberIdentifier

 ) as f1

 ) f2 group by fstatus

output :

counts  fstatus
1       Complete
1       In Progress
1       Not Started

#6

If the order of precedence for status is

如果状态的优先顺序是

 Not Started
 In Progress
 Complete

We can use a shortcut...

我们可以使用快捷方式......

   SELECT t.memberIdentifier
        , MAX(t.status) AS status
     FROM mytable t
    GROUP BY t.MemberIdentifier

That gets us the distinct memberIdentifier.

这使我们成为独特的memberIdentifier。

If there are any rows for a member that has rows in 'In Progress' and 'Complete' status, the query will return 'In Progress' as the status.

如果成员中有任何行处于“进行中”和“完成”状态，则查询将返回“进行中”作为状态。

We will get status 'Complete' returned for a member only if that member does not have any rows with a status greater than 'Complete'.

只有当该成员没有状态大于“完成”的任何行时，我们才会为成员返回状态“完成”。

To get counts from that result, we can reference that query as an inline view:

要从该结果中获取计数，我们可以将该查询作为内联视图引用：

 SELECT q.status
      , COUNT(q.memberIdentifier) 
   FROM ( 
          SELECT t.memberIdentifier
               , MAX(t.status) AS status
            FROM mytable t
           GROUP BY t.MemberIdentifier
        ) q
  ORDER BY q.status

Think of if this way... MySQL runs the query between the parens first (MySQL calls this a "derived table". The results from the query is a set of rows which can be queried like a table.

想想是否这样...... MySQL首先在parens之间运行查询（MySQL称之为“派生表”。查询的结果是一组行，可以像表一样查询。

We could do a COUNT(DISTINCT q.memberIdentifier) or, assuming memberIdentifier is guaranteed to be non-NULL, we could do COUNT(1) or SUM(1) and get an equivalent result. (The GROUP BY in the inline view guarantees us that memberIdentifier will be unique.)

我们可以做一个COUNT（DISTINCT q.memberIdentifier），或者假设memberIdentifier保证是非NULL，我们可以做COUNT（1）或SUM（1）并得到一个等价的结果。 （内联视图中的GROUP BY保证memberIdentifier将是唯一的。）

---

In the more general case, where we don't have a convenient shortcut of alphabetic ordering for the precedence of the status... we could use an expression that returns values that are "in order". That makes the query a bit more complicated, but it would work the same.

在更一般的情况下，我们没有状态优先级的字母顺序的方便快捷方式......我们可以使用返回“按顺序”的值的表达式。这使得查询更复杂一些，但它的工作原理会相同。

We could replace t.status with something like this:

我们可以用这样的东西替换t.status：

  CASE t.status
  WHEN 'Complete'    THEN 1
  WHEN 'In Progress' THEN 2
  WHEN 'Not Started' THEN 3
  ELSE 4
  END AS `status_priority`

And replace q.status with something the inverse, to convert back to strings:

并用反转的东西替换q.status，以转换回字符串：

  CASE q.status_priority
  WHEN 1 THEN 'Complete'
  WHEN 2 THEN 'In Progress'
  WHEN 3 THEN 'Not Started'
  ELSE NULL
  END AS `status`

We'd need to decide how we would handle values of status that aren't one of the three... are those going to be ignored, handled as a higher or lower priority than any of the others. (A test case would be rows with status = 'Unknown' and rows with status = 'Abracadabra.

我们需要决定如何处理不是三个中的一个的状态值......那些将被忽略的值，处理的优先级高于其他任何优先级。 （测试用例是状态='未知'的行和状态='Abracadabra的行。

#7

I just modified @thorsten-kettner's solution as you were facing problem while joining table. I have assumed you 2 tables, table1 - which has at least 2 rows (RowID and MemberIdentifier ) and table2 - which has at least 2 rows ( RowID and Status)

我刚刚修改@ thorsten-kettner的解决方案，因为你在加入桌子时遇到了问题。我假设你有2个表，table1 - 至少有2行（RowID和MemberIdentifier）和table2 - 至少有2行（RowID和Status）

select Status, count(*)
from(
  select 
    case when sum(newTable.Status = 'In Progress') > 0 then 'In Progress'
         when sum(newTable.Status = 'Not Started') > 0 then 'Not Started'
         else 'Complete'
    end as status
  from (
    select table1.RowId as RowId, table1.MemberIdentifier as MemberIdentifier, table2.Status as Status from table1 INNER JOIN table2 ON table1.RowId = table2.RowId
  )newTable
  group by newTable.MemberIdentifier
) statuses
group by Status;

#8

Another way using a specific table to configure the order (map to Power of two integer).

使用特定表配置顺序的另一种方法（映射到两个整数的幂）。

This mapping allow bit_or aggregate to simply transpose data.

此映射允许bit_or聚合简单地转置数据。

http://rextester.com/edit/ZSG98543

http://rextester.com/edit/ZSG98543

-- Table bit_progression to determine priority

CREATE TABLE bit_progression (bit_status int PRIMARY KEY, Status VARCHAR(255));
INSERT INTO bit_progression (bit_status, Status)
VALUES
(1,       'Not Started'),  
(2,       'Complete'   ),      
(4,       'In Progress');

select
    Status,
    count(*)
from
    (
    select
         MemberIdentifier,max(bit_status) bit_status
    from
        tbl natural join bit_progression
    group by
        MemberIdentifier
    ) Maxi natural join bit_progression
group by
    Status
;

produce

生产

Status  count(*)

1   Complete    1
2   In Progress 1
3   Not Started 1

Extra :

额外：

select
    MemberIdentifier,
    bit_or(bit_status) bits_status,
    case when bit_or(bit_status) & 4 = 4 then true end as withStatusInProgress,
    case when bit_or(bit_status) & 2 = 2 then true end as withStatusComplete,
    case when bit_or(bit_status) & 1 = 1 then true end as withStatusNotStarted
from
    tbl natural join bit_progression
group by
    MemberIdentifier
;

produce it :

生产它：

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

111111111   6   1       1       NULL
123456789   2   NULL    1       NULL
146782452   1   NULL    NULL    1