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
Ïà¹ØÎĵµ£º
Copy ÒÔÏÂÄÚÈݵ½²éѯ·ÖÎöÆ÷²Ù×÷£º
-- 3 ****************************************************
-- ÊìϤϵͳ×Ô´øµÄһЩº¯Êý
-- 3.1 --------------------------------------------------
-- Êýѧº¯Êý
-- ABS(num_expression) ·µ»Ø¸ø¶¨ÊýÖµ±í´ïʽµÄ¾ø¶ÔÖµ
-- C ......
Êý¾Ý¿âÀàÐÍΪDateTimeÀàÐÍ£¬½«Æä×öÄ£ºý²éѯʱ£¬ÔÚHibernateÖУ¬¿ÉÒÔÓÃÆ´SQLµÄÎÊÌ⽫Æä½â¾ö£¬¼´ÔÚSQL²éѯ·ÖÎöÆ÷дSQLÒ»Ñù£¬µ«ÊÇÀûÓòÎÊýÅäÖã¬Ôò·½·¨ÈçÏ£º
1¡¢Æ´ÉÏSQL,È磺
String beginPara= &q ......
1. ¼òµ¥ÐðÊöSQL Server 2000µÄ°²È«ÈÏÖ¤¹ý³Ì¡£
´ð£ºSQL Server 2000ÖУ¬ÏµÍ³Óüì²é¿ÚÁî¡¢ÉèÖýÇÉ«¡¢Ðí¿ÉµÈÊÖ¶ÎÀ´¼ì²éÓû§µÄÉí·Ý¼°Ê¹ÓÃȨÏÞ¡£ºÏ·¨µÄÓû§²ÅÄܽøÈëÊý¾Ý¿âϵͳ£¬µ±Óû§¶ÔÊý¾Ý¿âÖ´ÐвÙ×÷ʱ£¬ÏµÍ³×Ô¶¯¼ì²éÓû§ÊÇ·ñÓÐȨÏÞÖ´ÐÐÕâЩ²Ù×÷¡£Ò»¸öÓû§Èç¹ûÒª·ÃÎÊSQL ServerÊý¾Ý¿âÖеÄÊý¾Ý£¬Ëû±ØÐëÒª¾¹ýÈý¸öÈÏÖ¤¹ý³Ì¡£
£ ......
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, -- Éè ......
֮ǰµÄSQLÓï¾äÈçÏ£¬²éѯºÜÂý£¬6·ÖÖӶ඼ûÓнá¹ûÏÔʾ£¬Ò»¿ªÊ¼ÎÒÒÔΪÊÇ6ÕűíÁªºÏ²éѯ´øÀ´µÄËÙ¶ÈÂýµÄÎÊÌâ¡£
ºóÀ´·¢ÏÖÒòΪPO_D.ORDNOÓëPO_H.ORDNOÏàͬ£¬°ÑÂÌÉ«²¿·ÖÌæ»»Îª×ÏÉ«µÄ²¿·Ö£¬
°ÑPO_DÌæ»»ÎªPO_H£¬10ÃëÖÓ¾ÍÄܲéѯ³öÀ´
PS:PO_DÖÐ101401±ÊÊý¾Ý£¬PO_HÖÐ51341±ÊÊý¾Ý¡£
ËäûÓÐÏëÃ÷°×Ìæ»»Ö®ºóÌáËÙµ½Èç´ËÖ®¿ì½ö10Ã룬Óë6·Ö ......