postgresql Postgres：GROUP BY 几列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18891131/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: GROUP BY several column
提问by Cherche
I have two table in this example.
在这个例子中我有两个表。
( example column name )
（示例列名）
First is the product
首先是产品
product_id | product_text
Second table is Price.
第二个表是价格。
price_productid | price_datestart | price_price
Let's just say I have multiple datestart with the same product. How can I get the actual price ?
假设我有多个相同产品的日期开始。我怎样才能得到实际价格？
If I use GROUP BY in Postgres, with all the selected column, 2 row may come for the same product. Because the column price_datestart is different.
如果我在 Postgres 中使用 GROUP BY，对于所有选定的列，同一产品可能会有 2 行。因为列 price_datestart 不同。
Example :
例子 ：
product_id : 1
product_text : "Apple Iphone"
price_productid : 1
price_datestart :"2013-10-01"
price_price :"99"
price_productid : 1
price_datestart :"2013-12-01"
price_price :"75"
If I try this :
如果我尝试这个：
SELECT price_productid,price_datestart,price_price,product_text,product_id
WHERE price_datestart > now()
GROUP BY price_productid,price_datestart,price_price,product_text,product_id
ORDER BY price_datestart ASC
It will give me a result, but two rows and I need one.
它会给我一个结果，但有两行，我需要一个。
采纳答案by Roman Pekar
Use distinct onsyntax. If you want current price:
使用distinct on语法。如果您想要当前价格：
select distinct on (p.productid)
p.productid, pr.product_text, p.price, p.datestart
from Price as p
left outer join Product as pr on pr.productid = p.productid
where p.datestart <= now()
order by p.productid, p.datestart desc
回答by jgm
You have a few problems, but GROUP BY is not one of them.
您有一些问题，但 GROUP BY 不是其中之一。
First, although you have a datestart you don't have a dateend. I'd change datestart to be a daterange, for example:
首先，虽然你有一个日期开始，但你没有一个日期结束。我将 datestart 更改为 daterange，例如：
CREATE TABLE product
(
product_id int
,product_text text
);
CREATE TABLE price
(
price_productid int
,price_daterange TSRANGE
,price_price NUMERIC(10,2)
);
The TSRANGEallows you to set up validity of your price over a given range, for example:
将TSRANGE允许你设置你的价格的有效性在给定的范围，例如：
INSERT INTO product VALUES(1, 'phone');
INSERT INTO price VALUES(1, '[2013-08-01 00:00:00,2013-10-01 00:00:00)', 199);
INSERT INTO price VALUES(1, '[2013-10-01 00:00:00,2013-12-01 00:00:00)', 99);
INSERT INTO price VALUES(1, '[2013-12-01 00:00:00,)', 75);
And that makes your SELECTmuch more simple, for example:
这使您SELECT的操作更加简单，例如：
SELECT price_productid,price_daterange,price_price,product_text,product_id
FROM product, price
WHERE price_daterange @> now()::timestamp
AND product_id = price_productid
This also has the benefit of allowing you to query for any arbitrary time by swapping out now()for another date.
这还有一个好处是允许您通过换出now()另一个日期来查询任意时间。
You should read up on ranges in PostgresQL as they are very powerful. The example above is not complete in that it should also have indices on price_daterangeto ensure that you do not have overlaps for any product.
您应该阅读 PostgresQL 中的范围，因为它们非常强大。上面的示例并不完整，因为它还应该有索引price_daterange以确保您没有任何产品的重叠。
