mysql - 检索行中的不同值并计算总数

Question

只是寻找有关我的问题的任何信息，不一定是确切的答案。

我有一个名为 WorkDay 的表。

在工作日表中，有 6 个字段:

Date_of_Workday, Workday_type, Boss, Regular, Tech1, Tech2.

员工可以被分配多个角色，并且还有一些字段为 NULL。

我想编写一个查询，该查询将返回分配给每个工作日的员工总数，但显然，如果有一名员工分配给 Boss 角色和 Tech2 角色，则该员工只能算作一名员工。

编辑:这是我想要的输出。

+------------+------------+----+
|Date_of_Work| Work_type  |    |
+------------+------------+----+
| 2019-02-09 | AM         |  4 |
| 2019-02-09 | PM         |  4 |
| 2019-02-10 | AM         |  3 |
| 2019-02-10 | PM         |  2 |
| 2019-02-11 | AM         |  4 |
| 2019-02-11 | PM         |  4 |
| 2019-02-12 | AM         |  4 |
| 2019-02-12 | PM         |  4 |
| 2019-02-13 | AM         |  4 |
| 2019-02-13 | PM         |  4 |
 ------------+------------+----

这是从工作日中选择*:

+------------+------------+---------+----------+-----------+-----------+
| DateofWork | workday_typ | Boss   | Regular  |   Tech1   |   Tech2   |
+------------+------------+---------+----------+-----------+-----------+
| 2019-02-09 | AM         | LB1     | AW1      | AE1       | JE1       |
| 2019-02-09 | PM         | AE1     | IM1      | AL1       | BJ1       |
| 2019-02-10 | AM         | AE1     | MM1      | MW1       | NULL      |
| 2019-02-10 | PM         | AE1     | AE1      | EB1       | NULL      |
| 2019-02-11 | AM         | LB1     | AB1      | DJ1       | JP1       |
| 2019-02-11 | PM         | LB1     | JE1      | AB2       | BJ1       |
| 2019-02-12 | AM         | LB1     | NS1      | AE1       | MB1       |
| 2019-02-12 | PM         | LB1     | HP1      | EH1       | AL1       |
| 2019-02-13 | AM         | LB1     | EB2      | ME1       | MM1       |
| 2019-02-13 | PM         | LB1     | ME2      | AB2       | DJ1       |
+------------+------------+---------+----------+-----------+-----------+

我不确定是否要进行此查询。任何信息/指示将不胜感激。

Answer 1

看起来您的非规范化表不幸地具有多个包含人员姓名的列。您似乎想要计算每天所有这些列中唯一值的数量。

尝试这样的方法来获取每天的唯一值。这将返回一个以 Date_of_Workday、Workday_Type、Person 作为列的结果集。使用 UNION 消除了重复的 Person 值。

          SELECT Date_of_Workday, Workday_Type, Boss AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type,  Regular AS Person  FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech1 AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech2 AS Person FROM tbl

然后在聚合查询中使用该子查询，如下所示:( http://sqlfiddle.com/#!9/f1320a/1/0 )

SELECT Date_of_Workday, Workday_Type, COUNT(Person) AS PersonCount
  FROM (
          SELECT Date_of_Workday, Workday_Type, Boss AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type,  Regular AS Person  FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech1 AS Person FROM tbl
          UNION 
          SELECT Date_of_Workday, Workday_Type, Tech2 AS Person FROM tbl
       ) Persons
 GROUP BY Date_of_Workday, Workday_Type

此处需要 COUNT(Person) 而不是 COUNT(*)，否则它将计算 NULL 值，从而导致人数过多。