主页 / dba / 问题 / 166200
Accepted
Jaimin Sutariya
Asked: 2017-03-05 06:20:04 +0800 CST

如何获取与另一列在同一表中的匹配列数？

  • 772

我有一张如下表。

    +----+----------+---------+--------+----------+---------+---------------------+
    | id | box_name | diIndex | diMode | diStatus | status  | last_updated        |
    +----+----------+---------+--------+----------+---------+---------------------+
    |  7 | boxa     |       0 |      0 |        0 |    1    | 2017-03-02 10:49:35 |
    |  8 | boxa     |       1 |      0 |        1 |    0    | 2017-03-02 10:49:35 |
    |  9 | boxa     |       2 |      0 |        0 |    1    | 2017-03-02 10:49:36 |
    | 10 | boxa     |       3 |      0 |        0 |    1    | 2017-03-02 10:49:36 |
    | 11 | boxa     |       4 |      0 |        0 |    0    | 2017-03-02 10:49:36 |
    | 12 | boxa     |       5 |      0 |        0 |    2    | 2017-03-02 10:49:36 |
    | 13 | boxa     |       6 |      0 |        0 |    2    | 2017-03-02 10:49:36 |
    | 14 | boxa     |       7 |      0 |        0 |    3    | 2017-03-02 10:49:36 |
    +----+----------+---------+--------+----------+---------+---------------------+

现在我想在表中添加一个新列，这将使我获得状态列的每个匹配记录的总数，如下所示。

+----+----------+---------+--------+----------+---------+---------------+---------------------+
| id | box_name | diIndex | diMode | diStatus | status  | status count  | last_updated        |
+----+----------+---------+--------+----------+---------+---------------+---------------------+
|  7 | boxa     |       0 |      0 |        0 |    1    |       3       | 2017-03-02 10:49:35 |
|  8 | boxa     |       1 |      0 |        1 |    0    |       2       | 2017-03-02 10:49:35 |
|  9 | boxa     |       2 |      0 |        0 |    1    |       3       | 2017-03-02 10:49:36 |
| 10 | boxa     |       3 |      0 |        0 |    1    |       3       | 2017-03-02 10:49:36 |
| 11 | boxa     |       4 |      0 |        0 |    0    |       2       | 2017-03-02 10:49:36 |
| 12 | boxa     |       5 |      0 |        0 |    2    |       2       | 2017-03-02 10:49:36 |
| 13 | boxa     |       6 |      0 |        0 |    2    |       2       | 2017-03-02 10:49:36 |
| 14 | boxa     |       7 |      0 |        0 |    3    |       1       | 2017-03-02 10:49:36 |
+----+----------+---------+--------+----------+---------++--------------+---------------------+

在这里您可以看到，对于status价值1，有3记录，对于具有原样的status_count列的价值等等。 我怎样才能实现这一点？status13

mysql mysql-5.6

1 个回答

  1. Best Answer

    使用窗口函数，将其添加为查询中的列几乎是微不足道的：

    COUNT(*) OVER (PARTITION BY status) AS status_count

    由于 MySQL 尚未实现它们（您必须等待，它们可能在下一个版本中），您现在需要更复杂的东西。可以使用派生表来完成 - 计算这些计数 - 然后连接到基表：

    SELECT 
    t.id, t.box_name, t.diIndex, t.diMode, t.diStatus, t.status,
    g.status_count, t.last_updated
FROM
    tableX AS t
  JOIN
    ( SELECT status, COUNT(*) AS status_count
      FROM tableX 
      GROUP BY status
    ) AS g
  ON
    t.status = g.status ;
    • 4

相关问题