如何在 Postgres 中选择几列而不按所有列分组

Question

我正在使用 Postgres 8.4.2，我正在尝试进行一个选择一些数据的查询，但是当按我选择的这几列分组时，我得到了错误的结果。

我

试图不按所有这些列进行分组，但我得到通知，我应该按所有选定的列分组。

我的查询如下所示：

 SELECT c.id AS category_id, c.parent_id, c.title AS category_title, count(ofrs.id) AS offers_count
   FROM categories c
   LEFT JOIN offers_to_categories otc ON c.id = otc.category_id
   LEFT JOIN offers ofrs ON otc.offer_id = ofrs.id
  WHERE ofrs.offer_type = 1 AND ofrs.status = 1
  GROUP BY c.id, c.title, c.parent_id;

我想按类别选择商品计数，其中 offer_type = 1。

如果不按几列分组，而只能按c.id分组，我怎么能做到这一点？

我尝试了以下窗口函数，但结果是相同的 - 它向我显示的结果比应有的更多。

SELECT  ofr.id , c.id AS category_id, c.parent_id, c.title AS category_title,ofr.website_id ,
 count( ofr.id) 
 OVER (PARTITION BY  (ofr.id) order BY ofr.id)
 FROM offers AS ofr
 INNER JOIN offers_to_categories AS ofr_cat ON (ofr_cat.offer_id = ofr.id)
 INNER JOIN categories AS c ON (c.id = ofr_cat.category_id)
 WHERE (c.id = 3 or c.parent_id = 3) and ofr.website_id = 1 and ofr.status = 1

Answer 1

我找到了问题的答案，它是：

SELECT  distinct ON(ofr.id) ofr.id , c.id AS category_id, c.parent_id, c.title AS category_title,ofr.website_id ,
count( ofr.id) 
 OVER (PARTITION BY  (select distinct on(ofr.id) ofr.id from offers as id GROUP BY ofr.id) order BY ofr.id)
FROM offers AS ofr
INNER JOIN offers_to_categories AS ofr_cat ON (ofr_cat.offer_id = ofr.id)
INNER JOIN categories AS c ON (c.id = ofr_cat.category_id)
WHERE (c.id = 3 or c.parent_id = 3) and ofr.website_id = 1 and ofr.status = 1