增加一个表，怎么查询？

[emilchan6k]

参考这个帖子，我试了半天也没弄出来

http://community.csdn.net/Expert/TopicView3.asp?id=5643256
现在我要增加一个表的关联
tableC
userID,userName
1 admin
2 test
3 test2

[yaoshun1983]

建个视图吧。

[hellowork]

if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('tbTestC') is not null
drop table tbTestC
GO
create table tbTest1(aid int,name varchar(10),modelsId int)
insert tbTest1
select 1,'发贴',1 union all
select 2,'回贴',1 union all
select 3,'消息',2 union all
select 4,'申请',3
create table tbTest2(userid int,aid int,action varchar(10),content varchar(10))
insert tbTest2
select 1, 1, 'add', 'fsfdsfsd' union all
select 1, 2, 'add', 'sdfsfasf' union all
select 2, 3, 'add', 'adfsfddf' union all
select 3, 3, 'add', 'adfssddf'
create table tbTestC(userid int,userName varchar(10))
insert tbTestC
select 1, 'admin' union all
select 2, 'test' union all
select 3, 'test2'

----汇总
declare @sql varchar(8000)
set @sql = 'select a.userid,c.userName'
select @sql = @sql + ',' + name + '=sum(case b.name when ''' + name + ''' then 1 else 0 end)'
from tbTest1 group by name
set @sql = @sql + ' from tbTest2 as a
left join tbTest1 as b on a.aid = b.aid
LEFT JOIN tbTestC as c on a.userid = c.userid
group by a.userid,c.userName'
EXEC(@sql)

----清除测试环境
drop table tbTest1,tbTest2,tbTestC


/*结果
userid userName 发贴 回贴 申请 消息
----------- ---------- ----------- ----------- ----------- -----------
1 admin 1 1 0 0
2 test 0 0 0 1
3 test2 0 0 0 1
*/

[emilchan6k]

看来我对group by 理解得不透彻。。。。拜ls