连接查询结果的Sql查询

Question

我有一个查询结果:

Wname    NewCases
Mon       2
Tues      1
Sat       3

第二个查询结果为:

Wname     FollowUp
Tues         3
Wed          4
Friday       2

我需要写mysql查询得到结果:

Wname   NewCases   FollowUp
Mon       2          0
Tues      1          3
Wed       0          4
Fri       0          2
Sat       3          0

my第一个查询:

Select   substring(dayname(Dated),1,3) as wname,
count(RegId) as NewCases 
from     patienttable 
where    Patient_Type='New' 
and week(Dated)=week(now()) 
group by dayname(Dated) 
order by Dated

2号查询:

Select   substring(dayname(Dated),1,3) as wname,
count(RegId) as FollowUp
from     patienttable
where    Patient_Type='FollowUp' 
and week(Dated)=week(now()) 
group by dayname(Dated)
order by Dated

Answer 1

不需要连接查询。
您可以使用条件聚合在一个查询中完成:

SELECT SUBSTRING(DAYNAME(Dated), 1, 3) AS wname,
SUM(Patient_Type = 'New') AS NewCases,
SUM(Patient_Type = 'FollowUp') AS FollowUp  
FROM patienttable 
WHERE Patient_Type IN ('New', 'FollowUp') AND WEEK(Dated) = WEEK(NOW()) 
GROUP BY DAYNAME(Dated);