postgresql distinct on

68bkxrlz 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(311)

我无法定义别名或选择特定列，只能使用通配符：
工作：

SELECT DISTINCT ON (r.InsertDate) *  
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

不工作：

SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Servername 
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

错误

ERROR:  column "lastreport" does not exist
LINE 1:  SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Serve...
                                           ^
SQL state: 42703
Character: 36

也不起作用：

SELECT DISTINCT ON (r.InsertDate) , s.Id, s.Servername 
FROM public.server AS s
LEFT JOIN public.Report AS r ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

ERROR:  column "lastreport" does not exist
LINE 1:  SELECT DISTINCT ON (r.InsertDate) LastReport, s.Id, s.Serve...
                                           ^
SQL state: 42703
Character: 36

ERROR:  syntax error at or near ","
LINE 1:  SELECT DISTINCT ON (r.InsertDate) , s.Id, s.Servername 
                                           ^
SQL state: 42601
Character: 36

你知道吗？

sql postgresql distinct-on

来源：https://stackoverflow.com/questions/62319714/postgresql-distinct-on

1条答案

按热度按时间

disbfnqx1#

你好像不明白 DISTINCT ON . 它不“返回列”。它是一个句法结构。这些是 SELECT :

SELECT ALL
SELECT DISTINCT
SELECT DISTINCT ON ()

（第一个是默认值，在实践中从未使用过。）
列列表遵循这些构造。所以，显然你想要：

SELECT DISTINCT ON (r.InsertDate) r.InsertDate as LastReport, s.Id, s.Servername 
FROM public.server s LEFT JOIN
     public.Report r
     ON s.Id = r.ServerId
ORDER BY r.InsertDate DESC;

事实证明 DISTINCT ON 实际上是在 SELECT 子句，以便可以使用列别名：

SELECT DISTINCT ON (LastReport) r.InsertDate as LastReport, s.Id, s.Servername 
FROM public.server s LEFT JOIN
     public.Report r
     ON s.Id = r.ServerId
ORDER BY LastReport DESC;

但是，别名必须在列列表中的 distinct on .
请注意，您使用的是 LEFT JOIN ，所以 r.InsertDate 可以是 NULLL .

赞(0）回复(0）举报 2021-07-29

我来回答

postgresql distinct on

1条答案

相关问题

热门标签

最新问答