MySQL基础题

[从风～]

在student_score数据库中，查询选修课有两门分数在80分(含)以上的学生成绩信息，结果如下图


下面是相关表的内容：

[晴天8]

你到底想干什么？

[从风～]

引用 1 楼 晴天8的回复:

你到底想干什么？

写出这题的sql指令忘打这句话了

[小野马1209]

declare @temp table(PersonId varchar(50),Name varchar(50),subject VARCHAR(50),score int)
insert into @temp
SELECT '2014013601','陈明','数学',80 union all
SELECT '2014013601','陈明','语文',70 union all
SELECT '2014013602','张三','语文',85 union all
SELECT '2014013602','张三','数学',90 union all
SELECT '2014013602','张三','英语',65

SELECT A.*,
Que=(SELECT TOP 1 COUNT(1) OVER(PARTITION BY B.PersonId ) FROM @temp B WHERE B.PersonId=A.PersonId and B.score>=80)
 FROM @temp A

把结果>=2的捞出来即可

[从风～]

引用 3 楼 kaijie_wu1209的回复:

declare @temp table(PersonId varchar(50),Name varchar(50),subject VARCHAR(50),score int)
insert into @temp
SELECT '2014013601','陈明','数学',80 union all
SELECT '2014013601','陈明','语文',70 union all
SELECT '2014013602','张三','语文',85 union all
SELECT '2014013602','张三','数学',90 union all
SELECT '2014013602','张三','英语',65

SELECT A.*,
Que=(SELECT TOP 1 COUNT(1) OVER(PARTITION BY B.PersonId ) FROM @temp B WHERE B.PersonId=A.PersonId and B.score>=80)
 FROM @temp A

把结果>=2的捞出来即可

额😓能用子查询吗，这好像还没学

[晴天8]

我就用上面大佬的代码块借花献佛一下了 你要的子查询

declare @temp table(PersonId varchar(50),Name varchar(50),subject VARCHAR(50),score int)
insert into @temp
SELECT '2014013601','陈明','数学',80 union all
SELECT '2014013601','陈明','语文',70 union all
SELECT '2014013602','张三','语文',85 union all
SELECT '2014013602','张三','数学',90 union all
SELECT '2014013602','张三','英语',65
 
SELECT * 
from @temp
where personid in
(
    select personid from @temp 
    where score>=80 
    group by personid 
    having count(1)>=2
)

[小野马1209]

declare @temp table(PersonId varchar(50),Name varchar(50),subject VARCHAR(50),score int)
insert into @temp
SELECT '2014013601','陈明','数学',80 union all
SELECT '2014013601','陈明','语文',70 union all
SELECT '2014013602','张三','语文',85 union all
SELECT '2014013602','张三','数学',90 union all
SELECT '2014013602','张三','英语',65

SELECT * from @temp A
where EXISTS
(
    select 1 
    From @temp  B
    WHERE B.PersonId=A.PersonId
    AND B.score>=80 
    GROUP BY B.PersonId 
    HAVING count(1)>=2
)

[从风～]

好的谢谢，知道了