如何在oracle中合并一组记录？ [英] How to merge a Group of records in 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





 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从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.

这篇关于如何在oracle中合并一组记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！