مشکل در ROW_NUMBER()

[fakhravari]

با سلام
کد زیر ببنید دوستان
ALTER PROCEDURE [dbo].[CastumPaging]
(
@TableName nvarchar(20),
@PageIndex int,
@PageSize int
)
AS
Declare @SQL Nvarchar(max);


Declare @RowNumStart Nvarchar(30) = ((@PageIndex - 1) * (@PageSize + 1))
Declare @RowNumFinish Nvarchar(30) = (@PageIndex * @PageSize)

select (SELECT ISNULL(count(*), 0)
FROM dbo.B_Topics INNER JOIN
dbo.RatingPost ON dbo.B_Topics.TopicID = dbo.RatingPost.PostID
WHERE (dbo.RatingPost.PostID = B_Topics.TopicID))AS Rating_Of_count
,
(SELECT ISNULL(count(*), 0)
FROM dbo.B_Topics INNER JOIN
dbo.Comment_Post ON dbo.B_Topics.TopicID = dbo.Comment_Post.TopikID
WHERE (dbo.Comment_Post.TopikID = B_Topics.TopicID))AS Comment_Of_count
,(SELECT ISNULL(SUM(dbo.RatingPost.Rating), 0)
FROM dbo.B_Topics INNER JOIN
dbo.RatingPost ON dbo.B_Topics.TopicID = dbo.RatingPost.PostID
WHERE (dbo.RatingPost.PostID = B_Topics.TopicID))AS SumOfRating,
* from (select row_number()over(order by TopicID) as Rng,
* from B_Topics)as d where Rng between @RowNumStart and @RowNumFinish

در سلکت بالا
تعداد نظرات
جمع امتیاز
تعداد امتیاز
کل اطلاعات جدول پست میاد
چطوری کل اطلاعات
تعداد نظرات
جمع امتیاز
تعداد امتیاز
به صورت یکسان میاد.
به نظر من مشکل در پیدا نشدن TopicID که کلید اونها هستند

[fakhravari]

این روش درسته؟


ALTER PROCEDURE [dbo].[CastumPaging]
(
@TableName nvarchar(20),
@PageIndex int,
@PageSize int
)
AS
Declare @SQL Nvarchar(max);


Declare @RowNumStart int = ((@PageIndex - 1) * (@PageSize + 1))
Declare @RowNumFinish int = (@PageIndex * @PageSize)


set @SQL ='with T1 as
(
select row_number()over
(order by TopicID) as Rng,* from B_Topics
)
select
(SELECT ISNULL(count(*), 0)
FROM dbo.B_Topics INNER JOIN
dbo.RatingPost ON RatingPost.PostID = T1.TopicID
WHERE (dbo.RatingPost.PostID = T1.TopicID))AS Rating_Of_count
,
(SELECT ISNULL(count(*), 0)
FROM dbo.B_Topics INNER JOIN
dbo.Comment_Post ON dbo.Comment_Post.TopikID = T1.TopicID
WHERE (dbo.Comment_Post.TopikID = T1.TopicID))AS Comment_Of_count
,
(SELECT ISNULL(SUM(dbo.RatingPost.Rating), 0)
FROM dbo.B_Topics INNER JOIN
dbo.RatingPost ON RatingPost.PostID = T1.TopicID
WHERE (dbo.RatingPost.PostID = T1.TopicID))AS SumOfRating
, *
from T1 WHERE Rng BETWEEN '+ cast(@RowNumStart as Nvarchar) +' and '+ cast(@RowNumFinish as Nvarchar)

exec sp_executesql @SQL

[fakhravari]

این کد بهتر شد
with T1 as(select row_number()over(order by TopicID) as Rng,* from B_Topics)
select
(SELECT ISNULL(count(*), 0)FROM RatingPost
where PostID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS Rating_Of_count
,
(SELECT ISNULL(count(*), 0)FROM Comment_Post
where TopikID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS Comment_Of_count
,
(SELECT ISNULL(SUM(RatingPost.Rating), 0)FROM RatingPost
where PostID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS SumOfRating
, * from T1 WHERE Rng BETWEEN 20 and 30 order by TopicID

[محمد سلیم آبادی]

این کد بهتر شد
with T1 as(select row_number()over(order by TopicID) as Rng,* from B_Topics)
select
(SELECT ISNULL(count(*), 0)FROM RatingPost
where PostID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS Rating_Of_count
,
(SELECT ISNULL(count(*), 0)FROM Comment_Post
where TopikID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS Comment_Of_count
,
(SELECT ISNULL(SUM(RatingPost.Rating), 0)FROM RatingPost
where PostID in(select TopicID from B_Topics where TopicID = T1.TopicID))AS SumOfRating
, * from T1 WHERE Rng BETWEEN 20 and 30 order by TopicID



ستون TopicID در جدول B_Topics منحصر بفرد هست(کلید)؟
اگه بله خب من query تون رو ساده تر کردم:
WITH T1 AS
(
select row_number() over(order by TopicID) as Rng,*
from B_Topics
)
select COALESCE(S.cnt, 0) AS Rating_Of_count,
(SELECT ISNULL(count(*), 0)
FROM Comment_Post
where TopikID = T1.TopicID)AS Comment_Of_count,
COALESCE(sm, 0) AS SumOfRating,
*
from
(
select * from T1
WHERE Rng BETWEEN 20 and 30
) T1
CROSS APPLY (SELECT SUM(RatingPost.Rating) AS sm, COUNT(*) AS cnt
FROM RatingPost
WHERE PostID = T1.TopicID) AS S
order by TopicID

[fakhravari]

ممنون از فعالیت خوبتون داش سلم ابادی.
خوب سادش کردین که هیچیش نفهمیدم :لبخند:

[محمد سلیم آبادی]

من فقط قسمت های اضافه query اتان را حذف نمودم و تعداد subquery های موجود در ماده select را توسط cross apply کم کردم.
بطور مثال گزاره زیر با دستور TopicID = برابر است:
in(select TopicID from B_Topics where TopicID = T1.TopicID)