ÕÒЩ²»´íµÄsqlÃæÊÔÌâ(1)
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
Ïà¹ØÎĵµ£º
“È«²¿µÄÎÊÌâ”ÔÚÉÏһƪÖÐÒѾд¹ýÒ»µãÁË¡£
“½ö½ö”
£ºÕâ¸öÎÊÌâ¿ÉÒÔʹÓöà±íÁ¬½ÓÀ´´¦Àí¡£ÀýÈ磺²éѯ½ö½öÑ¡ÐÞÁ˿γÌAºÍ¿Î³ÌBµÄѧÉúµÄÐÅÏ¢,sqlÈçÏ£º
ÆäÖбístudent¶¨ÒåÁËѧÉúµÄÐÅÏ¢¡£transcript¶¨ÒåÁËѧÉúµÄÑ¡¿ÎÇé¿ö¡£crscode×ֶαíʾ¿Î³Ì´úÂë¡£
select s.id
from student s,transcript t1,transc ......
ÏÐÀ´Ð´ÏÂwith cubeµÄÓ÷¨
cubeÔËËã·ûÔÚ SELECT Óï¾äµÄ GROUP BY ×Ó¾äÖÐÖ¸¶¨¡£¸ÃÓï¾äµÄÑ¡ÔñÁбíÓ¦°üº¬Î¬¶ÈÁк;ۺϺ¯Êý±í´ïʽ¡£GROUP BY Ó¦Ö¸¶¨Î¬¶ÈÁк͹ؼü×Ö WITH CUBE¡£½á¹û¼¯½«°üº¬Î¬¶ÈÁÐÖи÷ÖµµÄËùÓпÉÄÜ×éºÏ£¬ÒÔ¼°ÓëÕâЩά¶ÈÖµ×éºÏÏàÆ¥ÅäµÄ»ù´¡ÐÐÖеľۺÏÖµ¡£
ÏÈ¿´ÏÂ±í£º
ÎÒÃÇÒÔid¾ÛºÏ²éѯ³öƽ¾ù·Ö
ÕâÒ»ÌõSQLÓï¾äÓ ......
1
:
replace
º¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸ö²ÎÊýÄãÏëÌæ»»µÄ²¿·Ö£¬µÚÈý¸ö²ÎÊýÄãÒªÌæ»»³Éʲô
select
replace
(
'
lihan
'
,
'
a
'
,
'
b
'
)
--
---------------------------
lihbn
£¨ËùÓ°ÏìµÄÐÐÊýΪ
1
ÐУ©
==== ......
SQL°æ£º
alter proc testguo
(
@cityid int,
@cityname nvarchar(100) output
)
as
select @cityname = city_name from BA_Hot_City where cityid = @cityid
select @cityname
go
declare @cityname nvarchar(100)
exec testguo 1,@cityname output
ÁíÒ»°æ£º
ht ......