mysql两个列的总和，条件在同一行(mysql sum of two columns with condition in same row)

mysql两个列的总和，条件在同一行(mysql sum of two columns with condition in same row)

我有一个包含这样的值的表

+-------+-----------+------------+------------+-----------+ | User | activity1 | activity2 | activity3 | activity4 | +-------+-----------+------------+------------+-----------+ | user1 | A.C.E A1 | A.... | ABCDE ZZZ | A.C.. | | user2 | A.C.. | A.CD. | ABCDE text | ABCD. | | user3 | ABC.. | A.C.E free | A.... | A.... | +-------+-----------+------------+------------+-----------+

我希望结果像

+-------+---+---+---+---+ | User | A | B | C | D | +-------+---+---+---+---+ | user1 | 4 | 1 | 3 | 1 | | user2 | 4 | 2 | 4 | 3 | | user3 | 4 | 1 | 2 | 0 | +-------+---+---+---+---+

ABC和D的结果是每行的活动1..4中的每个文本的存在的摘要。

我尝试用SUM（CASE WHEN，我必须做错了，它给了我错误的计数。我不能得到计数结果超过1。

E之后的文本将是自由文本，这是可能的，此刻并不在意。

这个数据是原始的csv格式从其他系统导出，我可以这样得到它然后导入到mysql服务器。

我不是sql的专家。 有人可以帮助找到查询的解决方案。

I have a table which contain value like this

+-------+-----------+------------+------------+-----------+ | User | activity1 | activity2 | activity3 | activity4 | +-------+-----------+------------+------------+-----------+ | user1 | A.C.E A1 | A.... | ABCDE ZZZ | A.C.. | | user2 | A.C.. | A.CD. | ABCDE text | ABCD. | | user3 | ABC.. | A.C.E free | A.... | A.... | +-------+-----------+------------+------------+-----------+

I want result something like

+-------+---+---+---+---+ | User | A | B | C | D | +-------+---+---+---+---+ | user1 | 4 | 1 | 3 | 1 | | user2 | 4 | 2 | 4 | 3 | | user3 | 4 | 1 | 2 | 0 | +-------+---+---+---+---+

Result of A B C and D are summary of present of each text in columns activity1..4 for each row.

I try with SUM(CASE WHEN which I must do something wrong and it give me wrong count. I can not get count result more than 1.

Text after E would be free text what ever can be possible and don't care at the moment.

This data was original in csv format export from some other system which I can have to get it this way then import to mysql server.

I am not expert in sql. Could someone help to find solution for query.

最满意答案

如果我理解正确，你想要计算每行中“A”，“B”等的数量。

基本想法是这样的：

select user, ((activity1 like '%A%') + (activity2 like '%A%') + (activity3 like '%A%') + (activity4 like '%A%')) as a, ((activity1 like '%B%') + (activity2 like '%B%') + (activity3 like '%B%') + (activity4 like '%B%')) as b, ((activity1 like '%C%') + (activity2 like '%C%') + (activity3 like '%C%') + (activity4 like '%C%')) as c, ((activity1 like '%D%') + (activity2 like '%D%') + (activity3 like '%D%') + (activity4 like '%D%')) as d from t;

If I understand correctly, you want to count the number of "A"s, "B"s and so on in each row.

The basic idea is something like:

select user, ((activity1 like '%A%') + (activity2 like '%A%') + (activity3 like '%A%') + (activity4 like '%A%')) as a, ((activity1 like '%B%') + (activity2 like '%B%') + (activity3 like '%B%') + (activity4 like '%B%')) as b, ((activity1 like '%C%') + (activity2 like '%C%') + (activity3 like '%C%') + (activity4 like '%C%')) as c, ((activity1 like '%D%') + (activity2 like '%D%') + (activity3 like '%D%') + (activity4 like '%D%')) as d from t;