sql²éѯÖÐÏÔʾÐòºÅ
sql²éѯÖÐÏÔʾÐòºÅ
2009-06-04 09:27
/* ·½·¨Ò»*/
SELECTÐòºÅ= (SELECT COUNT(¿Í»§±àºÅ) from ¿Í»§ AS LiMing
WHERE LiMing.¿Í»§±àºÅ<= Chang.¿Í»§±àºÅ),
¿Í»§±àºÅ,¹«Ë¾Ãû³Æ
from¿Í»§ AS Chang ORDER BY 1;
GO
/* ·½·¨¶þ: ʹÓÃSQL Server 2005 ¶ÀÓеÄRANK() OVER () Óï·¨*/
SELECT RANK() OVER (ORDER BY ¿Í»§±àºÅ DESC) AS ÐòºÅ,
¿Í»§±àºÅ,¹«Ë¾Ãû³Æ
from¿Í»§;
GO
/* ·½·¨Èý*/
SELECTÐòºÅ= COUNT(*), LiMing.¿Í»§±àºÅ, LiMing.¹«Ë¾Ãû³Æ
from ¿Í»§ AS LiMing, ¿Í»§AS Chang
WHERE LiMing.¿Í»§±àºÅ>= Chang.¿Í»§±àºÅ
GROUP BY LiMing.¿Í»§±àºÅ, LiMing.¹«Ë¾Ãû³Æ
ORDER BY ÐòºÅ;
GO
/* ·½·¨ËÄ
½¨Á¢Ò»¸ö¡¸×Ô¶¯±àºÅ¡¹µÄ×ֶΣ¬È»ºó½«Êý¾ÝÐÂÔöÖÁÒ»¸öÇøÓòÐÔÔÝ´æÊý¾Ý±í£¬
È»ºóÓɸÃÇøÓòÐÔÔÝ´æÊý¾Ý±íÖУ¬½«Êý¾Ýѡȡ³öÀ´£¬×îºóɾ³ý¸ÃÇøÓòÐÔÔÝ´æÊý¾Ý±í
*/
SELECTÐòºÅ= IDENTITY(INT,1,1),¹ÜµÀ,³ÌÐòÓïÑÔ,½²Ê¦,×ÊÀú
INTO #LiMing
fromÎÊȯµ÷²éÒ»;
GO
SELECT * from #LiMing;
GO
DROP TABLE #LiMing;
GO
/*
·½·¨Îå
ʹÓà SQL Server 2005 ¶ÀÓеÄROW_NUMBER() OVER () Óï·¨
´îÅä CTE £¨Ò»°ãÊý¾Ý±í±í´ïʽ£¬¾ÍÊÇ WITH ÄǶÎÓï·¨£©Ñ¡È¡ÐòºÅ2 ~ 4 µÄÊý¾Ý
*/
WITHÅÅÐòºóµÄͼÊé AS
(SELECT ROW_NUMBER() OVER (ORDER BY ¿Í»§±àºÅ DESC) AS ÐòºÅ,
¿Í»§±àºÅ,¹«Ë¾Ãû³Æ
from ¿Í»§)
SELECT * from ÅÅÐòºóµÄͼÊé
WHEREÐòºÅ BETWEEN 2 AND 4;
GO
Ïà¹ØÎĵµ£º
http://xujunprogrammer.blog.hexun.com/8376520_d.html
SQL ServerÖÐÎİæµÄĬÈϵÄÈÕÆÚ×Ö¶Îdatetime¸ñʽÊÇyyyy-mm-dd Thh:mm:ss.mmm
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
Õâ¶ÔÓÚÔÚÒª²»Í¬Êý¾Ý¿â¼ä×ªÒÆÊý¾Ý»òÕßϰ¹ßoracleÈÕÆÚ¸ñʽYYYY-MM-DD HH24:MI:SSµÄÈ˶àÉÙÓÐЩ²»·½±ã.
ÎÒÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄ ......
Window 2003 SQL2000Ô¶³ÌÁ¬½ÓµÄÎÊÌâ½â¾ö°ì·¨2008Äê10ÔÂ07ÈÕ ÐÇÆÚ¶þ 15:31Ò» ¿´ping ·þÎñÆ÷IPÄÜ·ñpingͨ¡£
¡¡¡¡Õâ¸öʵ¼ÊÉÏÊÇ¿´ºÍÔ¶³Ìsql server 2000·þÎñÆ÷µÄÎïÀíÁ¬½ÓÊÇ·ñ´æÔÚ¡£Èç¹û²»ÐУ¬Çë¼ì²éÍøÂ磬²é¿´ÅäÖ㬵±È»µÃÈ·±£Ô¶³Ìsql server 2000·þÎñÆ÷µÄIPƴдÕýÈ·¡£
¡¡¡¡¶þ ÔÚDos»òÃüÁîÐÐÏÂÊäÈëtelnet ·þÎñÆ÷IP ¶Ë¿Ú£¬¿´ ......
et ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[Get_StrArrayLength]
(
@str varchar(1024), --Òª·Ö¸îµÄ×Ö·û´®
@split varchar(10) --·Ö¸ô·ûºÅ
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
&nbs ......
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(1024), --Òª·Ö¸îµÄ×Ö·û´®
@split varchar(10), --·Ö¸ô·ûºÅ
@index int --È¡µÚ¼¸¸öÔªËØ
)
returns varchar(1024)
as
begin
declare @location int
de ......