两个表的联表查询

chaikunlai 2010-10-23 09:03:00
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
...全文
89 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2010-10-23
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 行)
*/
回复
dawugui 2010-10-23
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 行)
*/
回复
zsh0809 2010-10-23
按照你的现有逻辑实现了一下
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)
回复
chaikunlai 2010-10-23
[Quote=引用 1 楼 only_endure 的回复:]
看描述看的头晕。。。
[/Quote]
直接执行语句,看一下结果...帮帮忙
回复
一品梅 2010-10-23
看描述看的头晕。。。
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-23 09:03
社区公告
暂无公告