英文:
How to get unique values from multiple-column values in MySQL?
问题
| stf_invld_a | stf_invld_b | stf_invld_c | stf_invld_d | stf_invld |
|---|---|---|---|---|
| 21,22 | 23,25 | 24 | 26,27 | 21,22,23,24,25,26,27 |
英文:
I have a query result using the group_concat function in MySQL like below.
| stf_invld_a | stf_invld_b | stf_invld_c | stf_invld_d |
|---|---|---|---|
| 21,22,0,0 | 21,23,25,0 | 21,24,25,0 | 24,26,27,0 |
Now I need a query where each column value will get only once, zero values will be skipped, and need another column with unique values.
| stf_invld_a | stf_invld_b | stf_invld_c | stf_invld_d | stf_invld |
|---|---|---|---|---|
| 21,22 | 23,25 | 24 | 26,27 | 21,22,23,24,25,26,27 |
答案1
得分: 1
广义而言，您应该使用GROUP_CONCAT与DISTINCT和CASE表达式。类似这样：
SELECT -- 在GROUP BY中出现的某些列
GROUP_CONCAT(DISTINCT CASE WHEN a != 0 THEN a END) AS stf_invld_a
FROM yourTable
GROUP BY -- 某些列
英文:
Broadly speaking, you should use GROUP_CONCAT along with DISTINCT and a CASE expression. Something like:
<!-- language: sql -->
SELECT -- certain columns which appear in GROUP BY
GROUP_CONCAT(DISTINCT CASE WHEN a != 0 THEN a END) AS stf_invld_a
FROM yourTable
GROUP BY -- certain columns
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论