select m.username,
m.jobcount as totalcount,
(select jobcount from vmonthinsertyes where username=m.username and yearmonth=m.yearmonth) as Yescount,
(select jobcount from vmonthinsertno where username=m.username and yearmonth=m.yearmonth) as Nocount,
m.yearmonth as jobdate from vmonth m where
m.operateway='录入'
and m.yearmonth>='2004-01'
and m.yearmonth<='2005-01'
and m.username='lb'
m.jobcount as totalcount,
(select jobcount from vmonthinsertyes where username=m.username and yearmonth=m.yearmonth) as Yescount,
(select jobcount from vmonthinsertno where username=m.username and yearmonth=m.yearmonth) as Nocount,
m.yearmonth as jobdate from vmonth m where
m.operateway='录入'
and m.yearmonth>='2004-01'
and m.yearmonth<='2005-01'
and m.username='lb'
max((select jobcount from vmonthinsertyes where username=m.username and yearmonth=m.yearmonth)) as Yescount,
max((select jobcount from vmonthinsertno where username=m.username and yearmonth=m.yearmonth)) as Nocount,
max(m.yearmonth) as jobdate from vmonth m where
m.operateway='录入'
and m.yearmonth>='2004-01'
and m.yearmonth<='2005-01'
and m.username='lb'
group by m.username
SELECT * FROM
(
select m.username AS username ,
m.jobcount AS totalcount,
(select jobcount from vmonthinsertyes where username=m.username and yearmonth=m.yearmonth) AS Yescount,
(select jobcount from vmonthinsertno where username=m.username and yearmonth=m.yearmonth) AS Nocount,
m.yearmonth AS jobdate
from vmonth m
where m.operateway='录入'
and m.yearmonth>='2004-01'
and m.yearmonth<='2005-01'
and m.username='lb'
)MSTB
group by MSTB.username
sum((select jobcount from vmonthinsertyes where username=m.username and yearmonth=m.yearmonth)) as Yescount,
sum((select jobcount from vmonthinsertno where username=m.username and yearmonth=m.yearmonth)) as Nocount,
m.yearmonth as jobdate from vmonth m where
m.operateway='录入'
and m.yearmonth>='2004-01'
and m.yearmonth<='2005-01'
and m.username='lb'
group by m.username,jobdate;