SQL语句如下:SELECT Orders.OID, Book.BName, Book.BAuthor, BookPrice.PPrice * BookPrice.PDiscount * 0.1 AS Expr1, Orders.OQuantity, BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity AS Expr2,sum(BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity) AS ordersum FROM Book,bookprice,orders WHERE orders.obid=book.bid AND orders.obid=bookprice.ppid上面的Orders是定单表,Book是书目信息表,BookPrice是书价信息表,列名PDiscount是折扣率。OQuantity是定单中的书的本数!
我现在想返回 定单号 书名 作者 现价 数量 总价(每本书原价*折扣*本数) 和一个总计(也就是那个Sum() 函数了) 但是编译器却报错了!但是我不加Sum()函数的时候也能正常运行!何解?
谢谢!!!
我现在想返回 定单号 书名 作者 现价 数量 总价(每本书原价*折扣*本数) 和一个总计(也就是那个Sum() 函数了) 但是编译器却报错了!但是我不加Sum()函数的时候也能正常运行!何解?
谢谢!!!
GROUP BY Orders.OID, Book.BName, Book.BAuthor, BookPrice.PPrice * BookPrice.PDiscount * 0.1 , Orders.OQuantity, BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity
GROUP BY Orders.OID, Book.BName, Book.BAuthor, BookPrice.PPrice * BookPrice.PDiscount * 0.1 , Orders.OQuantity, BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity
WITH ROLLUP
或:
select sum(BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity) AS ordersum FROM Book,bookprice,orders WHERE orders.obid=book.bid AND orders.obid=bookprice.ppid
FROM bookprice INNER JOIN Orders ON bookprice.ppid=orders.obid
INNER JOIN books ON Orders.obid=book.bid
WHERE Order.OID=要查询的定单ID具体说不知道你表与表间是什么关系,根本没法写,按正常的业务逻辑,差不多是上面的那样写,
如果要列出全部定单的合计
那么用:
SELECT Orders.OID,sum(BookPrice.PPrice * BookPrice.PDiscount * 0.1 * Orders.OQuantity) AS ordersum
FROM bookprice INNER JOIN Orders ON bookprice.ppid=orders.obid
INNER JOIN books ON Orders.obid=book.bid
GROUP BY Orders.OID