时间戳

首页 > 解决方案 > 通过将两个查询的结果连接在一起来组合它们

sql - 通过将两个查询的结果连接在一起来组合它们

问题描述

我想将这两个查询的结果合并为一个结果。我尝试使用完全外连接，但出现错误。

select MS.TeamID, count(MS.MemberID) as Number_Of_Members
from MEMBERSHIP MS
where MS.TeamID  in
(select TeamID
from FRANCHISE_APP
where Status ='granted' or Status = 'Granted' or Status ='GRANTED')
group by TeamID;

select T.TeamID, T.TeamName, T.TeamLeaderID
from Team T
where T.TeamID in
(select TeamID
from FRANCHISE_APP
where Status ='granted' or Status = 'Granted' or Status ='GRANTED')
;

期望的结果：

|teamid|teanname |leadername|numberofmembers|
|------| ------- | -------- |---------------|
|T1    | TI      |  Justin  |4              |
|T2    | MT      |  Danny   |3              |

标签： sqloracle

解决方案

基于所提供的有限信息；对以下 SQL 的一些更改应该可以工作。

With A as (
  select 
    MS.TeamID, 
    count(MS.MemberID) as Number_Of_Members 
  from 
    MEMBERSHIP MS 
  where 
    MS.TeamID in (
      select 
        TeamID 
      from 
        FRANCHISE_APP 
      where 
        Status = 'granted' 
        or Status = 'Granted' 
        or Status = 'GRANTED'
    ) 
  group by 
    TeamID
), 
B as (
  select 
    T.TeamID, 
    T.TeamName, 
    T.TeamLeaderID 
  from 
    Team T 
  where 
    T.TeamID in (
      select 
        TeamID 
      from 
        FRANCHISE_APP 
      where 
        Status = 'granted' 
        or Status = 'Granted' 
        or Status = 'GRANTED'
    )
) 
Select 
  A.teamID, 
  B.teamname, 
  B.TeamLeaderID, 
  A.Number_Of_Members 
from 
  A, 
  B 
where 
  a.teamID = B.teamId;

推荐阅读