SQLÃæÊÔÌâС½á

 
ÎÒÏëÃæÊÔ¹ýÈí¼þ¿ª·¢µÄÅóÓѶ¼»áÅöµ½sql·½ÃæµÄÃæÊÔÌ⣬Õâ¸ö¿ÉÒÔ˵ÊÇÃæÊԱؿ¼µÄ¡£ÕâÀïÄü¸¸öÀý×Ó¿ªÍØÒ»ÏÂ˼·¡£
1.      
ÓÐÕâÑùÒ»Õűí
½ÌʦºÅ
ÐÇÆÚ
ÊÇ·ñÓпÎ
1
1
ÓÐ
2
3
ÓÐ
1
2
ÓÐ
1
2
ÓÐ
ÒªµÃ³öÕâÑùµÄÊý¾Ý£º
ÐÕÃû
ÐÇÆÚÒ»
ÐÇÆÚ¶þ
ÐÇÆÚÈý
ÐÇÆÚËÄ
ÐÇÆÚÎå
1
1
2
Null
Null
Null
2
Null
Null
1
Null
Null
²»ÓùܾßÌåµÄ±í½á¹¹£¬ÎÒÃÇ¿´¿´ÈçºÎµÃµ½ÕâÑùµÄ½á¹û£º
Ê×ÏÈÎÒÃÇͨ¹ýsql´´½¨Ò»ÕÅ±í£¬È»ºó²åÈëÊý¾Ý£º
create table Course
(
    TeacherId int,
    Week int,
    HasCourse varchar(2)
)
insert into Course values('1','1','ÓÐ')
ÎÒÃÇ·ÖÎö·¢ÏÖ,Ôڵõ½µÄÊý¾ÝÖÐ,ÐÇÆÚÒ»ÕâÑùµÄ×Ö¶ÎÔÚÔ­±íÖÐÊDz»´æÔÚµÄ,ËùÒÔÈçºÎ²úÉúÕâЩ×Ö¶ÎÊǹؼüËùÔÚ,¹À¼ÆÕâ¸öºÜ¶à³õѧÕßÒ²±È½ÏÉÙÓÃ,µ«ÊÇÈ´ºÜÓÐÓÃ.ÁíÍâÒ»µã¾ÍÊǶÔÓÚûÓпεÄÒªÏÔʾΪ¿Õ,¶ø²»ÊÇ0.
ºÃÁË,ÎÒÃÇ¿´Ò»ÏÂÈçºÎͳ¼Æ²éѯ£º
select distinct TeacherId as ½ÌʦºÅ,
ÐÇÆÚÒ»=(select case count(*) when 0 then null else count(*) end from Course where TeacherId=b.TeacherId and Week='1'),
ÐÇÆÚ¶þ=(select case count(*) when 0 then null else count(*) end from Course where TeacherId=b.TeacherId and Week='2'),
ÐÇÆÚÈý=(select case count(*) when 0 then null else count(*) end from Course where TeacherId=b.TeacherId and Week='3'),
ÐÇÆÚËÄ=(select case count(*) when 0 then null else count(*) end from Course where TeacherId=b.TeacherId and Week='4'),
ÐÇÆÚÎå=(select case count(*) when 0 then null else count(*) end from Course where TeacherId=b.TeacherId and Week='5')
from Course b group by TeacherId
 
2.  ²éѯ±íUserµÄµØ30µ½40ÌõÊý¾Ý,idÖ÷¼ü²¢ÇÒ²»Á¬Ðø.
¿ÉÒÔ˵ÕâÊÇÀϹŶ­ÁË,²»¹ý¿´Á½ÖÖ·½·¨°É:
select top 10 * from [User]
where id not in
(select top 30 id from [User])
 
select top 10 * from [User]
where id>(select max(id) from (select top 30 id from [User]) as T)
3.      
²éѯSaleDetail±íÖÐGoodsNameÖظ´³öÏÖÈý´Î¼°ÒÔÉϵļǼ.
select * from (select goodsName, count(goodsName) as c from SaleDetail group
by goodsName)as t where t.c>=3
4