我现在有2张表 是分别记录log的
我对他们分别作了处理
select date,projectid,resourceid,sum(pv) as pv from pvstatresult group by date,projectid,resourceid
得到了第一张表t1
字段分别是 date projectid resourceid pv
数据分别是 2008-01-10 1 1 1
2008-01-11 1 1 1
2008-01-12 1 1 1
2008-01-13 2 2 4select date,projectid,resourceid,sum(click) as click from clickstatresult group by date,projectid,resourceid
得到了第二张表t2
字段分别是 date projectid resourceid click
数据分别是 2008-01-11 1 1 2
2008-01-12 1 1 7
2008-01-12 2 1 6
2008-01-13 1 1 3
2008-01-13 2 1 7
2008-01-13 2 2 8
有上面两张表可以看出我要求的数据是不重复的
接下来我需要把这两张表整合查询
等到这样的一张表
字段分别是 date projectid resourceid click pv
2008-01-10 1 1 1
2008-01-11 1 1 2 1
2008-01-12 1 1 7 1
2008-01-12 2 1 6
2008-01-13 1 1 3
2008-01-13 2 1 7
2008-01-13 2 2 8 4
等到上面的表 条件就是 date projectid resourceid 必须相等 将两个表的数据合并
我需要等到 一条sql 语句(结合我上面的2条)来得出这张表 对其进行查询
请高手 指教 谢谢
我对他们分别作了处理
select date,projectid,resourceid,sum(pv) as pv from pvstatresult group by date,projectid,resourceid
得到了第一张表t1
字段分别是 date projectid resourceid pv
数据分别是 2008-01-10 1 1 1
2008-01-11 1 1 1
2008-01-12 1 1 1
2008-01-13 2 2 4select date,projectid,resourceid,sum(click) as click from clickstatresult group by date,projectid,resourceid
得到了第二张表t2
字段分别是 date projectid resourceid click
数据分别是 2008-01-11 1 1 2
2008-01-12 1 1 7
2008-01-12 2 1 6
2008-01-13 1 1 3
2008-01-13 2 1 7
2008-01-13 2 2 8
有上面两张表可以看出我要求的数据是不重复的
接下来我需要把这两张表整合查询
等到这样的一张表
字段分别是 date projectid resourceid click pv
2008-01-10 1 1 1
2008-01-11 1 1 2 1
2008-01-12 1 1 7 1
2008-01-12 2 1 6
2008-01-13 1 1 3
2008-01-13 2 1 7
2008-01-13 2 2 8 4
等到上面的表 条件就是 date projectid resourceid 必须相等 将两个表的数据合并
我需要等到 一条sql 语句(结合我上面的2条)来得出这张表 对其进行查询
请高手 指教 谢谢
或用 union .
[date]=isnull(t1.[date],t2.[date],''),
[projectid]=isnull(t1.[projectid],t2.[projectid]),
[resourceid]=isnull(t1.resourceid,t2.resourceid),
[click]=isnull(rtrim(t2.click),''),
[pv]=isnull(rtrim(t1.pv),'')
from
(select date,projectid,resourceid,sum(pv) as pv
from pvstatresult group by date,projectid,resourceid)T1
full join
(select date,projectid,resourceid,sum(click) as click
from clickstatresult group by date,projectid,resourceid )T2
on t1.[date]=t2.[date] and t1.[projectid]=t2.[projectid] and t1.resourceid=t2.resourceid
from
(select date,projectid,resourceid,0 as click,sum(pv) as pv
from pvstatresult group by date,projectid,resourceid
union all
select date,projectid,resourceid,sum(click) as click ,0 as pv
from clickstatresult group by date,projectid,resourceid
)Tgroup by date,projectid,resourceid