-- #### テーブル作成
CREATE TABLE [dbo].[Board](
[Idx] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Memo] [nvarchar](100) NOT NULL,
[Visit] [int] NOT NULL,
[RegDate] [datetime] NOT NULL,
CONSTRAINT [PK_Board] PRIMARY KEY CLUSTERED
(
[Idx] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
-- #### データ登録
INSERT INTO [dbo].[Board] (UserID, Title, Memo, Visit, RegDate)
SELECT 'ID1', 'Title1', 'Memo1', 20, GETDATE() UNION ALL
SELECT 'ID2', 'Title2', 'Memo2', 10, GETDATE() UNION ALL
SELECT 'ID3', 'Title3', 'Memo3', 15, GETDATE() UNION ALL
SELECT 'ID4', 'Title4', 'Memo4', 5, GETDATE() UNION ALL
SELECT 'ID5', 'Title5', 'Memo5', 15, GETDATE() UNION ALL
SELECT 'ID6', 'Title6', 'Memo6', 20, GETDATE() UNION ALL
SELECT 'ID7', 'Title7', 'Memo7', 20, GETDATE() UNION ALL
SELECT 'ID8', 'Title8', 'Memo8', 1, GETDATE() UNION ALL
SELECT 'ID9', 'Title9', 'Memo9', 30, GETDATE() UNION ALL
SELECT 'ID10', 'Title10', 'Memo10', 10, GETDATE()
CREATE PROCEDURE [dbo].[pBoardList2_Get]
@Page int
, @ListSize int
AS
SET NOCOUNT ON
SELECT TOP (@ListSize)
B.Idx, B.UserID, B.Title, B.Memo, B.Visit, B.RegDate
FROM
(
SELECT TOP 1 A.Visit, A.Idx FROM
(
SELECT TOP ((@Page -1) * @ListSize + 1) B.Visit, B.Idx
FROM dbo.Board B WITH (NOLOCK)
ORDER BY B.Visit DESC, B.Idx DESC
) A
ORDER BY A.Visit ASC, A.Idx ASC
) A JOIN dbo.Board B WITH (NOLOCK)
ON A.Visit >= B.Visit
AND ((A.Visit = B.Visit AND A.Idx >= B.Idx)
OR (A.Visit != B.Visit))
ORDER BY B.Visit DESC, B.Idx DESC
GO