如何在MySQL中计算满足条件的唯一值的数量？

Question

我正在尝试编写一个查询以查找特定字段中的唯一值，在所有该特定值的实例中，另一个列的值都满足条件。然后按照以下方式显示结果（稍后会有更多解释）：

示例数据库：

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

期望结果：

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

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

- Funsaized

---

如果一个成员有一条记录是“未开始”，另一条记录是“进行中”，那么最终的实际状态是什么？ - Tim Biegeleisen

一个成员同时拥有“未开始”和“进行中”状态的记录是不可能发生的，但为了逻辑上的完整性，最终状态将被视为“进行中”。 - Funsaized

为了正确回答这个问题并与您的数据库匹配，我们需要一些关于表和结构的更多信息。如果您可以更新您的问题并提供这些字段来自哪里的一些信息，我可以为您编写一个好的查询。我可以使用上面的内容来完成它，但是在查看了您对所提供答案的一些评论后，听起来您的表结构比那复杂一些。 - Dom DaFonte

@Bronco423 顺便说一下，你上面编写的表格是通过在 rowID 上连接而获得的，但是你有一个重复值，即"6 Not Started 146782452"，它有 2 个不同的 rowID。我建议您检查一下之前连接表时的查询。另外，就像已经提到的那样，请提供原始表格。您可以使用此 http://sqlfiddle.com/ 来更好地处理。 - Carmine Tambascia

Answer 1

对于每个 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;

- Thorsten Kettner

---

我在尝试在我的表上下文中执行此查询时遇到了一些问题（我认为问题出在我尝试连接的地方）。我没有在问题中提到；但是，memberidentifier字段来自table1，status字段来自table2。这两个表通过table1.RowId = table2.RowId连接... 为了让我理解，你查询中的“statuses”字段具体是做什么的？ - Funsaized

statuses 不是一个字段，而是我从中选择的子查询的名称。在子查询中，我按成员标识获取状态，在主查询中，我按状态计算行数。 - Thorsten Kettner

Answer 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;

这利用了这些字符串的比较方式："In Progress" > "Complete"。通过这样做，它会对具有多个状态的任何其他成员进行随机处理。

- Rick James

---

使用状态值的字母顺序快捷方式，使用MAX（）将“进行中”优先于“已完成”。 - spencer7593

@spencer7593 - 用大写字母K来描述的修补程序。 （我应该在代码上添加一个闪烁的霓虹灯标志，解释一下什么是修补程序。） - Rick James

未知的是status的其他可能值...我们得到了一些关于可能值的信息。“理论上，理论和实践没有区别。但在实践中，却存在差异。” - spencer7593

SELECT max_status AS 状态, (缺失的伙伴无法更改) - Argus Malware

我不知道为什么你在子查询中使用MAX（Status），如果我们删除max，就没有区别了。http://rextester.com/VYIEFR77975 - Argus Malware

@Argus：MAX(status)在内联视图中用于获取每个MemberIdentifier的单个状态值。（内联视图（派生表）为我们提供了成员的不同列表...外部查询为我们提供了计数。）使用MAX聚合是一种快捷方式。根据规范，“进行中”状态优先于“已完成”状态。字符串比较'In progress'>'Completed'是MAX所依赖的巧合。 - spencer7593

Answer 3

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;

在线演示

http://rextester.com/FFGM6300

解释

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

对于表中的每一行，已经像这样派生了调整后的状态，按AdjustedStatus和MemberIdentifier进行GROUP BY，以获取这两个字段值的所有唯一组合。然后将其转换为子查询-别名为subq。然后在AdjustedStatus上聚合(GROUP BY)并计算出现次数，即每个独特的MemberIdentifier的数量。

- Steve Chambers

Answer 4

我假设你有以下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'   );

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

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;

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

查询的输出结果为：

- Ramesh

---

这个查询似乎有点过度杀伐了...就像用一支猎枪打倒一只松鼠。 - spencer7593

Answer 5

使用以下代码获取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

使用以下代码获取特定状态下MemberIdentifiers的计数。

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

counts  fstatus
1       Complete
1       In Progress
1       Not Started

- Nandish B

Answer 6

如果 status 的优先级顺序为

 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

这将为我们提供独特的memberIdentifier。

如果成员有在状态为'In Progress'和'Complete'的行，则查询将返回状态'In Progress'。

仅当该成员没有任何状态大于'Complete'的行时，我们才会返回成员的状态'Complete'。

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

 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

想象一下... MySQL首先在括号之间运行查询（MySQL称之为“派生表”）。查询结果是一组行，可以像表一样进行查询。

我们可以使用COUNT(DISTINCT q.memberIdentifier)或者假设memberIdentifier保证不为空，我们可以使用COUNT(1)或SUM(1)来获得等效的结果。（内联视图中的GROUP BY确保了memberIdentifier的唯一性。）

---

在更一般的情况下，如果没有方便的字母顺序来确定状态的优先级，我们可以使用一个返回“有序”值的表达式。这使得查询变得更加复杂，但它仍然可以正常工作。

我们可以将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`

将 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`

我们需要决定如何处理不属于这三种状态之一的状态值...它们将被忽略吗？还是作为比其他任何状态更高或更低的优先级进行处理。 （一个测试用例是具有 status = 'Unknown' 和 status = 'Abracadabra' 的行。）

- spencer7593

Answer 7

我刚刚修改了@thorsten-kettner的解决方案，因为您在连接表时遇到了问题。我假设您有两个表，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;

- pkn.47

Answer 8

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

这种映射允许bit_or聚合函数简单地转置数据。

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
;

产生

Status  count(*)

1   Complete    1
2   In Progress 1
3   Not Started 1

额外：

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
;

产生它：

MemberIdentifier bits_status    withStatusInProgress    withStatusComplete  withStatusNotStarted

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

- Indent