--第一个
create table tb1
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
) insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70) select name from tb1 where score >= 65 group by name having count(*) = (select count(distinct subject) from tb1)drop table tb1/*
name
------------------------------
a2(所影响的行数为 1 行)
*/
create table tb1
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
) insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70) select name from tb1 where score >= 65 group by name having count(*) = (select count(distinct subject) from tb1)drop table tb1/*
name
------------------------------
a2(所影响的行数为 1 行)
*/
create table tb1
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
)
insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70) select name from tb1 where score >= 65 group by name having count(*) = (select count(distinct subject) from tb1)create table tb2
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
)
insert into tb2 Values('a1','语文',60)
insert into tb2 Values('a1','数学',70)
insert into tb2 Values('a1','英语',70) insert into tb2 Values('a2','语文',65)
insert into tb2 Values('a2','数学',70)
--删除具体个人当中重复的分数,求sql
delete tb2 from tb2 a where exists(select 1 from tb2 where score=a.score and name=a.name and subject>a.subject)
create table tb1
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
) insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a1','英语',70) insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70) --查询select t.* from tb1 t where Subject = (select min(Subject) from tb1 where name = t.name and score = t.score)drop table tb1/*
Name Subject Score
------------------------------ ---------- -----------
a1 语文 60
a1 数学 70
a2 语文 65
a2 数学 70(所影响的行数为 4 行)
*/
(
Name nvarchar(30),
Subject nvarchar(10),
Score int default 0,
) insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70) insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70)
go
select *
from tb1 a
where exists(select 1
from (
select *
from tb1 b
where not exists(select 1 from tb1 where name =b.name and Score < b.Score) ) c
where c.score >= 65 and name = a.name)
select *
from tb1 a
where name in( select name
from tb1 b
where not exists(select 1 from tb1 where name =b.name and Score < b.Score)
and score >= 65 )/*Name Subject Score
------------------------------ ---------- -----------
a2 语文 65
a2 数学 70(所影响的行数为 2 行)Name Subject Score
------------------------------ ---------- -----------
a2 语文 65
a2 数学 70(所影响的行数为 2 行)
*/
delete from tb1insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a1','英语',70) insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70) delete a
from tb1 a
where exists(select 1 from tb1 where name = a.name and score = a.score and subject < a.subject)
select * from tb1
/*Name Subject Score
------------------------------ ---------- -----------
a1 语文 60
a1 数学 70
a2 语文 65
a2 数学 70(所影响的行数为 4 行)
*/delete from tb1insert into tb1 Values('a1','语文',60)
insert into tb1 Values('a1','数学',70)
insert into tb1 Values('a1','英语',70) insert into tb1 Values('a2','语文',65)
insert into tb1 Values('a2','数学',70)
delete a
from tb1 a
where exists(select 1 from tb1 where name = a.name and score = a.score and subject > a.subject)
select * from tb1
/*Name Subject Score
------------------------------ ---------- -----------
a1 语文 60
a1 英语 70
a2 语文 65
a2 数学 70(所影响的行数为 4 行)
*/drop table tb1