如何在oracle中合并一组记录？

本文介绍了如何在oracle中合并一组记录？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

考虑下表，

名称| Subject_1 | Marks_1 | Subject_2 | Marks_2 | Tom |数学| 90 | | | Tom | | |科学| 50 | Jon | | |科学| 70 | Jon |数学| 60 | | |

我如何得到以下结果

Name | Subject_1 | Marks_1 | Subject_2 | Marks_2 | Tom |数学| 90 |科学| 50 | Jon |数学| 60 |科学| 70 |

尝试过 GROUP BY 的形式，但没有得到正确的结果，数学总是会在SUbject_2下进入Subject_1和Science。

解决方案

使用： $ b

strong> MAX
• GROUP BY
SELECT NAME， 2 MAX（subject_1）subject_1， 3 MAX（marks_1）marks_1， 4 MAX（subject_2）subject_2， 5 MAX（marks_2）marks_2 6从t 7 GROUP BY名称; 名称SUBJECT_1 MARKS_1 SUBJECT_2 MARKS_2 ---- --------- ---------- --------- - --------- Jon Maths 60科学70 Tom Maths 90科学50 SQL>

在附注中，您需要考虑表格设计。您只能有3列，名称，主题，标记。

如果您希望在同一个表中使用分隔列，那么您应该将它们作为单行为每个学生。当你有一个学生的新主题，然后更新该学生的行，而不是添加一个新的行。

Consider the following table,

Name | Subject_1 | Marks_1 | Subject_2 | Marks_2 | Tom | Maths | 90 | | | Tom | | | Science | 50 | Jon | | | Science | 70 | Jon | Maths | 60 | | |

How do I get the following result

Name | Subject_1 | Marks_1 | Subject_2 | Marks_2 | Tom | Maths | 90 | Science | 50 | Jon | Maths | 60 | Science | 70 |

Tried forms of GROUP BY but did not get correct result, Maths will always come under Subject_1 and Science under SUbject_2.

解决方案

Use:

• MAX
• GROUP BY
SQL> SELECT NAME, 2 MAX(subject_1) subject_1, 3 MAX(marks_1) marks_1, 4 MAX(subject_2) subject_2, 5 MAX(marks_2) marks_2 6 FROM t 7 GROUP BY name; NAME SUBJECT_1 MARKS_1 SUBJECT_2 MARKS_2 ---- --------- ---------- --------- ---------- Jon Maths 60 Science 70 Tom Maths 90 Science 50 SQL>

On a side note, you need to think about your table design. You could only have 3 columns, name, subject, marks.

If you want to have separate columns in the same table, then you should have them as a single row for each student. And when you have a new subject for the student, then update the row for that student, instead of adding a new row.