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
Ïà¹ØÎĵµ£º
Ò»¡¢²Ù×÷·ûÓÅ»¯
1¡¢IN ²Ù×÷·û
ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ׶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵ģ¬´ÓOracleÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£¬Èç¹ûת»»³É¹ ......
´íÎóÈçÏ£º
Database character set (AL32UTF8) and Client character set (ZHS16GBK) are different.
Character set conversion may cause unexpected results.
Note: you can set the client character set through the NLS_LANG environment variable or the NLS_LANG registry key in
HKEY_LOCAL_MACHINE\SOFTWARE\ ......
CREATE PROCEDURE fenye
@tblName varchar(255)='wdf1', -- ±íÃû
@strGetFields varchar(1000) = '*', -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255)='userid', -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@doCount bit = 0, -- ·µ»Ø¼Ç¼×ÜÊý, ·Ç 0 ÖµÔò·µ»Ø
@OrderType bit = 0, -- Éè ......
ºÜ¶àʱºò£¬ÎÒÃÇ¿ÉÄÜÏ£Íû°´Ô¡¢°´Ìì¡¢°´Äê×öһЩÊý¾Ýͳ¼Æ£¬µ«ÊÇ£¬ÎÒÃÇʵ¼Ê±£´æµÄÊý¾Ý¿ÉÄÜÊÇÒ»¸öºÜ¾«È·µÄ·¢Éúʱ¼ä£¬¿ÉÄÜÊǵ½Ãë¡£ÈçºÎ¸ù¾ÝÒ»¸öʱ¼äÖ®½ØÈ¡ÆäÖеÄÒ»²¿·Ö¾Í³ÉÁËÎÊÌâ¡£
ÓÐÁ½¸ö½â¾ö·½·¨£º
×îÖ±½ÓµÄÏë·¨ÀûÓÃDatePart»òÕßYear¡¢Month¡¢Dayº¯Êý
CAST(
(
STR( Y ......
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 ......