我正在创建两个查询：
一
select count(c.id) as cid1, p.name as pname1, u.name as uname1
from crm_lead c, account_period p, res_users u
where c.create_date between p.date_start and p.date_stop and
(c.user_id = u.id or c.sales_vertical=u.id) and
u.id = 1
group by p.name, u.name
二
select count(c.id) as cid2, p.name as pname2, u.name as uname2
from crm_lead c, account_period p, res_users u
where c.create_date between p.date_start and p.date_stop and
(c.user_id = u.id or c.sales_vertical=u.id) and
stage_id =12 and
u.id = 1
where a.pname = b.pname
group by p.name, u.name
以上两个查询的结果如下
第一季度结果：
cid1 pname1 uname1
11 07/2011 admin
5 08/2011 admin
9 09/2011 admin
第2季度结果：
cid2 pname2 uname2
9 07/2011 admin
3 09/2011 admin
在组合两个查询之后，我得到以下输出：
cid1 cid2 pname1 pname2 uname1 uname2
11 9 07/2011 07/2011 admin admin
9 3 09/2011 09/2011 admin admin
但我希望结果是
cid1 cid2 pname1 pname2 uname1 uname2
11 9 07/2011 07/2011 admin admin
5 0 08/2011 08/2011 admin admin
9 3 09/2011 09/2011 admin admin
怎么能做到？
我的综合查询如下：
select a.cid1, b.cid2, a.pname1, b.pname2, a.uname1, b.uname2
from (select count(c.id) as cid1, p.name as pname1, u.name1 as uname
from crm_lead c, account_period p, res_users u
where c.create_date between p.date_start and p.date_stop and
(c.user_id = u.id or c.sales_vertical=u.id) and
u.id = 1
group by p.name, u.name) as a,
(select count(c.id) as cid2, p.name as pname2, u.name as uname2
from crm_lead c, account_period p, res_users u
where c.create_date between p.date_start and p.date_stop and
(c.user_id = u.id or c.sales_vertical=u.id) and
stage_id =12 and
u.id = 1
group by p.name, u.name)as b
where a.pname1 = b.pname2
最佳答案
要获取请求的结果，查询可能如下所示：
SELECT a.cid1
,COALESCE(b.cid2, 0) AS cid2
,a.pname1
,COALESCE(b.pname2, a.pname2) AS pname2
,a.uname1
,COALESCE(b.uname2, a.uname2) AS uname2
FROM (<query1>) a
LEFT JOIN (<query2>) b ON a.pname1 = b.pname2
不过，我怀疑你是否真的想得到确切的结果。