两个表的联表查询

[chaikunlai]

CREATE TABLE [dbo].[tableA](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[mCount] [int] NOT NULL DEFAULT ((0)),
[mSMSstate] [bit] NOT NULL,
[mName] [varchar](50) NOT NULL
)


CREATE TABLE [dbo].[tableB](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[sMsgId] [int] NOT NULL,
[sRes] [int] NOT NULL,
[sName] [varchar](50) NOT NULL
)


insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(2,'True','test')
insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(5,'False','test')
insert into dbo.tableA values(1,'False','test')
insert into dbo.tableA values(1,'True','test2')
insert into dbo.tableA values(1,'True','test2')
insert into dbo.tableA values(1,'False','test2')

insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,2,'test')
insert into dbo.tableB values(1,2,'test')
insert into dbo.tableB values(1,2,'test2')
insert into dbo.tableB values(1,2,'test2')


查询语句
Select r.mName,sum(r.mCount),sum(case when r.mSMSstate='True' then r.mCount end),count(s.ID),count(case when s.sRes=1 then s.ID end) From tableA as r left join tableB as s on s.sName=r.mName group by r.mName


正确需要的结果应该为
mName ASum ATSum Bcount BTcount
test 11 4 6 4
test2 3 2 2 0

现在的结果确实查很远..
表B算同一个Name的条数,而表A是要算同一个Name的mCount的和...(Name有多个的)
并且表A跟表B的条数是不一样的主键ID就肯定不一样,其实最终的效果就是要下面的输出,将同一个Name的成为一行
Select s.sName,count(s.ID),count(case when s.sRes=1 then s.ID end) From tableB as s group by s.sName
Select r.mName,sum(r.mCount),sum(case when r.mSMSstate='True' then r.mCount end)From tableA as r group by r.mName

[一品梅]

看描述看的头晕。。。

[chaikunlai]

[Quote=引用 1 楼 only_endure 的回复:]
看描述看的头晕。。。
[/Quote]
直接执行语句,看一下结果...帮帮忙

[「已注销」]

按照你的现有逻辑实现了一下
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.sName

mName                                              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)

[dawugui]

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 行）
*/

[dawugui]

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 行）
*/