50¸ö³£ÓÃsqlÓï¾ä
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
Ïà¹ØÎĵµ£º
¹«Ë¾Óиö±¨±íϵͳÏìÓ¦ËٶȲ»ÊǺÜÀíÏ룬µ÷Óŵ÷Á˺ü¸´Î£¬ÓеãЧ¹û£¬µ«²»ÊǺÜÈÃÈËÂúÒâ¡£×î½üÓÖ¿ªÊ¼µ÷ÓÅ£¬ÊÔÁËÊÔsql server 2008µÄ·ÖÇø±í¹¦ÄÜ£¬¿´¿´ÊDz»ÊÇ·ÖÇøÖ®ºóËÙ¶È¿ÉÒÔ¿ìÒ»µã¡£
ÓÃÁËÒ»¸ö°ÙÍò¼¶Êý¾ÝµÄ»ã×ܱíÀ´×ö·ÖÇø£¬·ÖÇøµÄÏë·¨Êǰ´ÔÂÀ´·ÖÇø£¬Ò»¸ö·ÖÇø´æ´¢Ò»¸öÔµÄÊý¾Ý£¬Ò»¸ö·ÖÇøµ ......
¹¦ÄÜ£º·µ»Ø×Ö·û¡¢¶þ½øÖÆ¡¢Îı¾»òͼÏñ±í´ïʽµÄÒ»²¿·Ö
Óï·¨£ºSUBSTRING ( expression, start, length )
SQL ÖÐµÄ substring º¯ÊýÊÇÓÃÀ´×¥³öÒ»¸öÀ¸Î»×ÊÁÏÖÐµÄÆäÖÐÒ»²¿·Ö¡£Õâ¸öº¯ÊýµÄÃû³ÆÔÚ²»Í¬µÄ×ÊÁÏ¿âÖв»ÍêȫһÑù£º
²ÎÊý£º
expression ×Ö·û´®¡¢¶þ½øÖÆ×Ö·û´®¡¢Î ......
´æ´¢¹ý³ÌgetRecordfromPageµÄÄÚÈÝ
//getRecordfromPage.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getRecordfromPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getRecordfromPage]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
G ......
ÔÚ´æ´¢¹ý³Ì»ò´¥·¢Æ÷ÖÐʹÓà Transact-SQL ÓαêµÄµäÐ͹ý³ÌΪ£º
ÉùÃ÷ Transact-SQL ±äÁ¿°üº¬Óα귵»ØµÄÊý¾Ý¡£ÎªÃ¿¸ö½á¹û¼¯ÁÐÉùÃ÷Ò»¸ö±äÁ¿¡£ÉùÃ÷×ã¹»´óµÄ±äÁ¿À´±£´æÁзµ»ØµÄÖµ£¬²¢ÉùÃ÷±äÁ¿µÄÀàÐÍΪ¿É´ÓÁÐÊý¾ÝÀàÐÍÒþʽת»»µÃµ½µÄÊý¾ÝÀàÐÍ¡£
ʹÓà DECLARE CURSOR Óï¾ä½« Transact-SQL ÓαêÓë SELECT Óï¾äÏà¹ØÁª¡£ÁíÍ⣬D ......
select d.code,d.name, sum(w.weight) weight,round(avg(w.price),2) price,sum(w.money) money
from weight_info w left outer join t_dict d on w.productcode=d.code left outer join t_balancecode b on w.balancecode=b.balancecode where 1=1 and w.operdate>TO_TIMESTAMP('2009-11-2 04:12:32.0', ' ......