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
Ïà¹ØÎĵµ£º
http://kang275284.javaeye.com/blog/154331
Ò»¡¢ ÏȽéÉÜÒ»ÏÂoracle
µÄSGA£ºÊý¾Ý¿âµÄϵͳȫ¾ÖÇø£¬SGAÖ÷ÒªÓÉÈý²¿·Ö¹¹
³É£º¹²Ïí³Ø¡¢Êý¾Ý»º³åÇø¡¢ÈÕÖ¾»º³åÇø¡£
1¡¢ ¹²Ïí³ØÓÖÓÉÁ½²¿·Ö¹¹³É£º¹²ÏíSQL
Çø
ºÍÊý¾Ý×ֵ仺³åÇø¡£¹²ÏíSQL
ÇøרÃÅ´æ·ÅÓû§SQL
Ãü
Áoracle
ʹÓÃ×î½ü×îÉÙʹÓõÈÓÅÏȼ¶Ëã·¨À´¸üи²¸Ç£ ......
ɾ³ýÊý¾Ý¿â±íÖÐijһ×ֶεÄÖظ´¼Í¼£¬Ö»Ïë±£ÁôÒ»Ìõ¼Ç¼¿ÉÓÃÒÔÏ·½·¨£º
Ö´ÐÐÒÔÏÂÓï¾ä£º
declare @max integer,@id varchar(18) --´Ë´¦±äÁ¿ÀàÐ͸ù¾Ý×Ö¶ÎÀàÐÍÉèÖÃ
declare cur_rows cursor local for select ×Ö¶Î,count(*) from ±íÃûgroup by ×ֶΠhaving count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
wh ......
@ECHO OFF
ECHO /////////////////////////////////////////////////
ECHO // Microsoft SQL Server 2005 ÕûºÏ¡¢¼¯³ÉSP3
ECHO // Author : cxygoo
ECHO // Date : 2008-12-22
ECHO /////////////////////////////////////////////////
ECHO ÔËÐдËÅú´¦ÀíÇ°£¬µÃ½¨Ä¿Â¼£ºC:\SQL2005,C:\SQL200 ......
´ÓdateTimeÀàÐÍÊý¾ÝÖлñÈ¡¼¾¶È£º
select cast(datepart(q,sign_date) as varchar(2))+'¼¾¶È'
Ò».sql serverÈÕÆÚʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼ä ......