ROW_NUMBER()返回值不一致

发布于 2024-11-10 04:21:49 字数 1283 浏览 5 评论 0 原文

我在 ASP.NET 中使用 ROW_NUM() 函数进行分页，

SELECT row_num, expense_id,email, reason, amount,date,category_name,is_income
FROM
  (
  SELECT e.expense_id,e.email, e.reason, e.amount,e.date,c.category_name,e.is_income, 
  ROW_NUMBER() OVER(ORDER BY e.date DESC,e.expense_id) as row_num
  FROM Expense e
  JOIN Category c ON e.category_id = c.category_id
  WHERE e.date >='5-1-2011' AND e.date<='5-31-2011'
  ) as ExpenseInfo
WHERE email='[email protected]'

但它返回了不一致的 row_num 列表（例如 1,3,4... 或 2,3,4...）。我该如何解决这个问题？

提前致谢。

我已经解决了我的问题

SELECT RowNum, expense_id, email, reason, amount, date, category_name, is_income
FROM
(
SELECT e.expense_id, e.email, e.reason, e.amount, e.date, c.category_name, e.is_income, Row_Number() OVER(ORDER BY date DESC) as row_num 
FROM Expense e JOIN Category c 
  ON e.category_id = c.category_id
WHERE e.date >='5-1-2011' AND e.date<='5-31-2011' AND e.email='[email protected]'
) AS ExpenseInfo
WHERE row_num>=1 and row_num<=20

原文

I used ROW_NUM() function for paging in ASP.NET

SELECT row_num, expense_id,email, reason, amount,date,category_name,is_income
FROM
  (
  SELECT e.expense_id,e.email, e.reason, e.amount,e.date,c.category_name,e.is_income, 
  ROW_NUMBER() OVER(ORDER BY e.date DESC,e.expense_id) as row_num
  FROM Expense e
  JOIN Category c ON e.category_id = c.category_id
  WHERE e.date >='5-1-2011' AND e.date<='5-31-2011'
  ) as ExpenseInfo
WHERE email='[email protected]'

But it returned an inconsistent list of row_num (such as 1,3,4... or 2,3,4...). How can I solve this problem?

Thanks in advance.

I have solved my problem

SELECT RowNum, expense_id, email, reason, amount, date, category_name, is_income
FROM
(
SELECT e.expense_id, e.email, e.reason, e.amount, e.date, c.category_name, e.is_income, Row_Number() OVER(ORDER BY date DESC) as row_num 
FROM Expense e JOIN Category c 
  ON e.category_id = c.category_id
WHERE e.date >='5-1-2011' AND e.date<='5-31-2011' AND e.email='[email protected]'
) AS ExpenseInfo
WHERE row_num>=1 and row_num<=20

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仄言 2024-11-17 04:21:49

在主查询中添加 ROW_NUMBER。当您将其添加到子查询中时，首先生成数字，然后过滤记录。当您生成最终结果的行号时，就不会遇到这个问题。

select
  s.*,
  row_number() over(order by s.date desc, s.expense_id) as row_num
from
  (
   /*YourQueryWithAllItsConditions*/
  ) s

Add the ROW_NUMBER in the main query. When you add it in the subquery, first the numbers are generated, and afterwards you're filtering the records. When you generate the row numbers on the final result, you won't have that problem.

select
  s.*,
  row_number() over(order by s.date desc, s.expense_id) as row_num
from
  (
   /*YourQueryWithAllItsConditions*/
  ) s

回复收藏 0 原文

烟酒忠诚 2024-11-17 04:21:49

Where email = '[电子邮件受保护]' 正在过滤掉某些行。对于您在这里所做的事情，您不需要“外部”查询。只需将它们全部包裹在一起即可。

 SELECT ROW_NUMBER() OVER(ORDER BY e.date DESC, e.expense_id) as row_num
       , e.expense_id
       , e.email
       , e.reason
       , e.amount
       , e.date
       , c.category_name
       , e.is_income
  FROM Expense e
  JOIN Category c 
    ON e.category_id = c.category_id
  WHERE e.date >='5-1-2011' 
    AND e.date <='5-31-2011'
    AND e.email = '[email protected]'

The Where email = '[email protected]' is filtering out certain rows. For what you are doing here, you don't need the "outer" query. Just wrap it all together.

 SELECT ROW_NUMBER() OVER(ORDER BY e.date DESC, e.expense_id) as row_num
       , e.expense_id
       , e.email
       , e.reason
       , e.amount
       , e.date
       , c.category_name
       , e.is_income
  FROM Expense e
  JOIN Category c 
    ON e.category_id = c.category_id
  WHERE e.date >='5-1-2011' 
    AND e.date <='5-31-2011'
    AND e.email = '[email protected]'

回复收藏 0 原文

~没有更多了~