34,576
社区成员
发帖
与我相关
我的任务
分享
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
)
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
)
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的捞出来即可