按照你的现有逻辑实现了一下 SELECT mName ,ASum ,ATSum, Bcount, BTcount FROM ( Select s.sName,count(s.ID)Bcount ,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )s RIGHT JOIN ( Select r.mName,sum(r.mCount)ASum,sum(case when r.mSMSstate='True' then r.mCount end)ATSum From tableA as r group by r.mName )r ON r.mName=s.sNamemName ASum ATSum Bcount BTcount -------------------------------------------------- ----------- ----------- ----------- ----------- test 11 5 6 4 test2 3 2 2 0 Warning: Null value is eliminated by an aggregate or other SET operation.(2 row(s) affected)
select isnull(m.mName,n.sname) mName , m.ASum , m.ATSum, n.Bcount, n.BTcount from (Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then 1 end) ATSum From tableA as r group by r.mName) m full join (Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n on m.mname = n.sname/* mName ASum ATSum Bcount BTcount -------------------------------------------------- ----------- ----------- ----------- ----------- test 11 4 6 4 test2 3 2 2 0(所影响的行数为 2 行) */
select isnull(m.mName,n.sname) mName , m.ASum , m.ATSum, n.Bcount, n.BTcount from (Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then 1 end) ATSum From tableA as r group by r.mName) m full join (Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n on m.mname = n.sname/* mName ASum ATSum Bcount BTcount -------------------------------------------------- ----------- ----------- ----------- ----------- test 11 4 6 4 test2 3 2 2 0(所影响的行数为 2 行) */select isnull(m.mName,n.sname) mName , m.ASum , m.ATSum, n.Bcount, n.BTcount from (Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then r.mCount else 0 end) ATSum From tableA as r group by r.mName) m full join (Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n on m.mname = n.sname /*mName ASum ATSum Bcount BTcount -------------------------------------------------- ----------- ----------- ----------- ----------- test 11 5 6 4 test2 3 2 2 0(所影响的行数为 2 行) */
SELECT mName ,ASum ,ATSum, Bcount, BTcount
FROM
(
Select s.sName,count(s.ID)Bcount ,count(case when s.sRes=1 then s.ID end) BTcount
From tableB as s
group by s.sName
)s
RIGHT JOIN
(
Select r.mName,sum(r.mCount)ASum,sum(case when r.mSMSstate='True' then r.mCount end)ATSum
From tableA as r
group by r.mName
)r
ON r.mName=s.sNamemName ASum ATSum Bcount BTcount
-------------------------------------------------- ----------- ----------- ----------- -----------
test 11 5 6 4
test2 3 2 2 0
Warning: Null value is eliminated by an aggregate or other SET operation.(2 row(s) affected)
m.ASum ,
m.ATSum,
n.Bcount,
n.BTcount
from
(Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then 1 end) ATSum From tableA as r group by r.mName) m
full join
(Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n
on m.mname = n.sname/*
mName ASum ATSum Bcount BTcount
-------------------------------------------------- ----------- ----------- ----------- -----------
test 11 4 6 4
test2 3 2 2 0(所影响的行数为 2 行)
*/
m.ASum ,
m.ATSum,
n.Bcount,
n.BTcount
from
(Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then 1 end) ATSum From tableA as r group by r.mName) m
full join
(Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n
on m.mname = n.sname/*
mName ASum ATSum Bcount BTcount
-------------------------------------------------- ----------- ----------- ----------- -----------
test 11 4 6 4
test2 3 2 2 0(所影响的行数为 2 行)
*/select isnull(m.mName,n.sname) mName ,
m.ASum ,
m.ATSum,
n.Bcount,
n.BTcount
from
(Select r.mName,sum(r.mCount) ASum,sum(case when r.mSMSstate='True' then r.mCount else 0 end) ATSum From tableA as r group by r.mName) m
full join
(Select s.sName,count(s.ID) Bcount,count(case when s.sRes=1 then s.ID end) BTcount From tableB as s group by s.sName )n
on m.mname = n.sname
/*mName ASum ATSum Bcount BTcount
-------------------------------------------------- ----------- ----------- ----------- -----------
test 11 5 6 4
test2 3 2 2 0(所影响的行数为 2 行)
*/