除了我们熟知的distinct外,postgresql还支持distinct on,它的用途跟mysql没有启用ONLY_FULL_GROUP_BY SQL选项是一样的,针对声明的字段做分组,分组外的字段返回随机一行。如下:
lightdb@oradb=# create table t_distincton(id int,v text); CREATE TABLE lightdb@oradb=# lightdb@oradb=# lightdb@oradb=# insert into t_distincton values (1,'v1'),(2,'v2'),(3,'v3'); INSERT 0 3 lightdb@oradb=# insert into t_distincton values (1,'v1'),(2,'v2'),(3,'v4'); INSERT 0 3 lightdb@oradb=# select * from t_distincton ; id | v ----+---- 1 | v1 2 | v2 3 | v3 1 | v1 2 | v2 3 | v4 (6 rows) lightdb@oradb=# select distinct on(id) v from t_distincton ; v ---- v1 v2 v3 (3 rows) lightdb@oradb=# select distinct on(v) id,v from t_distincton ; id | v ----+---- 1 | v1 2 | v2 3 | v3 3 | v4 (4 rows) lightdb@oradb=# select distinct on(v) id,v from t_distincton order by id; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on(v) id,v from t_distincton order by id; ^ lightdb@oradb=# select distinct on(v) id,v from t_distincton order by v,id; id | v ----+---- 1 | v1 2 | v2 3 | v3 3 | v4 (4 rows) lightdb@oradb=# select distinct on(v,id) id,v from t_distincton order by v; id | v ----+---- 1 | v1 2 | v2 3 | v3 3 | v4 (4 rows)
如果不使用distinct on,也可以通过分析函数row_number()达到相同的效果,如下:
select
*
from
(
select
row_number() over (partition
by
a)
as
rn, *
from
t_distinct) t
where
rn=1;
需要注意的是,如果有distinct on(a,b,c)的值有null,null被认为都相同,这个null != null不一样。
标签:postgresql,lightdb,distinct,distincton,用法,oradb,id,select From: https://www.cnblogs.com/lightdb/p/17955892