英文:
Combine rows when a person has more than one race, to a single row called Two or more Races
问题
在Oracle SQL中，您可以使用以下查询来将具有多个种族的学生合并为一行，命名为"Two or more races"：
SELECT
student_number,
CASE
WHEN COUNT(DISTINCT race) > 1 THEN 'Two or more races'
ELSE MAX(race)
END AS race
FROM your_table
GROUP BY student_number;
这个查询将对学生进行分组，如果学生具有不止一种种族，那么将种族设置为"Two or more races"，否则将保留其中的一个种族。
英文:
Some students within my district have more than one race. I am trying to create a column in Oracle SQL with the students race but I am getting multiple rows for one student because the student has more than one race. I want to combine them into one row called Two or more races. Here is a little example:
Select
student_number,
race
| Student_Number | Race |
|---|---|
| 5144 | White |
| 5144 | Asian |
Any idea on how I could combine them into one row, or is that more something you would do in Python.
答案1
得分: 1
[编辑] 考虑到[@Thorsten Kettner][1]的解决方案，要考虑`NULL`值，只需在`race`字段上添加[COALESCE][2]子句：
```sql
SELECT
student_number,
CASE
WHEN MIN(COALESCE(race, '未知')) = MAX(COALESCE(race, '未知'))
THEN MIN(COALESCE(race, '未知'))
ELSE '两个或更多种族'
END AS 种族
FROM students
GROUP BY student_number
ORDER BY student_number;
输入：
| 学生号码 | 种族 |
|---|---|
| 5144 | 白人 |
| 5144 | 亚洲人 |
| 5145 | NULL |
| 5146 | 白人 |
输出：
| 学生号码 | 种族 |
|---|---|
| 5144 | 两个或更多种族 |
| 5145 | 未知 |
| 5146 | 白人 |
然而，如果你想要将每个"种族"的出现聚合到一个名为'两个或更多种族'的单独列中（假设用逗号分隔），你需要使用Oracle的LISTAGG函数：
SELECT
student_number,
LISTAGG(COALESCE(race, '未知'),',') WITHIN GROUP (ORDER BY student_number) AS "两个或更多种族"
FROM students
GROUP BY student_number;
输出：
| 学生号码 | 种族 |
|---|---|
| 5144 | 亚洲人, 白人 |
| 5145 | 未知 |
| 5146 | 白人 |
在sqlfiddle上查看演示。
<details>
<summary>英文:</summary>
[Edit] Considering the solution from [@Thorsten Kettner][1], to take account of `NULL` values just add the [COALESCE][2] clause on the `race` field:
```sql
SELECT
student_number,
CASE
WHEN MIN(COALESCE(race, 'Unkown')) = MAX(COALESCE(race, 'Unkown'))
THEN MIN(COALESCE(race, 'Unkown'))
ELSE 'Two or more races'
END AS races
FROM students
GROUP BY student_number
ORDER BY student_number;
Input:
| student_number | race |
|---|---|
| 5144 | White |
| 5144 | Asian |
| 5145 | NULL |
| 5146 | White |
Output:
| student_number | races |
|---|---|
| 5144 | Two or more races |
| 5145 | Unknown |
| 5146 | White |
However, if you want to aggregate each occurrence of "Race" in a single column named 'Two or more races' (let's say, separated by a comma), you'll need the LISTAGG function from Oracle:
SELECT
student_number,
LISTAGG(COALESCE(race, 'Unkown'),',') WITHIN GROUP (ORDER BY student_number) "Two or more races"
FROM students
GROUP BY student_number;
Output:
| student_number | races |
|---|---|
| 5144 | Asian, White |
| 5145 | Unknown |
| 5146 | White |
Demo in sqlfiddle.
答案2
得分: 0
你需要进行数据聚合。你将聚合你的数据，以便每个学生编号 (GROUP BY student_number) 得到一行。然后检查最小的种族（即字母顺序中的第一个）是否等于最大的种族。
select
student_number,
case when min(race) = max(race) then min(race) else 'Two or more races' end as races
from mytable
group by student_number
order by student_number;
英文:
You want aggregation. You'll aggregate your data so as to get one row per student number (GROUP BY student_number). Then check whether the minimum race (i.e. first in alphabetical order) equals the maximum one.
select
student_number,
case when min(race) = max(race) then min(race) else 'Two or more races' end as races
from mytable
group by student_number
order by student_number;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论