SELECT UID ,ProjectID,
      sum([LocalValidCount]) as 本地预约量
      ,sum([NoLocalValidCount]) as 外地预约量
  FROM DialogueQuantity
group by
  UID,ProjectID

select
   UID,ProjectID,
   count(1) as 患者总数,
   sum(case IsNative when 'true' then 1 else 0 end) as '本地患者数量',
   sum(case IsNative when 'false' then 1 else 0 end) as '外地患者数量',
   sum(case DiseaseName when '感冒' then 1 else 0 end) as '感冒',
   sum(case DiseaseName when '发烧' then 1 else 0 end) as '发烧',
   sum(case DiseaseName when '阑尾炎' then 1 else 0 end) as '阑尾炎'

select * from
(
SELECT UID ,ProjectID,
      sum([LocalValidCount]) as 本地预约量
      ,sum([NoLocalValidCount]) as 外地预约量
  FROM DialogueQuantity
group by
  UID,ProjectI
) as a
full join 
(
select
   UID,ProjectID,
   count(1) as 患者总数,
   sum(case IsNative when 'true' then 1 else 0 end) as '本地患者数量',
   sum(case IsNative when 'false' then 1 else 0 end) as '外地患者数量',
   sum(case DiseaseName when '感冒' then 1 else 0 end) as '感冒',
   sum(case DiseaseName when '发烧' then 1 else 0 end) as '发烧',
   sum(case DiseaseName when '阑尾炎' then 1 else 0 end) as '阑尾炎'
) as b
on a.UID=b.UID and a.ProjectID=b.ProjectID

------解决方案--------------------

直接写 select * from
(
SELECT UID ,ProjectID,
     sum([LocalValidCount]) as 本地预约量
     ,sum([NoLocalValidCount]) as 外地预约量
 FROM DialogueQuantity
group by
 UID,ProjectI
) as a
full join  
(
select
  UID,ProjectID,
  count(1) as 患者总数,
  sum(case IsNative when 'true' then 1 else 0 end) as '本地患者数量',
  sum(case IsNative when 'false' then 1 else 0 end) as '外地患者数量',
  sum(case DiseaseName when '感冒' then 1 else 0 end) as '感冒',
  sum(case DiseaseName when '发烧' then 1 else 0 end) as '发烧',
  sum(case DiseaseName when '阑尾炎' then 1 else 0 end) as '阑尾炎'
) as b
on a.UID=b.UID and a.ProjectID=b.ProjectID

------解决方案--------------------

SQL code

-- 方法1
select * 
from ([子查询1]) a
full join ([子查询2]) b

-- 方法2
with 
a as
([子查询1]),
b as
([子查询2])
select *
from a full join b

------解决方案--------------------

SQL code

select * from (
SELECT UID ,ProjectID,
      sum([LocalValidCount]) as 本地预约量
      ,sum([NoLocalValidCount]) as 外地预约量
  FROM DialogueQuantity
group by
  UID,ProjectID)a full join (
select
   UID,ProjectID,
   count(1) as 患者总数,
   sum(case IsNative when 'true' then 1 else 0 end) as '本地患者数量',
   sum(case IsNative when 'false' then 1 else 0 end) as '外地患者数量',
   sum(case DiseaseName when '感冒' then 1 else 0 end) as '感冒',
   sum(case DiseaseName when '发烧' then 1 else 0 end) as '发烧',
   sum(case DiseaseName when '阑尾炎' then 1 else 0 end) as '阑尾炎'
) b on a.UID=b.UID and a.ProjectID and b.ProjectID