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.aspxtable2
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
就是两张表同一个用户不同记录统计然后放到一个结果集中
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.aspxtable2
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
就是两张表同一个用户不同记录统计然后放到一个结果集中
解决方案 »
- 表设计问题
- 本人愚昧,小小约束搞不懂,请人帮忙写一下
- 关于VALUES语句插入多个数据
- TOP N 和SET ROWCOUNT N的区别是什么
- 服务重启失败
- 关于sql server 6.5 字段去掉空格的问题
- 求救~高手 SQL问题
- 发布跟订阅问题
- 如何返回在sql server中自己设定的错误提示.....
- 各位大虾,请问在MS SQL Server建立了两个表的主外键关系后,在PB中的Database画板中的Foreign Key属性中没有Rules选项卡啊?(若用anywhere数据库时有的啊)
- 请教 SQL2005 或 2008 中如何将 identity 的主键字段重新改为非自增字段?
- 在sql server里是否有trim这个词
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 1table2
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.这两张表怎么分别组合后然后输出为一个表呢?
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]
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
go
drop Table table2
goCreate 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)) ago