Oracle -通过查询将基本联接查询与计数/组联接

dw1jzc5e  于 2023-02-07  发布在  Oracle
关注(0)|答案(1)|浏览(139)

我有两个查询,一个是基本连接查询,另一个是使用count/having/group by的查询,使用count的查询使用了一个表,这个表也用在基本连接中,所以我想我可以添加另一个连接或某种子查询。
我要做的是将另一个表中的一个或多个列添加到查询2中。
质询1

SELECT t1.col1,
, t2.col12
FROM Table1
inner join Table2 t2
on t1.ID_NO = t2.ID_NO

质询2

SELECT t2.col1||t2.col2, count(distinct t2.col3) Totals
FROM Table2 t2 having count(distinct t2.col3) >=15 GROUP BY t2.col1, t2.col2

| 姓名|账户|共计|
| - ------|- ------|- ------|
| t1.col1|t2.col1和t2.col2|计数(不同t2.col3)|

hts6caw3

hts6caw31#

您还没有描述您希望从查询中得到什么样的输出,但是您可以通过许多途径来连接表:
联接到子查询:

SELECT t1.col1,
       t2.col1 || t2.col2 AS col12,
       t2.max_id_no,
       t2.totals
FROM   Table1 t1
       INNER JOIN (
         SELECT col1,
                col2,
                MAX(id_no) AS max_id_no,
                COUNT(DISTINCT col3) AS Totals
         FROM   Table2
         GROUP BY col1, col2
         HAVING COUNT(DISTINCT col3) >=15
       ) t2
       ON t1.id_no = t2.max_id_no

或者,先加入再分组:

SELECT t2.col1 || t2.col2 AS col12,
       MAX(t1.col1) AS max_t1_col1,
       COUNT(DISTINCT t2.col3) AS Totals
FROM   Table1 t1
       INNER JOIN Table2 t2
       ON (t1.ID_NO = t2.ID_NO)
GROUP BY t2.col1, t2.col2
HAVING COUNT(DISTINCT t2.col3) >=15

相关问题