£¨×ª£©SQL¾­µäÃæÊÔÌ⼯£¨¶þ£©

µÚʮһÌ⣺
Óбístudents(name,class,grade),ÇëÓñê×¼sqlÓï¾äÍê³É
name class grade
ÕÅÈý Êýѧ 81
ÀîËÄ ÓïÎÄ 70
ÍõÎå Êýѧ 90
ÕÅÈý ÓïÎÄ 60
ÀîËÄ Êýѧ 100
ÍõÎå ÓïÎÄ 90
ÍõÎå Ó¢Óï 81
ÒªÇó: ÓÃsqlÓï¾äÊä³ö¸÷ÃŹ¦¿Î¶¼´óÓÚ80·ÖµÄͬѧÐÕÃû?
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
insert into students values ('Íõ¶þ','Êýѧ',81)
insert into students values ('Íõ¶þ','Ó¢Óï',90)
insert into students values ('ÀîÎå','Êýѧ',83)
insert into students values ('ÀîÎå','Ó¢Óï',90)
insert into students values ('ÀîÎå','»¯Ñ§',90)
---Ñ¡³öËùÓгɼ¨´óÓÚ80·ÖµÄѧÉúÐÕÃû-----
------½â·¨Ò»------
select name from students group by name having min(grade)>80
------½â·¨¶þ------
select distinct Name from students where grade >80 and Name not in (select Name from students where grade <80)
------½â·¨Èý------
select distinct name from students where name not in (select name from students where grade <=80 group by name )
-----½â·¨ËÄ-------
select name from students group by name having name not in (select name from students where grade<=80)
µÚÊ®¶þÌ⣺
ÒÑÖªÒ»¸ö±íµÄ½á¹¹Îª£º
ÐÕÃû ¿ÆÄ¿ ³É¼¨
ÕÅÈý ÓïÎÄ 20
ÕÅÈý Êýѧ 30
ÕÅÈý Ó¢Óï 50
ÀîËÄ ÓïÎÄ 70
ÀîËÄ Êýѧ 60
ÀîËÄ Ó¢Óï 90
ÔõÑùͨ¹ýselectÓï¾ä°ÑËû±ä³ÉÒÔϽṹ£º
ÐÕÃû ÓïÎÄ Êýѧ Ó¢Óï
ÕÅÈý 20 30 50
ÀîËÄ 70 60 90
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
--½â´ð£º
select A.Name,A.grade as ÓïÎÄ,B.grad