php mysql 按 View 显示

Question

感谢您的关注。

我有3张 table

图书、book_category、book_click。

书籍表book_id,网址，标题，图像，简短描述，精选,启用

book_category 表book_id, category_id

book_clicks 表site_id、时间、浏览量

我想通过 View SUMS 从 books 表中获取数据，但这只会返回一个结果

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC

Answer 1

使用 ROLLUP 检查 mysql

更新

再次阅读您的问题后，它可能就像添加 GROUP BY books.book_id 一样简单>

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC GROUP BY books.book_id

原答案:

http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html

SELECT  books.book_id, url, title, short_desc, img, featured, SUM(views) as total_views
 FROM  books,book_category,book_click  WHERE  enable=1 AND category_id=7
AND books.book_id=book_category.book_id
ORDER BY  strong texttotal_views DESC GROUP BY your_grouping WITH ROLLUP;

示例

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |