一个网上书店系统,有以下三个表
(books)书本表格 book_id(书本id),book_name(书名),..........
(comments)书本评论表 comment_id(评论id),user_id(用户id),book_id(书本id),comment_content(评论内容),.............
(orderdetails)订单明细表 orderdetail_id(订单明细id),order_id(订单id),book_id(书本id),orderdetial_quantity(数量),orderdetail(价格),......... //===========================我想用一个下查询查询出每本书的评论次数,和销售数量,如下
SELECT b.book_id, b.book_name,
SUM(od.orderdetial_quantity) AS book_sales_count --销售数量
,COUNT(c.comment_id) AS book_comment_count --评论次数
FROM dbo.books b LEFT OUTER JOIN
dbo.comments c ON b.book_id = c.book_id LEFT OUTER JOIN
orderdetails od ON b.book_id = od.book_id
GROUP BY b.book_id, b.book_name
但是有误,发现销售数量有错,
但是我分开查询
如 books 和 comments 接连查评论次数 对了
books 和 orderdetails 连接查销售数量对了
但是三个表连起来就是不对,请问大家,我错在那里~~??如果想一次查询出来,语句怎么写,谢谢拉~
(books)书本表格 book_id(书本id),book_name(书名),..........
(comments)书本评论表 comment_id(评论id),user_id(用户id),book_id(书本id),comment_content(评论内容),.............
(orderdetails)订单明细表 orderdetail_id(订单明细id),order_id(订单id),book_id(书本id),orderdetial_quantity(数量),orderdetail(价格),......... //===========================我想用一个下查询查询出每本书的评论次数,和销售数量,如下
SELECT b.book_id, b.book_name,
SUM(od.orderdetial_quantity) AS book_sales_count --销售数量
,COUNT(c.comment_id) AS book_comment_count --评论次数
FROM dbo.books b LEFT OUTER JOIN
dbo.comments c ON b.book_id = c.book_id LEFT OUTER JOIN
orderdetails od ON b.book_id = od.book_id
GROUP BY b.book_id, b.book_name
但是有误,发现销售数量有错,
但是我分开查询
如 books 和 comments 接连查评论次数 对了
books 和 orderdetails 连接查销售数量对了
但是三个表连起来就是不对,请问大家,我错在那里~~??如果想一次查询出来,语句怎么写,谢谢拉~
你这个不算菜鸟问题了
不用left,用inner试试
我Copy回家给你做
可否?
类似写法可可查询出
好的那就有劳你拉
(select count(*) from comments where book_id=b.book_id) as book_comment_count,
(select sum(orderdetial_quantity) from orderdetails where book_id=b.book_id) as book_sales_count
from books b这个是可以的
FROM Books B
LEFT JOIN Comments C ON C.Book_ID=B.Book_ID
LEFT JOIN (
SELECT Book_ID,Sum(OrderDetial_Quantity) AS Quantity FROM OrderDetials GROUP BY Book_ID
) T ON T.Book_ID=B.Book_ID
GROUP BY B.Book_ID,B.Book_Name,T.Quantity
ORDER BY B.Book_ID