如果主非重复计数>1，我不想显示英语，否则就像往常一样显示。

select id, name, major, mark
from (select id, name, major, mark, 
             count(distinct major) over (partition by id) cnt 
      from table_name)
where cnt = 1 or major <> 'English'

dbfiddle demo

这里有一个选择阅读代码中的注解。
样本数据：

SQL> with
  2  test (id, name, major, mark) as
  3    (select 1, 'Peter', 'English', 90 from dual union all  --> Peter has 2 majors, so - omit English
  4     select 1, 'Peter', 'Art'    , 85 from dual union all
  5     select 2, 'John' , 'Maths'  , 20 from dual union all  --> Display Maths for John
  6     select 3, 'Mike' , 'English', 50 from dual            --> Display English for Mike
  7    ),

查询从这里开始：

8  temp as
  9    -- Count number of distinct major values per each ID
 10    (select id, count(distinct major) cnt_dm
 11     from test
 12     group by id
 13    )
 14  -- Join the "original" table with TEMP so that you could check whether number of major values
 15  -- is greater than 1; if so, don't display English. Otherwise, display what you have.
 16  -- This presumes that there's no major whose name is 'x'
 17  select a.*
 18  from test a join temp b on a.id = b.id
 19  where major <> case when b.cnt_dm > 1 then 'English'
 20                      else 'x'
 21                 end;

        ID NAME  MAJOR         MARK
---------- ----- ------- ----------
         1 Peter Art             85
         2 John  Maths           20
         3 Mike  English         50

SQL>