sql多表分组查询

select * from

(

select t0=CONVERT(VARCHAR(10),[TimeStamp],120)

from table0 S

INNER JOIN ACDGroupMember M on

S.LoginID=M.AgentID

WHERE M.ACDGroupID=10016 AND S.TimeStamp BETWEEN '2012-10-2' AND '2012-11-2'

group by CONVERT(VARCHAR(10),[TimeStamp],120)

left join

(

select t1= COUNT(M.AgentID),t2=CONVERT(VARCHAR(10),[TimeStamp],120)

from table1 S

INNER JOIN ACDGroupMember M on

S.LoginID=M.AgentID

WHERE M.ACDGroupID=10016 AND S.TimeStamp BETWEEN '2012-10-2' AND '2012-11-2'

group by CONVERT(VARCHAR(10),[TimeStamp],120)

)b on t0=t2