34,575
社区成员
发帖
与我相关
我的任务
分享
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
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
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]