抱歉,如果您已经问过这个问题。我想不出要做什么的字眼,但在询问之前我已经做了一些Google foo。
在下面的代码中,我尝试将UNION集成到select查询的Summary行中。在摘要行的第一列中,我需要文本摘要。我很确定我以前曾经这样做过,但是对于我的一生来说,它无法正常工作,因为postgres只是认为这是一个无效的int。
任何帮助将不胜感激。
CREATE OR REPLACE VIEW OrderHistory
AS SELECT OrderLine.ShopOrderID, Book.Title, OrderLine.UnitSellingPrice, OrderLine.Quantity,
SUM(Quantity*UnitSellingPrice) AS total
FROM OrderLine
INNER JOIN Book
ON OrderLine.BookID = Book.BookID
WHERE OrderLine.BookID = 2
GROUP BY OrderLine.ShopOrderID, Book.Title, OrderLine.Quantity, OrderLine.UnitSellingPrice;
SELECT * FROM OrderHistory
UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice)
FROM OrderHistory
ORDER BY total;
如果这段代码很糟糕,我很抱歉,因为我现在正在尝试学习一些SQL noob,因此可能会有一百万种方法可以更有效地做到这一点。
编辑:对不起。我可能还应该指出这是我遇到的麻烦:)
UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice)
试试这个:
CREATE OR REPLACE VIEW OrderHistory
AS SELECT cast(OrderLine.ShopOrderID as varchar(255)) as ShopOrderId, Book.Title, OrderLine.UnitSellingPrice, OrderLine.Quantity,
SUM(Quantity*UnitSellingPrice) AS total
FROM OrderLine
INNER JOIN Book
ON OrderLine.BookID = Book.BookID
WHERE OrderLine.BookID = 2
GROUP BY OrderLine.ShopOrderID, Book.Title, OrderLine.Quantity, OrderLine.UnitSellingPrice;
SELECT * FROM OrderHistory
UNION SELECT 'Summary', NULL, NULL, SUM(Quantity), SUM(Quantity*UnitSellingPrice)
FROM OrderHistory
ORDER BY total;
也就是说,将ShopOrderId
转换为字符串。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句