mysql - MySQL查询包含相似数据的3列

Question

在我继承的数据库表中，该结构包含 3 列关注 1、关注 2、关注 3。

Record 1 may have concern1 A, concern2 B (nothing in concern3)
Record 2 may have concern1 C, concern2 A (nothing in concern3)
Record 3 may have concern1 B, concern2 A , concern3 D

我想结束

Concern Count
A         3
B         2
C         1
D         1

I can then use this to produce a chart.

My knowledge of SQL is not enough to do this.

标签： mysql

Answer 1

您可以使用联合查询：

SELECT Concern, COUNT(*) AS Count
FROM
(
    SELECT concern1 AS Concern FROM yourTable UNION ALL
    SELECT concern2 FROM yourTable UNION ALL
    SELECT concern3 FROM yourTable
) t
WHERE
    Concern IS NOT NULL
GROUP BY
    Concern;

顺便说一句，经常需要执行此类查询可能意味着糟糕的表设计。在这种情况下，您可能只想维护一个关注点列，以及关注点类型的辅助列。