php - Mysql汇总所有包含两列之一中的值的行

Question

我有一个表，可能在两列(B 列和/或 C 列)之一中具有搜索值。我想根据 A 列生成一个摘要，显示所选搜索值的次数(例如 x ) 出现在每个人的任一列中。我想计算两列中的总数。

+--------+------+----+
|  name  | B    | C  |
+--------+------+----+ 
| john   | x    | z  |
| john   | x    | x  |
| john   | y    | x  |
| peter  | x    | z  |
| peter  | x    | z  |
| amanda | x    | x  |
| amanda | x    | x  |
| amanda | x    | x  |
| amanda | x    | y  |
| amanda | x    | y  |
+--------+-----------+

在上面的示例中，假设我的搜索值为 x，我想计算每个人在 B 列和/或 C 列中出现 x 的次数。我还想生成总计，并生成以下输出:

Name   B  C

john   2  2
peter  2  0
amanda 5  3
total  9  5

我可以像这样分别为每一列做:

     SELECT name, COUNT(*) as count FROM table 
          WHERE A = 'x'
          GROUP BY name
          ORDER BY count DESC";

     SELECT name, COUNT(*) as count FROM table 
          WHERE B = 'x'
          GROUP BY name
          ORDER BY count DESC";

然后我可以循环对每个输出进行单独提取并将它们组合起来，但我想知道是否有一种方法可以在一个 mysql 语句中对两列进行检查和计数？

Answer 1

如果您想要分别计算每列的总和，则可以使用以下 ( SQL Fiddle):

(
  SELECT MTA.Name, Sum(MTA.B = 'x') AS BSum, Sum(MTA.C = 'x') AS CSum
  FROM MyTable MTA
  GROUP BY MTA.Name
)
UNION
(
  SELECT 'Total' AS name, Sum(MTB.B = 'x') AS BSum, Sum(MTB.C = 'x') AS CSum
  FROM MyTable AS MTB
)

或者，如果您希望合并两列的总和，则可以执行类似以下操作 ( SQL Fiddle ):

(
  SELECT MTA.Name, Sum(MTA.B = 'x') + Sum(MTA.C = 'x') AS PersonTotal
  FROM MyTable MTA
  GROUP BY MTA.Name
)
UNION
(
  SELECT 'Total' AS name, Sum(MTB.B = 'x') + Sum(MTB.C = 'x') AS PersonTotal
  FROM MyTable AS MTB
)