SQL ServerʾÀý²éѯ
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2¡¢²éѯƽ¾ù³É¼¨´óÓÚ60·ÖµÄͬѧµÄѧºÅºÍƽ¾ù³É¼¨£»
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3¡¢²éѯËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡¿ÎÊý¡¢×ܳɼ¨£»
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4¡¢²éѯÐÕ“ÀÄÀÏʦµÄ¸öÊý£»
select count(distinct(Tname))
from Teacher
where Tname like 'Àî%';
5¡¢²éѯûѧ¹ý“Ҷƽ”ÀÏʦ¿ÎµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Ҷƽ');
6¡¢²éѯѧ¹ý“001”²¢ÇÒҲѧ¹ý±àºÅ“002”¿Î³ÌµÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7¡¢²éѯѧ¹ý“Ҷƽ”ÀÏʦËù½ÌµÄËùÓпεÄͬѧµÄѧºÅ¡¢ÐÕÃû£»
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='Ҷƽ' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='Ҷƽ'));
8¡¢²éѯ¿Î³Ì±àºÅ“002”µÄ³É¼¨±È¿Î³Ì±àºÅ“001”¿Î³ÌµÍµÄËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû£»
Select S#,Sname from (select Student.S#,Student.Snam
Ïà¹ØÎĵµ£º
ǰһ¶Îʱ¼äÔÚά»¤Ò»¸öÀÏÏîÄ¿µÄʱºò·¢ÏÖ£¬ÔÚÖ´ÐÐÒ»¸ö½Ï´óµÄ´æ´¢¹ý³Ìʱ£¬×ÜÊDZ¨“³£¹æÍøÂç´íÎó£¬Çë¼ì²éÄúµÄÍøÂçÎĵµ”¡£ÔÚºóÐøµÄά»¤¼ì²éÖз¢ÏÖ£¬ÊÇÊÂÎﳬʱµ¼ÖÂÖ´ÐÐÖÐÖ¹µÄ¡£
ÏÂÃæÊǵ÷Õû¹ý³Ì£º
Ò»¡¢°ÑSQL SERVERµÄµÈ´ýÏìӦʱ¼ ......
Æäʵɾ³ýÊý¾Ý¿âÖÐÊý¾ÝµÄ·½·¨²¢²»¸´ÔÓ£¬ÎªÊ²Ã´ÎÒ»¹Òª¶à´ËÒ»¾ÙÄØ£¬Ò»ÊÇÎÒÕâÀï½éÉܵÄÊÇɾ³ýÊý¾Ý¿âµÄËùÓÐÊý¾Ý£¬ÒòΪÊý¾ÝÖ®¼ä¿ÉÄÜÐγÉÏà»¥Ô¼Êø¹ØÏµ£¬É¾³ý²Ù×÷¿ÉÄÜÏÝÈëËÀÑ»·£¬¶þÊÇÕâÀïʹÓÃÁË΢ÈíδÕýʽ¹«¿ªµÄsp_MSForEachTable´æ´¢¹ý³Ì¡£
Ò²ÐíºÜ¶à¶ÁÕßÅóÓѶ¼¾Àú¹ýÕâÑùµÄÊÂÇ飺ҪÔÚ¿ª·¢Êý¾Ý¿â»ù´¡ÉÏÇåÀíÒ»¸ö¿Õ¿â£¬µ«ÓÉÓÚ¶ÔÊý¾Ý¿ ......
ÐÐÁе¹ÖÃÔÚsql serverÖÐÊÇÒ»Öֺܳ£¼ûµÄ¼¼ÇÉ£¬ÔÚ×öÓ¦ÓÃϵͳµÄʱºò£¬¾³£ÐèÒª×öһЩͳ¼Æ¹¦ÄܱÜÃâ²»ÁËʹÓÃÐÐÁе¹ÖÃÕâ¸ö¼¼ÇÉ£¬ÎÒССµÄ×öÁËÒ»ÏÂ×ܽ᣺
µÚÒ»ÖÖ£ºsql server 2000ÖÐʹÓÃcase½øÐÐÐÐÁе¹ÖÃ
create table RowCellConvertTest
(
grade varchar(50),
sex varchar(50),
studentCount int
)
......
Ó¦ÓÃÖз¢ÏÖsqlÖеÄand¼°orµÄÖ´ÐÐЧÂÊÎÊÌâ
£ó£ñ£ìÓï¾ä£¬ÎªÊ²Ã´°Ñ×îºóµÄ£ï£ò»»³É£á£î£ä£¬²éѯµÄ¾ÍºÜ¿ì£¬Ê¹ÓõÄÊÇmssql·¶ÀýÖÐnorthwindÊý¾Ý¿âΪÀý£¬
select * from Orders a left join [Order Details] b on a.orderid = b.orderid
where a.customerid like '%ics%' or b.productid in (42,72)
×·×ÙÁËÓï¾äµÄÖ´Ðз½°¸,·¢ÏÖ ......
´ÓTable ±íÖÐÈ¡³öµÚ m Ìõµ½µÚ n ÌõµÄ¼Ç¼£º(Not In °æ±¾)
SELECT TOP n-m+1 *
from Table
WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
--´ÓTABLE±íÖÐÈ¡³öµÚmµ½nÌõ¼Ç¼ (Exists°æ±¾)
SELECT TOP n-m+1 * from TABLE AS a WHERE Not Exists
(Select * from (Select Top m-1 * from TABLE orde ......