PostgreSQL数据库中DISTINCT关键字的4种⽤法
⽂章⽬录
⼤家好，我是只谈技术不剪发的 Tony ⽼师。PostgreSQL 不但⾼度兼容 SQL 标准，同时还对很多语法进⾏了扩展，可以⽤于实现⼀些特殊的功能。今天我们就来介绍⼀下 PostgreSQL 数据库中 DISTINCT 关键字的 4 种不同⽤法。
本⽂⽰例数据，如果觉得⽂章有⽤，欢迎评论 、点赞 、推荐
DISTINCT
按照 SQL 标准，SELECT DISTINCT可以在返回查询结果之前去除重复的记录，每个重复的数据组中只保留⼀条记录。例如：
SELECT DISTINCT dept_id, sex
FROM employee;
dept_id|sex|
-
------|---|
4|男|
1|男|
4|⼥|
5|男|
3|⼥|
2|男|
以上语句中的 DISTINCT 表⽰返回不同部门 id 和性别的组合值。我们也可以使⽤ GROUP BY 实现相同的结果：
SELECT dept_id, sex
FROM employee
GROUP BY dept_id, sex;
按照 SQL 标准，多个 NULL 值对于 DISTINCT ⽽⾔属于相同的分组。
DISTINCT ON
考虑⼀个问题：每个部门中⽉薪最⾼的员⼯都是谁？这个问题可以使⽤多种实现⽅法：
SELECT dept_id, emp_name,salary
FROM employee
WHERE(dept_id, salary)IN(SELECT dept_id,MAX(salary)
FROM employee
GROUP BY dept_id );
dept_id|emp_name|salary |
-------|--------|--------|
1|刘备|30000.00|
2|诸葛亮|24000.00|
3|孙尚⾹|12000.00|
4|赵云|15000.00|
5|法正|10000.00|
-- 窗⼝函数
WITH ranked_employee AS(select distinct from
SELECT dept_id, emp_name, salary, ROW_NUMBER()OVER(PARTITION BY dept_id ORDER BY salary DESC) rn
FROM employee
)
SELECT*
FROM ranked_employee
WHERE rn =1;
dept_id|emp_name|salary |rn|
-------|--------|--------|--|
1|刘备|30000.00|1|
2|诸葛亮|24000.00|1|
3|孙尚⾹|12000.00|1|
4|赵云|15000.00|1|
5|法正|10000.00|1|
其中，第⼀个语句使⽤了；第⼆个语句使⽤了，除了 ROW_NUMBER 之外，也可以使⽤ RANK 或者 DENSE_RANK 等函数。这两者都是SQL 标准实现。
除此之外，PostgreSQL 提供了扩展的 ⼦句，可以更加⽅便地实现以上结果：
SELECT DISTINCT ON(dept_id) dept_id, emp_name, salary
FROM employee
ORDER BY dept_id, salary DESC;
dept_id|emp_name|salary |rn|
-------|--------|--------|--|
1|刘备|30000.00|1|
2|诸葛亮|24000.00|1|
3|孙尚⾹|12000.00|1|
4|赵云|15000.00|1|
5|法正|10000.00|1|
其中，DISTINCT ON (dept_id) 表⽰部门 id 相同的数据组，返回其中的第⼀条记录；ORDER BY ⼦句确保了返回的是每个部分中⽉薪最⾼的记录。DISTINCT ON 中的字段或表达式（可能多个）必须和 ORDER BY 最左侧的⼏个字段或表达式相同。
IS DISTINCT FROM
空值（NULL）是数据库中的⼀个特殊值，通常⽤于表⽰缺失值或者不适⽤的值。空值的⽐较是⼀个⽐较容易出错的问题。例如：
UNION ALL
SELECT NULL,1
UNION ALL
SELECT NULL,NULL)
SELECT a, b, a = b "a=b"
FROM t;
a |
b |a=b |
----|----|-----|
1|1|true|
1|2|false|
NULL|1|NULL|
NULL|NULL|NULL|
当我们使⽤⽐较运算符（=、<>、<、> 等）与 NULL 进⾏⽐较时，结果既不是真也不是假，⽽是未知；因为 NULL 表⽰未知，也就意味着可能是任何值；我们不能说两个未知的值相同，也不能说它们不相同。
为了⽐较 NULL 值，SQL 定义了两个专⽤的运算符：IS NULL和IS NOT NULL。例如：
SELECT1IS NULL"1 IS NULL",
1IS NOT NULL"1 IS NOT NULL",
NULL IS NULL"NULL IS NULL",
NULL IS NOT NULL"NULL IS NOT NULL";
1IS NULL|1IS NOT NULL|NULL IS NULL|NULL IS NOT NULL|
---------|-------------|------------|----------------|
false|true|true|false|
因此，对于两个可能为空的字段进⾏⽐较的完整⽅法如下：
WITH t AS(
SELECT1AS a,1AS b
UNION ALL
SELECT1,2
UNION ALL
SELECT NULL,1
UNION ALL
SELECT NULL,NULL)
SELECT a, b,
(a IS NULL AND b IS NULL)
OR
(a IS NOT NULL AND b IS NOT NULL AND a = b)"a=b"
FROM t;
a |
b |a=b |
----|----|-----|
1|1|true|
1|2|false|
NULL|1|false|
NULL|NULL|true|
以上语句返回了我们期望的结果，但是读写都很不⽅便；为此，PostgreSQL 提供了扩展的 运算符，⽀持 NULL 值的⽐较。例如：
UNION ALL
SELECT NULL,1
UNION ALL
SELECT NULL,NULL)
SELECT a, b, a IS NOT DISTINCT FROM b "a=b"
FROM t;
a |
b |a=b |
----|----|-----|
1|1|true|
1|2|false|
NULL|1|false|
NULL|NULL|true|
注意，IS NOT DISTINCT FROM 表⽰判断两个数据是否相同，IS DISTINCT FROM 表⽰判断两个数据是否不同；它们都将 NULL 看作已知的⼀个特殊值，⽽不是 SQL 标准中的未知值。显然这种语法更加⾔简意赅。
另外，PostgreSQL 还提供了⼀个配置变量 ，该参数默认为 off；如果设置为 on，PostgreSQL 会⾃动执⾏ convert x = NULL 到 x IS NULL 的转换。建议不要依赖这个参数的设置，⽽是应该修改应⽤程序
关于 SQL 空值的详细讨论以及它们在 MySQL、Oracle、SQL Server、PostgreSQL、SQLite 中的具体实现，可以参考。
聚合函数与 DISTINCT
（aggregate function）针对⼀组数据⾏进⾏运算，并且返回⼀条结果。PostgreSQL ⽀持的聚合函数包括 AVG、COUNT、
MAX/MIN、SUM、STRING_AGG、ARRAY_AGG 等。例如：
SELECT dept_id,count(*),avg(salary), string_agg(emp_name,','ORDER BY salary DESC)
FROM employee
GROUP BY dept_id
ORDER BY dept_id;
dept_id|count|avg |string_agg |
-------|-----|----------------------|--------------------------------------|
1|3|26666.666666666667|刘备，关⽻，张飞|
2|3|13166.6666666666666667|诸葛亮，黄忠，魏延|
3|2|9000.0000000000000000|孙尚⾹，孙丫鬟|
4|9|7577.7777777777777778|赵云，周仓，关兴，关平，赵⽒，廖化，张苞，赵统，马岱|
5|8|5012.5000000000000000|法正，简雍，孙乾，糜竺，黄权，庞统，邓芝，蒋琬|
以上语句返回了每个部门的员⼯⼈数、平均⽉薪以及所有员⼯姓名的连接字符串（按照⽉薪从⾼到低）。
PostgreSQL 不仅实现了分组聚合操作，还⽀持聚合函数中的 选项，可以在进⾏汇总之前去除每个分组中的重复记录。例如：
SELECT dept_id, string_agg(sex,','), string_agg(DISTINCT sex,',') string_agg_distinct
FROM employee
GROUP BY dept_id
ORDER BY dept_id;
dept_id|string_agg |string_agg_distinct|
-------|-----------------------|-------------------|
1|男，男，男|男|
2|男，男，男|男|
3|⼥,⼥|⼥|
4|男,⼥，男，男，男，男，男，男，男|⼥，男|
5|男，男，男，男，男，男，男，男|男|
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论