时间戳

首页 > 解决方案 > 有联接时如何从多个表中进行选择？

sql - 有联接时如何从多个表中进行选择？

问题描述

我有一个当前查询，它为我提供了一个用户 ID 和计数表，但我希望该表更具描述性。此查询工作正常：

SELECT a.user_id, count(*) FROM table_a a
JOIN table_b b ON b.user_id = a.user_id
WHERE <some condition>
    AND <some condition>
    AND b.account_created_at < a.account_created_at
GROUP BY a.user_id
ORDER BY count DESC;

但是，我想在输出中添加字段，但在这样做时遇到了麻烦：

SELECT a.user_id, b.organization, b.first_name, b.last_name, b.alias, count(*) FROM table_a a
JOIN table_b b ON b.user_id = a.user_id
WHERE <some condition>
    AND <some condition>
    AND b.account_created_at < a.account_created_at
GROUP BY a.user_id
ORDER BY count DESC;

但我收到一条错误消息b.organization must appear in the GROUP BY clause or be used in an aggregate function。

基本上，作为输出，我想：

organization | first_name | last_name | alias | user_id | id_counts

除了最后两列之外的所有列都来自哪里table_b。

我怎样才能做到这一点？

标签： sqlpostgresql

解决方案

您需要 group by 中不在聚合函数中的列 

SELECT a.user_id, b.organization, b.first_name, b.last_name, b.alias, count(*) 
FROM table_a a
JOIN table_b b ON b.user_id = a.user_id
WHERE <some condition>
    AND <some condition>
    AND b.account_created_at < a.account_created_at
GROUP BY a.user_id, b.organization, b.first_name, b.last_name, b.alias
ORDER BY count DESC;

或为这些列添加聚合函数

SELECT a.user_id, max(b.organization), max(b.first_name), max(b.last_name), max(b.alias), count(*) 
FROM table_a a
JOIN table_b b ON b.user_id = a.user_id
WHERE <some condition>
    AND <some condition>
    AND b.account_created_at < a.account_created_at
GROUP BY a.user_id, b.organization, b.first_name, b.last_name, b.alias
ORDER BY count DESC;

推荐阅读