 --/*-----瀛樺偍杩囩▼ 鍒嗛〉澶勭悊 瀛欎紵 2005-03-28鍒涘缓 -------*/
--/*----- 瀵规暟鎹繘琛屼簡2鍒嗗鐞嗕娇鏌ヨ鍓嶅崐閮ㄥ垎鏁版嵁涓庢煡璇㈠悗鍗婇儴鍒嗘暟鎹�ц兘鐩稿悓 -------*/
--/*-----瀛樺偍杩囩▼ 鍒嗛〉澶勭悊 瀛欎紵 2005-04-21淇敼 娣诲姞Distinct鏌ヨ鍔熻兘-------*/
--/*-----瀛樺偍杩囩▼ 鍒嗛〉澶勭悊 瀛欎紵 2005-05-18淇敼 澶氬瓧娈垫帓搴忚鍒欓棶棰?------*/
--/*-----瀛樺偍杩囩▼ 鍒嗛〉澶勭悊 瀛欎紵 2005-06-15淇敼 澶氬瓧娈垫帓搴忎慨鏀?------*/
--/*-----瀛樺偍杩囩▼ 鍒嗛〉澶勭悊 瀛欎紵 2005-12-13淇敼 淇敼鏁版嵁鍒嗛〉鏂瑰紡涓簍op max妯″紡 鎬ц兘鏈夋瀬澶ф彁楂?------*/
--/*-----缂虹偣锛氱浉瀵逛箣鍓嶇殑not in鐗堟湰涓婚敭鍙兘鏄暣鍨嬪瓧娈?濡備富閿负GUID绫诲瀷璇蜂娇鐢╪ot in 妯″紡鐨勭増鏈?------*/
CREATE PROCEDURE dbo.proc_ListPageInt
(
@tblName nvarchar(200), ----瑕佹樉绀虹殑琛ㄦ垨澶氫釜琛ㄧ殑杩炴帴
@fldName nvarchar(500) = '*', ----瑕佹樉绀虹殑瀛楁鍒楄〃
@pageSize int = 10, ----姣忛〉鏄剧ず鐨勮褰曚釜鏁?/SPAN>
@page int = 1, ----瑕佹樉绀洪偅涓�椤电殑璁板綍
@pageCount int = 1 output, ----鏌ヨ缁撴灉鍒嗛〉鍚庣殑鎬婚〉鏁?/SPAN>
@Counts int = 1 output, ----鏌ヨ鍒扮殑璁板綍鏁?/SPAN>
@fldSort nvarchar(200) = null, ----鎺掑簭瀛楁鍒楄〃鎴栨潯浠?/SPAN>
@Sort bit = 0, ----鎺掑簭鏂规硶锛?涓哄崌搴忥紝1涓洪檷搴?濡傛灉鏄瀛楁鎺掑垪Sort鎸囦唬鏈�鍚庝竴涓帓搴忓瓧娈电殑鎺掑垪椤哄簭(鏈�鍚庝竴涓帓搴忓瓧娈典笉鍔犳帓搴忔爣璁?--绋嬪簭浼犲弬濡傦細' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----鏌ヨ鏉′欢,涓嶉渶where
@ID nvarchar(150), ----涓昏〃鐨勪富閿?/SPAN>
@Dist bit = 0 ----鏄惁娣诲姞鏌ヨ瀛楁鐨?nbsp;DISTINCT 榛樿0涓嶆坊鍔?1娣诲姞
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----瀛樻斁鍔ㄦ�佺敓鎴愮殑SQL璇彞
Declare @strTmp nvarchar(1000) ----瀛樻斁鍙栧緱鏌ヨ缁撴灉鎬绘暟鐨勬煡璇㈣鍙?/SPAN>
Declare @strID nvarchar(1000) ----瀛樻斁鍙栧緱鏌ヨ寮�澶存垨缁撳熬ID鐨勬煡璇㈣鍙?/SPAN>
Declare @strSortType nvarchar(10) ----鏁版嵁鎺掑簭瑙勫垯A
Declare @strFSortType nvarchar(10) ----鏁版嵁鎺掑簭瑙勫垯B
Declare @SqlSelect nvarchar(50) ----瀵瑰惈鏈塂ISTINCT鐨勬煡璇㈣繘琛孲QL鏋勯�?/SPAN>
Declare @SqlCounts nvarchar(50) ----瀵瑰惈鏈塂ISTINCT鐨勬�绘暟鏌ヨ杩涜SQL鏋勯�?/SPAN>
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------鐢熸垚鏌ヨ璇彞--------
--姝ゅ@strTmp涓哄彇寰楁煡璇㈢粨鏋滄暟閲忕殑璇彞
if @strCondition is null or @strCondition='' --娌℃湁璁剧疆鏄剧ず鏉′欢
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----鍙栧緱鏌ヨ缁撴灉鎬绘暟閲?----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--鍙栧緱鍒嗛〉鎬绘暟
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
  /**//**褰撳墠椤靛ぇ浜庢�婚〉鏁?nbsp;鍙栨渶鍚庝竴椤?*/
if @page>@pageCount
set @page=@pageCount
--/*-----鏁版嵁鍒嗛〉2鍒嗗鐞?------*/
declare @pageIndex int --鎬绘暟/椤靛ぇ灏?/SPAN>
declare @lastcount int --鎬绘暟%椤靛ぇ灏?nbsp;
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***鏄剧ず鍒嗛〉
if @strCondition is null or @strCondition='' --娌℃湁璁剧疆鏄剧ず鏉′欢
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --鍓嶅崐閮ㄥ垎鏁版嵁澶勭悊
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --鍚庡崐閮ㄥ垎鏁版嵁澶勭悊
if @page <= 1 --鏈�鍚庝竴椤垫暟鎹樉绀?nbsp;
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
else --鏈夋煡璇㈡潯浠?/SPAN>
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --鍓嶅崐閮ㄥ垎鏁版嵁澶勭悊
begin
if @page=1
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
else
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'
+' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType
end
end
else
begin
set @page = @pageIndex-@page+1 --鍚庡崐閮ㄥ垎鏁版嵁澶勭悊
if @page <= 1 --鏈�鍚庝竴椤垫暟鎹樉绀?/SPAN>
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'
+' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
------杩斿洖鏌ヨ缁撴灉-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
GO
璋冪敤鏂规硶鍒楀瓙锛?BR>
/**//// <summary>
 /// 閫氱敤鍒嗛〉鏁版嵁璇诲彇鍑芥暟
/// 娉ㄦ剰锛氬湪鍑芥暟璋冪敤澶栭儴鎵撳紑鍜屽叧闂繛鎺ワ紝浠ュ強鍏抽棴鏁版嵁璇诲彇鍣?BR> /// </summary>
/// <param name="comm">SqlCommand瀵硅薄</param>
/// <param name="_tblName">鏌ヨ鐨勮〃/琛ㄨ仈鍚?/SPAN></param>
/// <param name="_fldName">瑕佹煡璇㈢殑瀛楁鍚?/SPAN></param>
/// <param name="_pageSize">姣忛〉鏁版嵁澶у皬</param>
/// <param name="_page">褰撳墠绗嚑椤?/SPAN></param>
/// <param name="_fldSort">鎺掑簭瀛楁</param>
/// <param name="_Sort">鎺掑簭椤哄簭0闄嶅簭1鍗囧簭</param>
/// <param name="_strCondition">杩囨护鏉′欢</param>
/// <param name="_ID">涓昏〃涓婚敭</param>
 /// <param name="_dr">杩斿洖鐨凷qlDataReader ref</param>
public static void CutPageData(SqlConnection conn, ref SqlCommand comm, string _tblName, string _fldName, int _pageSize, int _page, string _fldSort, int _Sort, string _strCondition, string _ID, ref SqlDataReader _dr)
 {
//娉ㄦ剰锛氬湪鍑芥暟璋冪敤澶栭儴鎵撳紑鍜屽叧闂繛鎺ワ紝浠ュ強鍏抽棴鏁版嵁璇诲彇鍣?BR> //comm = new SqlCommand("proc_ListPage",conn);
//comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("@tblName", SqlDbType.NVarChar, 200);
comm.Parameters["@tblName"].Value = _tblName;
comm.Parameters.Add("@fldName", SqlDbType.NVarChar, 500);
comm.Parameters["@fldName"].Value = _fldName;
comm.Parameters.Add("@pageSize", SqlDbType.Int);
comm.Parameters["@pageSize"].Value = _pageSize;
comm.Parameters.Add("@page", SqlDbType.Int);
comm.Parameters["@page"].Value = _page;
comm.Parameters.Add("@fldSort", SqlDbType.NVarChar, 200);
comm.Parameters["@fldSort"].Value = _fldSort;
comm.Parameters.Add("@Sort", SqlDbType.Bit);
comm.Parameters["@Sort"].Value = _Sort;
comm.Parameters.Add("@strCondition", SqlDbType.NVarChar, 1000);
comm.Parameters["@strCondition"].Value = _strCondition;
comm.Parameters.Add("@ID", SqlDbType.NVarChar, 150);
comm.Parameters["@ID"].Value = _ID;
comm.Parameters.Add("@Counts", SqlDbType.Int, 0);
comm.Parameters["@Counts"].Direction = ParameterDirection.Output;
comm.Parameters.Add("@pageCount", SqlDbType.Int, 0);
comm.Parameters["@pageCount"].Direction = ParameterDirection.Output;
_dr = comm.ExecuteReader();
}
璋冪敤渚嬪锛?/SPAN>
CutPageData(conn, ref comm, "VOX_CDSinger", "id, cdsinger, cdsingertype, area, cdsingerreadme", 15, page, "id", 1, strFilter, "id", ref dr);
瀵瑰簲璇存槑锛?/SPAN>
CutPageData(鏁版嵁杩炴帴瀵硅薄, ref Sqlcommand瀵硅薄, "闇�瑕佽〃鎴栬鍥惧悕绉?, "瑕佹煡璇㈢殑瀛楁", 姣忛〉璇诲彇鏁版嵁鏉℃暟, 褰撳墠椤? "鎺掑簭瀛楁鍙瀛楁濡?addtime desc, visitcounts娉ㄦ剰杩欓噷鏈�鍚庝竴涓瓧娈典笉鍔燿esc鎴朼sc 鏈�鍚庝竴涓瓧娈靛搴斾簬鍚庨潰鐨勬帓搴忚鍒?/SPAN>)", 鎺掑簭鏂瑰紡(1 desc 0 asc), where鏉′欢锛堣繖閲屼笉鍐嶆坊鍔爓here鏉′欢娣诲姞濡傦細' and visitcounts>100'锛? 琛ㄤ富閿? ref 杩斿洖鐨凷qlDataReader瀵硅薄);
杩欓噷鐨勮皟鐢ㄥ悓鏍烽�傜敤浜庝箣鍓嶇殑not in鐗堟湰.
 鍛靛懙 杩欓噷鐚粰澶у 鍦ｈ癁蹇箰 锛?BR>
posted on 2005-12-21 09:48 锛絽锝?锛╋綆锝旓絽锝掞鸡锝侊絻锝?/a> 闃呰(443) 璇勮(13) 缂栬緫 鏀惰棌 鏀惰棌鑷?65Key
|
