select AA.wusername as username,aa.logdate,,isnull(aa.tbytes,0)+isnull(bb.tbytes,0) from
(select wusername,logdate,sum(tbytes) from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
(select wusername,logdate,sum(tbytes) from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
select a.wusername,a.logdate,sum(a.tbytes)+sum(b.tbytes)
from a,b
where a.logdate= '2001-1-1' and b.logdate= '2001-1-1'
您的语句运行提示说
没有为第 3 列(属于 'AA')指定列。
select uname ,logdate ,sum(ubyte)
from
(select wusename as uname ,logdate,tbytes as ubyte from a
where logdate='2001-1-1'
union ALL
select fusename as uname ,logdate,tbytes as ubyte from b
where logdate='2001-1-1'
) tableC
group by uname,logdate
但在C中只能有一条a
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
select uname ,logdate ,sum(ubyte)
from
(select wusename as uname ,logdate,tbytes as ubyte from a
where logdate='2001-1-1'
union ALL
select fusename as uname ,logdate,tbytes as ubyte from b
where logdate='2001-1-1'
) tableC
group by logdate,uname
aa.wusername=bb.fusername
我认为不可行,因为AA表中有的名字,在BB表中不一定存在,但这不表明AA表中的这条记录不插入C,只是TBYTES字段中不计算sum(bb.tbytes)而已,也就是说这两个表的用户名是不能作为完全的连接的条件的,其实这两个表找不到完全的连接条件就是我最头痛的.
A表是记录用户吃饭(比喻)的数据流量,B表是记录用户吃菜(比喻)的数据流量
C是记录总共吃的东西.所以A有可能吃菜不吃饭,但在C表中还是要插入的,因为他吃了东西
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
a 2001-1-1 150
null null 50
这里应该是两条记录的数据流量的总和,应该是
a 2002-1-1 200请再想想办法,不胜感激
select isnull(AA.wusername,bb.fusername as username,isnull(aa.logdate,bb.logdate),isnull(aa.tbytes,0)+isnull(bb.tbytes,0) as tbytes from
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate) cc
group by username,logdate
select AA.wusername as username,aa.logdate,isnull(aa.tbytes,0)+isnull(bb.tbytes,0) as tbytes from
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
你现在刚写的两段都有错误
服务器: 消息 156,级别 15,状态 1,行 2
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: 'AA' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: 'BB' 附近有语法错误。
没有为第 2 列(属于 'cc')指定列。
select isnull(AA.wusername,bb.fusername as username,isnull(aa.logdate,bb.logdate) as logdate,isnull(aa.tbytes,0)+isnull(bb.tbytes,0) as tbytes from
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate) cc
group by username,logdate
第二行加as logdate,再试试!
select isnull(AA.wusername,bb.fusername) as username,isnull(aa.logdate,bb.logdate) as logdate,isnull(aa.tbytes,0)+isnull(bb.tbytes,0) as tbytes from
(select wusername,logdate,sum(tbytes) as tbytes from a group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from b group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate) cc
group by username,logdate好了,这样应该没有问题了。
select isnull(AA.wusername,bb.fusername) as username,isnull(aa.logdate,bb.logdate),isnull(aa.tbytes,0)+isnull(bb.tbytes,0) as tbytes from
(select wusername,logdate,sum(tbytes) as tbytes from web where logdate='2002-1-1' group by wusername,logdate) AA
full outer join
(select fusername,logdate,sum(tbytes) as tbytes from fire where logdate='2002-1-1' group by fusername,logdate) BB
on aa.wusername=bb.fusername and aa.logdate=bb.logdate
这个是最后通过测试的语句,谢谢!
2002-11-29