英文:
SQL Result to multiple array
问题
你可以使用 SQL 查询来实现所需的结果。你可以使用 GROUP BY 和 GROUP_CONCAT 函数来将具有相同员工的行合并为所需的格式。以下是一个示例 SQL 查询：
SELECT staff, GROUP_CONCAT(province SEPARATOR ', ') AS province
FROM your_table_name
GROUP BY staff;
记得将 your_table_name 替换为你实际使用的表名。这个查询将按员工分组，并使用逗号和空格作为分隔符，将相同员工的省份合并成一个字段，并显示所需的结果格式。
英文:
MY SQL returns the following array...
| id | staff | province |
|---|---|---|
| 1 | Ben | Ontario |
| 2 | Ben | Quebec |
| 3 | John | Manitoba |
| 4 | John | Saskatchewan |
| 6 | Kitty | Alberta |
| 7 | Kitty | Nova Scotia |
I would like to have the record displayed like this...
| staff | province |
|---|---|
| Ben | Ontario, Quebec |
| John | Quebec, Manitoba, Saskatchewan |
| Kitty | Alberta, Nova Scotia |
what approach should I use to approach this?
答案1
得分: 1
以下是翻译好的部分：
"Would be better to post the tables as well for clearer context.
You can use Aggregate functions and Grouping to help doing this. A GROUP BY to group the rows by staff column, then use GROUP_CONCAT() to concatenate province values in one string.
A reference of how you want it to be, unsure what table you are using or if there are any other factors but you can adapt as needed.
SELECT staff, GROUP_CONCAT(province SEPARATOR ', ') as province
FROM table_name
GROUP BY staff;"
请注意，代码部分不需要翻译。
英文:
Would be better to post the tables as well for clearer context.
You can use Aggregate functions and Grouping to help doing this. A GROUP BY to group the rows by staff column, then use GROUP_CONCAT() to concatenate province values in one string.
A reference of how you want it to be, unsure what table you are using or if there are any other factors but you can adapt as needed.
SELECT staff, GROUP_CONCAT(province SEPARATOR ', ') as province
FROM table_name
GROUP BY staff;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论