两个表联合查询统计count．怎么输出为一个结果集

[startray]

table1
id name timestamp pagename
1 张三 2008-08-23 index.aspx
2 李四 2008-07-2 page.aspx
3 张三 2007-08-23 index.aspx
4 王五 2008-06-02 index.aspx

table2
id name timestamp action
1 张三 2008-08-23 write
2 李四 2008-07-2 write
3 张三 2007-08-12 read
4 王五 2008-06-02 write

现在想输出一个结果集：
id name timestamp actioncount pagenamecount
1 张三 2008-08-23 1 2
2 李四 2008-07-2 1 1
3 张三 2007-08-12 1 0
4 张三 2007-08-23 0 1
4 王五 2008-06-02 1 1
就是两张表同一个用户不同记录统计然后放到一个结果集中

[-狙击手-]

第一条记录本的pagenamecount为什么是 2 ?

[startray]

对index.spx统计了两次所以计数为2

[startray]

table1
id name timestamp pagename 　　　　　　type
1 张三 2008-08-23 index.aspx 0
2 李四 2008-07-2 page.aspx 1
3 张三 2007-08-23 index.aspx 0
4 王五 2008-06-02 index.aspx 1

table2
id name timestamp action type
1 张三 2008-08-23 write 0
2 李四 2008-07-2 write 1
3 张三 2007-08-12 read 1
4 王五 2008-06-02 write 0

现在想输出一个结果集：
id name timestamp actioncount pagenamecount type
1 张三 2008-08-23 1 2 0
2 李四 2008-07-2 1 1 1
3 张三 2007-08-12 1 0 1
4 王五 2008-06-02 1 0 0
5 王五 2008-06-02 0 1 1

就是两张表同一个用户不同记录统计然后放到一个结果集中
我刚写的有点写的不全．不好意思．就相当于按劳name,timestamp ,type三者分组然后count．这两张表怎么分别组合后然后输出为一个表呢？

[chuifengde]

DECLARE @a table(id int,  name varchar(20) ,   [timestamp] varchar(20),    pagename  varchar(20))
INSERT @a SELECT 1   ,'张三','2008-08-23'   ,'index.aspx' 
union all select 2    ,'李四','2008-07-2'   ,'page.aspx' 
union all select 3    ,'张三','2007-08-23'   ,'index.aspx' 
union all select 4    ,'王五','2008-06-02'   ,'index.aspx' 

DECLARE @b table(id int,  name varchar(20),    [timestamp] varchar(20) ,     action  varchar(20))
INSERT @b SELECT 1    ,'张三',    '2008-08-23',    'write' 
UNION ALL SELECT 2    ,'李四',    '2008-07-2',      'write' 
UNION ALL SELECT 3    ,'张三',    '2007-08-12',      'read' 
UNION ALL SELECT 4    ,'王五',    '2008-06-02',      'write' 

SELECT id,name,[timestamp],
	actioncount=sum(CASE WHEN action='' THEN 0 ELSE 1 END),
	pagenamecount=sum(CASE WHEN pagename='' THEN 0 ELSE 1 END) from
(
	SELECT id,name,[timestamp],cast('' AS varchar(20)) action,pagename FROM @a
	UNION ALL
	SELECT id,name,[timestamp],action,cast('' AS varchar(20)) pagename FROM @b
)aa
GROUP BY id,name,[timestamp]

[-狙击手-]

select a.name,a.timestamp,a.type
actioncount = (select count(1) from table2 where name = a.name and timestamp = a.timestamp),
pagenamecount=(select count(1) from table1 where name = a.name and pagename = a.pagename)
from  (select name ,timestamp,type ,pagename
       from table1 
       union all 
       select name,timestamp,type,''
       from table2 b
       where not exists(select 1 from table1 where name = b.name and timestamp = b.timestamp) a

[fzcheng]

楼上的大侠是对的..

drop table table1
go
drop Table table2
go

Create Table table1 
(id int,  name NVARCHAR(10),    timestamp DATETIME,   pagename NVARCHAR(10))
INSERT INTO table1(id,name,timestamp,pageName)
Select  1,'张三',      '2008-08-23',  'index.aspx' UNION ALL 
Select   2,    '李四',      '2008-07-2',    'page.aspx' UNION ALL 
Select   3,    '张三',      '2008-08-23',    'index.aspx' UNION ALL 
Select  4,    '王五',    '2008-06-02',    'index.aspx '

Create Table table2 
(id int, name NVARCHAR(10),    timestamp DATETIME,      action NVARCHAR(10)) 
INSERT INTO table2(id,name,timestamp,action)
select 1,    '张三',      '2008-08-23 ' ,  'write' UNION ALL
Select 2,    '李四'  ,    '2008-07-2'   ,   'write' UNION ALL
Select 3,    '张三'  ,    '2007-08-23' ,    'read' UNION ALL
Select 4,    '王五'  ,  '2008-06-02'    ,  'write' 

select a.name,a.timestamp,
actioncount = (select count(1) from table2 where name = a.name and timestamp = a.timestamp),
pagenamecount=(select count(1) from table1 where name = a.name and pagename = a.pagename)
from  (select name ,timestamp ,pagename
       from table1 
       union all 
       select name,timestamp,''
       from table2 b
       where not exists(select 1 from table1 where name = b.name and timestamp = b.timestamp)) a

go

[lyshiba]

靠！五楼这么牛X！