sql语句将结果集相加/多表查询/

[dlh0529]

查询3个条码9月和10月的pv(upid),uv(urid)。
原语句：
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' GROUP BY IndirectObject
UNION ALL
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' GROUP BY IndirectObject

结果集是：

6900572040302 473 407
6900572061093 65 39
6901070180170 1627 1339
6900572040302 548 448
6900572061093 72 54
6901070180170 1839 1412
（同一个条码有两条记录）

希望能将结果变为：
6900572040302 1021 855
6900572061093 137 93
6901070180170 3466 2751
将同一条码的记录合并相加

有什么方法可以实现么

[--小F--]

;with f as
(

select IndirectObject,count(UPID) as CUPID ,COUNT(DISTINCT URID) as [count]from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' GROUP BY IndirectObject
UNION ALL
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' GROUP BY IndirectObject
)

select IndirectObject,sum(CUPID) ,sum([count]) from f group by IndirectObject

[黄_瓜]

select IndirectObject,sum(UPID),sum(URID)
(
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' GROUP BY IndirectObject
UNION ALL
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' GROUP BY IndirectObject
) t
group by IndirectObject

[黄_瓜]

select IndirectObject,sum(UPID),sum(URID)
(
select IndirectObject,count(UPID) as  UPID,COUNT(DISTINCT URID) as  URID from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' GROUP BY IndirectObject
UNION ALL
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' GROUP BY IndirectObject
) t
group by IndirectObject

[中国风]

and Datetime<='2011-10-31'--這條件不對吧
and Datetime<='2011-9-30'--

兩個結果集會有重復數據

[中国风]

COUNT(DISTINCT URID)--這個字段在兩表有重復時記算了兩次
GC_User_Report_2011_0901_1001/GC_User_Report_2011_1001_1101

[中国风]

SELECT 
IndirectObject,
count(UPID) AS UPID,COUNT(DISTINCT URID) AS URID
FROM 
(
select IndirectObject,UPID, URID from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' 
UNION ALL
select IndirectObject,UPID, URID from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' 
)t
GROUP BY IndirectObject

猜一個
把group by 放在外面時，不會重復記數

[pengxuan]

select IndirectObject,sum(c1),sum(c2) from
(
--把你写的查询作为子查询放在括号里然后再分组统计
select IndirectObject,c1=count(UPID),c2=COUNT(DISTINCT URID) from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' GROUP BY IndirectObject
UNION ALL
select IndirectObject,count(UPID),COUNT(DISTINCT URID) from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' GROUP BY IndirectObject
) t group by IndirectObject

[chenguang79]

WITH newSql AS
(
select IndirectObject,count(UPID) as UpidNum,COUNT(DISTINCT URID) as dUpidNum from GC_User_Report_2011_0901_1001
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-9-30' 
UNION ALL
select IndirectObject,count(UPID) as UpidNum ,COUNT(DISTINCT URID) as dUpidNum from GC_User_Report_2011_1001_1101
where (IndirectObject='6900572040302'
OR IndirectObject='6900572061093'
OR IndirectObject='6901070180170') and Datetime<='2011-10-31' 
)

SELECT  IndirectObject,sum(UpidNum),sum(dUpidNum) FROM newSql GROUP BY IndirectObject