I have a data in my database like this :
我的数据库中有一个数据,如下所示:
jamu_a | jamu_b | khasiat
A | B | Z
A | B | X
A | B | C
And then, I want an output like this :
然后,我想要一个像这样的输出:
jamu_a | jamu_b | khasiat | total
A | B | Z, X, C | 3
I'm not expert in MySQL, what kind of query to produce an output like that? Tell me if MySQL can't do that and need some programming language. Thanks in advance
我不是MySQL的专家,产生这样的输出是什么样的查询?告诉我MySQL是否不能这样做,需要一些编程语言。提前致谢
1 个解决方案
#1
10
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b
- SQLFiddle Demo
- SQLFiddle演示
- MySQL GROUP_CONCAT()
- MySQL GROUP_CONCAT()
OUTPUT
OUTPUT
╔════════╦════════╦═════════╦═══════╗
║ JAMU_A ║ JAMU_B ║ KHASIAT ║ TOTAL ║
╠════════╬════════╬═════════╬═══════╣
║ A ║ B ║ Z,X,C ║ 3 ║
╚════════╩════════╩═════════╩═══════╝
if there are repeating values on column KHASIAT
and you want it to be unique, you can add DISTINCT
on GROUP_CONCAT()
如果列KHASIAT上有重复值并且您希望它是唯一的,则可以在GROUP_CONCAT()上添加DISTINCT
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(DISTINCT khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b
#1
10
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b
- SQLFiddle Demo
- SQLFiddle演示
- MySQL GROUP_CONCAT()
- MySQL GROUP_CONCAT()
OUTPUT
OUTPUT
╔════════╦════════╦═════════╦═══════╗
║ JAMU_A ║ JAMU_B ║ KHASIAT ║ TOTAL ║
╠════════╬════════╬═════════╬═══════╣
║ A ║ B ║ Z,X,C ║ 3 ║
╚════════╩════════╩═════════╩═══════╝
if there are repeating values on column KHASIAT
and you want it to be unique, you can add DISTINCT
on GROUP_CONCAT()
如果列KHASIAT上有重复值并且您希望它是唯一的,则可以在GROUP_CONCAT()上添加DISTINCT
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(DISTINCT khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b