PostgreSQL查询报错：子查询需别名，求3列统计结果的正确写法

错误原因分析 #

你遇到的ERROR: subquery in FROM must have an alias错误，直接原因是FROM子句内的子查询必须指定别名，同时原查询还有其他语法问题：

• 字段名"Name缺少闭合双引号，语法不合法
• 内层DISTINCT查询末尾多了逗号，触发语法报错
• 错误地将两个统计子查询用逗号拼接在FROM中，还额外引入了无关表npid_it，完全没必要

修正后的查询语句 #

方案一：子查询方式（兼容所有PostgreSQL版本） #

SELECT
  (SELECT COUNT(*) FROM (SELECT DISTINCT "Name", "Location", "Interactivity" FROM idtable) AS total_inv) AS "Total_Inventory",
  (SELECT COUNT(*) FROM (SELECT DISTINCT "Name", "Location", "Interactivity" FROM idtable WHERE "Active" = 'true') AS total_act) AS "Active_IDs",
  'test ids' AS "ID Type";

方案二：简化高效写法（PostgreSQL 9.4+ 支持） #

利用PostgreSQL支持多列DISTINCT的特性，仅扫描一次表，效率更高：

SELECT
  COUNT(DISTINCT ("Name", "Location", "Interactivity")) AS "Total_Inventory",
  COUNT(DISTINCT ("Name", "Location", "Interactivity")) FILTER (WHERE "Active" = 'true') AS "Active_IDs",
  'test ids' AS "ID Type"
FROM idtable;

说明 #

两种方案都会返回你需要的三列：多列去重后的总ID数量、Active值为true的去重ID数量、固定标识test ids。

内容的提问来源于stack exchange，提问作者Patrick Teng