sql - Postgresql SELECT DISTINCT ON

Question

在下面的查询中，我试图选择数据，但需要它在 Book.Title 和 OrderDate 列上是不同的。我试过使用 DISTINCT ON 但也得到了多个结果，我也将在下面发布。对不起，如果这个问题有点微不足道。我目前正在学习 SQL，所以当涉及到我可能错过的非常明显的东西时，我有点菜鸟。

我如何更改查询以使结果格式如下:

   month   |                    title                    | quantity | total_value 
-----------+---------------------------------------------+----------+-------------
 February  | Internet and World Wide Web: How to Program |       15 |      899.70
 March     | C How To Program                            |        4 |      183.92
 March     | Core Servlets and JavaServer Pages          |       13 |      856.70
 March     | Internet and World Wide Web: How to Program |       21 |     1071.58

我构建的查询:

SELECT DISTINCT ON (OrderDate, Book.Title) Book.Title, to_char(OrderDate, 'Month') AS "Order Date", 
OrderLine.Quantity AS "Order Quantity", (SUM(Quantity*UnitSellingPrice)) AS "Total Value"
FROM Book
INNER JOIN Publisher
ON Book.PublisherID=Publisher.PublisherID
INNER JOIN OrderLine
ON Book.BookID=OrderLine.BookID
INNER JOIN ShopOrder
ON OrderLine.ShopOrderID=ShopOrder.ShopOrderID
WHERE Publisher.Name='Prentice Hall'
GROUP BY book.title, OrderDate, orderline.quantity
ORDER BY OrderDate ASC;

我得到的结果:

                    title                    | Order Date | Order Quantity | Total Value 
---------------------------------------------+------------+----------------+-------------
 Internet and World Wide Web: How to Program | February   |             10 |      299.90
 Internet and World Wide Web: How to Program | February   |              5 |      149.95
 C How To Program                            | March      |              3 |       68.97
 Core Servlets and JavaServer Pages          | March      |             10 |      329.50
 Internet and World Wide Web: How to Program | March      |             20 |      519.80
 C How To Program                            | March      |              1 |       22.99
 Core Servlets and JavaServer Pages          | March      |              3 |       98.85
 Internet and World Wide Web: How to Program | March      |              1 |       15.99

非常感谢您的帮助!

Answer 1

我认为你在这里把事情复杂化了:如果你想要每个标题和月份的总数，那么你想要分组标题和月份，然后总和 数量。您不想希望根据数量本身分组，因为这意味着每个不同的数量都有一个新行。

这在标准 SQL 中很容易做到(DISTINCT ON 是 Postgres 扩展，在一些情况下代替GROUP BY很有用，但是此处不必要):

SELECT
    Book.Title,
    to_char(OrderDate, 'Month') AS "Order Date", 
    SUM(OrderLine.Quantity) AS "Order Quantity", 
    SUM(Quantity*UnitSellingPrice) AS "Total Value"
FROM [...]
WHERE Publisher.Name='Prentice Hall'
GROUP BY
    book.title,
    to_char(OrderDate, 'Month')
ORDER BY to_char(OrderDate, 'Month') ASC;

根据经验，SELECT 子句中的所有内容 - 以及被认为“处于同一级别”的 ORDER BY 子句 - 应该是:

• 在 GROUP BY 子句中列出，因为每次变化时您都需要一个新行
• 或包裹在聚合函数中(此处为 SUM())，因为您希望根据某些规则“汇总”。