Postgres SQL：如何在此 SQL 中执行 GROUP BY

Question

我们有以下 SQL，可以从审计表 family_audit 和 family_address_audit 生成报告。

(SELECT fam.familyserialno, fam.familyname, addr.housenumber, addr.address1,
        COALESCE(fam.operation, addr.operation) op,
        COALESCE(fam.username, addr.username) usr,
        COALESCE(fam.adddate, addr.adddate) add_date,
        (SELECT r.userrolename 
           FROM userrole_master r, user_master u 
          WHERE u.loginid=COALESCE(fam.username, addr.username) AND r.userrole_id=u.userrole_id) rolename
  FROM family_audit fam
       INNER JOIN family_address_audit addr USING(txid, family_id)
WHERE (fam.node_id = 8) AND (fam.familyserialno >=1 and fam.familyserialno <=5)
)
UNION
(SELECT fam.familyserialno, fam.familyname,
        addr.housenumber, addr.address1,
        COALESCE(fam.operation, addr.operation) op,
        COALESCE(fam.username, addr.username) usr,
        COALESCE(fam.adddate, addr.adddate) add_date,
        (SELECT r.userrolename 
           FROM userrole_master r, user_master u 
          WHERE u.loginid=COALESCE(fam.username, addr.username) AND r.userrole_id=u.userrole_id) rolename
   FROM family_audit fam
        FULL OUTER JOIN family_address_audit addr USING(txid, family_id)
  WHERE ((fam.familyserialno IS NULL and addr.family_id in 
             (select family_id 
                from family 
               where familyserialno >=1 and familyserialno <=5 and node_id =8)) 
         OR ((addr.family_id IS NULL) and ((fam.familyserialno >=1 and fam.familyserialno <=5) and fam.node_id=8) ) )
)
ORDER BY add_date DESC;

“上述”SQL 的典型输出如下

 familyserialno | familyname | housenumber |  address1  | op |  usr  |        add_date         | rolename
----------------+------------+-------------+------------+----+-------+-------------------------+----------
              3 | thirda     |             |            | U  | admin | 2010-11-24 15:29:34.312 | admin
                |            | 34324       | sdfdsfdsf  | U  | admin | 2010-11-24 15:28:42.314 | admin
              3 | third      |             |            | I  | admin | 2010-11-24 15:28:34.576 | admin
                |            | 333         | sdfdsf     | U  | admin | 2010-11-24 15:18:57.129 | admin
              2 | aSecond    |             |            | I  | admin | 2010-11-24 14:58:15.477 | admin
              1 | anjanFirst | anjanFirst  | anjanFirst | I  | admin | 2010-11-24 14:12:35.477 | admin

========================

我想按 fam.familyserialno 对输出进行分组。所以，我将上面的SQL修改如下；但是，这会导致错误（最后）

SELECT * from 
((SELECT fam.familyserialno, fam.familyname, addr.housenumber, addr.address1,
        COALESCE(fam.operation, addr.operation) op,
        COALESCE(fam.username, addr.username) usr,
        COALESCE(fam.adddate, addr.adddate) add_date,
        (SELECT r.userrolename 
           FROM userrole_master r, user_master u 
          WHERE u.loginid=COALESCE(fam.username, addr.username) AND r.userrole_id=u.userrole_id) rolename
  FROM family_audit fam
       INNER JOIN family_address_audit addr USING(txid, family_id)
WHERE (fam.node_id = 8) AND (fam.familyserialno >=1 and fam.familyserialno <=5)
)
UNION
(SELECT fam.familyserialno, fam.familyname,
        addr.housenumber, addr.address1,
        COALESCE(fam.operation, addr.operation) op,
        COALESCE(fam.username, addr.username) usr,
        COALESCE(fam.adddate, addr.adddate) add_date,
        (SELECT r.userrolename 
           FROM userrole_master r, user_master u 
          WHERE u.loginid=COALESCE(fam.username, addr.username) AND r.userrole_id=u.userrole_id) rolename
   FROM family_audit fam
        FULL OUTER JOIN family_address_audit addr USING(txid, family_id)
  WHERE ((fam.familyserialno IS NULL and addr.family_id in 
             (select family_id 
                from family 
               where familyserialno >=1 and familyserialno <=5 and node_id =8)) 
         OR ((addr.family_id IS NULL) and ((fam.familyserialno >=1 and fam.familyserialno <=5) and fam.node_id=8) ) )
))
GROUP BY 1 
ORDER BY 7 DESC;

错误：FROM 中的子查询必须有别名 第 2 行：((选择 fam.familyserialno、fam.familyname、addr.housenumbe... ^ 提示：例如，FROM (SELECT ...) [AS] foo。

如何解决此问题？我们确实需要按 fam.familyserialno 对所有行进行分组。

非常感谢，

PS：我们使用的是 Postgres 8.4.4。

Answer 1

您需要为子查询指定别名，如下所示：

 SELECT * FROM ((SELECT ...) UNION (SELECT ...)) AS foo GROUP BY 1 ORDER BY 7 DESC;

这里 foo 是子查询的别名。