当行分页时如何检索列总计?
我在表中有一个“价格”列,我使用分页脚本来生成显示的数据。现在分页工作正常,但我试图在 HTML 表格中添加最后一行来显示所有价格的总和。因此,我编写了一个脚本来使用 foreach 循环来做到这一点,它确实可以为我提供所有价格总和的总和,但它是所有行的总和,甚至是后续页面上的行的总和。如何仅检索分页中显示的行的总和?
这是查询..
SELECT
purchase_log.id,
purchase_log.date_purchased,
purchase_log.total_cost,
purchase_log.payment_status,
cart_contents.product_name,
members.first_name, members.last_name,
members.email FROM purchase_log LEFT
JOIN cart_contents ON purchase_log.id
= cart_contents.purchase_id LEFT JOIN members ON purchase_log.member_id =
members.id GROUP BY `id` ORDER BY `id` DESC LIMIT 0,30";
I have a column "price" in a table and I used a pagination script to generate the data displayed. Now the pagination is working perfectly however I am trying to have a final row in my HTML table to show the total of all the price. So I wrote a script to do just that with a foreach loop and it sort of works where it does give me the total of all the price summed up together however it is the sum of all the rows, even the ones that are on following pages. How can I retrieve just the sum of the rows displayed within the pagination?
Here is the query..
SELECT
purchase_log.id,
purchase_log.date_purchased,
purchase_log.total_cost,
purchase_log.payment_status,
cart_contents.product_name,
members.first_name, members.last_name,
members.email FROM purchase_log LEFT
JOIN cart_contents ON purchase_log.id
= cart_contents.purchase_id LEFT JOIN members ON purchase_log.member_id =
members.id GROUP BY `id` ORDER BY `id` DESC LIMIT 0,30";
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
只需使用MySQL函数FOUND_ROWS():http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
Just use MySQL function FOUND_ROWS(): http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
您需要将 LIMIT 0,20 附加到 mysql 查询,这只会对当前分页显示的结果进行求和。
我假设您使用 LIMIT 对结果进行分页,除非您将数据排列到网格中,在这种情况下您需要发布代码。
You need to append LIMIT 0,20 to a mysql query this will only sum total the current pageinated displayed results.
I assume you use a LIMIT to pageinate the results, unless you array the data into a grid in which case you'll need to post code.
在 mySQL 查询中使用 mysql SUM 函数怎么样?
http://dev.mysql.com/doc /refman/5.0/en/group-by-functions.html#function_sum
What about using the mysql SUM function in your mySQL query?
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum
首先,您需要将总计初始化为零,以便它可以显示您正在获取的限制记录的总和。
希望这有帮助。
First you need to initialize total to zero so that it can display the sum of the records that you are fetching with limit.
Hope this helps.