SQL 分页存储过程 以及SQL concatenate 几种方法 

  1 USE [GPP]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[P_V_USER_INFO_ROLE_SCHEME_S_PAGE_BY_APPID_ROLEID]    Script Date: 14/11/2014 10:10:36 AM ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER PROC [dbo].[P_V_USER_INFO_ROLE_SCHEME_S_PAGE_BY_APPID_ROLEID]
  9 (
 10 @p_app_id nvarchar(50),
 11 @p_role_id nvarchar(50),
 12 @page_index int,
 13 @page_size  int
 14 )
 15 as
 16 WITH PageIndex AS 
 17 ( SELECT TOP (@page_index+@page_size) ROW_NUMBER() OVER (ORDER BY LAST_ACTIVITY_TIME) 
 18 as RowIndex, 
 19        [USER_ID]
 20 
 21       ,[APP_ID]
 22 
 23       ,[USER_NAME]
 24 
 25       ,[LOWERED_USER_NAME]
 26 
 27       ,[MOBILE_ALIAS]
 28 
 29       ,[IS_ANONYMOUS]
 30 
 31       ,[LAST_ACTIVITY_TIME]
 32 
 33       ,[FLAG]
 34 
 35       ,[AD_ID]
 36 
 37       ,[NRIC]
 38 
 39       ,[DESIGNATION]
 40 
 41       ,[AGENCY_ID]
 42 
 43       ,[ADDRESS]
 44 
 45       ,[AGE]
 46 
 47       ,[EMAIL_ADDRESS]
 48 
 49       ,[ROLE_ID]
 50 
 51       ,[ROLE_NAME]
 52 
 53       ,[SCHEME_ID]
 54 
 55       ,[SCHEME_CODE]
 56 
 57       ,[SCHEME_NAME]
 58 
 59       ,[IS_ACTIVE]
 60 
 61       ,[USER_IN_ROLE_ID]
 62 
 63       ,[ROLE_IS_DELETED]
 64       ,[USER_IS_DELETED]
 65 
 66       ,[CAN_VIEW_VIP]
 67 
 68       ,[IS_POSTQA_VERIFIER]
 69 
 70       ,[UF_VERSION_NO]
 71 
 72       ,[U_VERSION_NO]
 73 
 74       ,[UIR_VERSION_NO]
 75 
 76       ,[FULL_NAME]
 77 
 78       ,[dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]([USER_ID]) AS SCHEME_ROLE
 79 
 80     FROM [V_USER_INFO_ROLE_SCHEME] 
 81 
 82     WHERE 
 83 
 84     APP_ID=@p_app_id AND ROLE_ID=@p_role_id and USER_IS_DELETED=0 ) 
 85 SELECT
 86       [USER_ID]
 87 
 88       ,[APP_ID]
 89 
 90       ,[USER_NAME]
 91 
 92       ,[LOWERED_USER_NAME]
 93 
 94       ,[MOBILE_ALIAS]
 95 
 96       ,[IS_ANONYMOUS]
 97 
 98       ,[LAST_ACTIVITY_TIME]
 99 
100       ,[FLAG]
101 
102       ,[AD_ID]
103 
104       ,[NRIC]
105 
106       ,[DESIGNATION]
107 
108       ,[AGENCY_ID]
109 
110       ,[ADDRESS]
111 
112       ,[AGE]
113 
114       ,[EMAIL_ADDRESS]
115 
116       ,[ROLE_ID]
117 
118       ,[ROLE_NAME]
119 
120       ,[SCHEME_ID]
121 
122       ,[SCHEME_CODE]
123 
124       ,[SCHEME_NAME]
125 
126       ,[IS_ACTIVE]
127 
128       ,[USER_IN_ROLE_ID]
129 
130       ,[ROLE_IS_DELETED]
131       ,[USER_IS_DELETED]
132 
133       ,[CAN_VIEW_VIP]
134 
135       ,[IS_POSTQA_VERIFIER]
136 
137       ,[UF_VERSION_NO]
138 
139       ,[U_VERSION_NO]
140 
141       ,[UIR_VERSION_NO]
142 
143       ,[FULL_NAME] 
144 
145       ,[dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]([USER_ID]) AS SCHEME_ROLE
146     
147       FROM PageIndex WHERE RowIndex >@page_index AND RowIndex <= (@page_index+@page_size) ORDER BY LAST_ACTIVITY_TIME
148 
149 SELECT COUNT(*) AS TotalRowCount FROM 
150 (
151 SELECT [USER_ID] FROM V_USER_INFO_ROLE_SCHEME  
152 WHERE APP_ID=@p_app_id AND ROLE_ID=@p_role_id and USER_IS_DELETED=0 
153 GROUP BY USER_ID)X
View Code

这是一段关于存储过程分页的代码，，用Row_NUMBER 实现，， 其中 [dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]  为SQL Concatenate的实现。

 下面介绍几种实现该方法的代码。。

 1 CREATE FUNCTION [dbo].[F_T_USER_IN_ROLE_S_SCHEMEROLE_BY_USERID]
 2 
 3 (
 4 
 5        @USER_ID VARCHAR(50)
 6 
 7 )
 8 
 9 RETURNS VARCHAR(1000)
10 
11 AS
12 
13   BEGIN
14 
15        DECLARE @SchemeRole VARCHAR(1000) = ''
16 
17 
18 
19        SELECT @SchemeRole = @SchemeRole + s.SCHEME_NAME + '-' + r.ROLE_NAME + ';<br/>'
20 
21        FROM dbo.T_USER_IN_ROLE AS ur
22 
23        INNER JOIN dbo.T_SCHEME AS s ON ur.SCHEME_ID = s.SCHEME_ID
24 
25        INNER JOIN dbo.T_IC_ROLES AS r ON ur.ROLE_ID = r.ROLE_ID
26 
27        WHERE ur.USER_ID = @USER_ID and ur.IS_DELETED=0
28 
29 
30 
31     RETURN @SchemeRole
32 
33   END
View Code

这段感觉是比较好的  至少比下面哪种取xml的方法好得多。。 但还是介绍一下。。

SELECT     
         USER_ID,
  STUFF
  ((SELECT ';' +  (CAST(ROLE_NAME AS VARCHAR(MAX))+','+CAST(SCHEME_CODE AS VARCHAR(MAX)))
    FROM V_USER_INFO_ROLE_SCHEME
    WHERE 
     (USER_ID = Results.USER_ID)
    FOR 
     XML 
     PATH ('')),1,1,''
  ) AS SCHEME_ROLE

     FROM [V_USER_INFO_ROLE_SCHEME]  as Results  group by  USER_ID
View Code

 

posted @ 2014-11-14 10:35  just so  阅读(198)  评论(0)  编辑  收藏  举报