MySql - 三个表由一个表中的一列连接和分组

Question

我总共有四张 table ，

tbl用户，tbl学生领导，tbl 调用，tbldocusin

我想按 tbluser.id 对查询结果进行分组

这是我的查询:

select t.fk, MAX(u.firstname) as Name, t.Leads, t.Calls, t.Docu
from
(


SELECT salesuserId as fk, COUNT(id) Leads
FROM tblstudentlead 
WHERE createdon
BETWEEN  '2015-01-01'
AND  '2016-01-01'
GROUP BY salesuserId


union all
SELECT createdBy as fk, COUNT(id) Calls
FROM tblcalls
WHERE createdon
BETWEEN  '2015-01-01'
AND  '2016-01-01'
GROUP BY createdBy


union all
SELECT createdBy as fk, COUNT(id) Docu
FROM tbldocusin
WHERE createdon
BETWEEN  '2015-01-01'
AND  '2016-01-01'
AND status = 'completed'
GROUP BY createdBy
) t

inner join tbluser u on (t .fk=u.id)
group by u.id
order by u.id asc

但它只给了我这个结果

fk | Name | Leads 

我需要的结果是:

fk | Name | Leads | Docu

谢谢希望你能帮助我

Answer 1

如果我正确理解你的问题，你可能会更好地使用子查询。这是一个例子:

select u.id, 
       u.firstname as Name, 
       (select count(id) Leads
        from tblstudentlead t
        where t.salesuserId = u.id and createdon between '2015-01-01' and '2016-01-01') as Leads,
       (select count(id) Calls
        from tblcalls c
        where c.createdBy = u.id and createdon between '2015-01-01' and '2016-01-01') as Calls,
       (select count(id) Docu
        from tbldocusin d
        where d.createdBy = u.id createdon between '2015-01-01' and '2016-01-01') as Docu
from tbluser u 
order by u.id

或者，您可以在 join 中使用子查询:

select u.id, 
       u.firstname as Name, 
       l.Leads,
       c.Calls,
       d.Docu
from tbluser u join
    (select salesuserId as id, count(id) Leads
    from tblstudentlead 
    where createdon between '2015-01-01' and '2016-01-01'
    group by salesuserId) l on u.id = l.id join 
    (select createdBy as id, count(id) Calls
    from tblcalls 
    where createdon between '2015-01-01' and '2016-01-01'
    group by createdBy) c on u.id = c.id join 
    (select createdBy as id, count(id) Calls
    from tbldocusin 
    where createdon between '2015-01-01' and '2016-01-01'
    group by createdBy) d on u.id = d.id 
order by u.id