ORCALE查询
0
[已解决问题]
解决于 2011-12-19 22:00
1 select a.mgid, count(*) goalcount
2 from custinfo a
3 where a.ctime >= to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
4 and a.ctime <= to_date('2011-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
5 and a.mgid in (select b.mgid from marketgroup b)
6 group by a.mgid
7 union all
8 select c.mgid, count(*) truecount
9 from custinfo c
10 left join contactrecord d
11 on d.custid = c.custid
12 where c.ctime >= to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
13 and c.ctime <= to_date('2011-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
14 and c.mgid in (select e.mgid from marketgroup e)
15 and d.submittime is not null
16 group by c.mgid
我希望得到的结果是
mgid goalcount truecount
0 1111 2222
1 3333 4444
但是现在显示的是：
mgid goalcount
0 1111
1 3333
0 2222
1 4444
求高手指导一下我那段代码应该怎么改。谢谢了
最佳答案
0
楼主，不知道你想获取的结果是否真的是你想要的，如果不存在a.mgid和c.mgid相同的记录，那么结果将是空的。按你的意思写了下面的代码，希望能帮到你。
另外，像a.mgid in (select b.mgid from marketgroup b) 这样的语句写成a left outer join b on a.mgid=b.mgid where b.mgid is not null是不是效率会高一点？
View Code
select at.mgid,at.goalcount,ct.truecount from
(select a.mgid, count(*) goalcount from custinfo a
where a.ctime >= to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and a.ctime <= to_date('2011-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and a.mgid in (select b.mgid from marketgroup b) group by a.mgid
) as at
inner join
(select c.mgid, count(*) truecount from custinfo c left join contactrecord d on d.custid = c.custid
where c.ctime >= to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c.ctime <= to_date('2011-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and c.mgid in (select e.mgid from marketgroup e)
and d.submittime is not null16 group by c.mgid
) as ct on at.mgid=ct.mgid
万分感谢，你的代码给了我很大的启示，周一去试试行不行。
肯定会有a.mgid跟c.mgid相同的记录的，因为第一个语句是查询之前导了多少条数据进数据库，第二个语句是查询导进去的这批数据有多少是已经被提取使用过了（d.submittime is not null 这个条件标示了数据是否被使用过），而且a.mgid跟c.mgid其实是同一张表的同个字段；
另外你说的用a left outer join b on a.mgid=b.mgid where b.mgid is not null来代替a.mgid in (select b.mgid from marketgroup b)我觉得在我这个数据库里这两句的效率其实相差不多，因为marketgroup这张表里只放了3个mgid（原来有4个的），而custinfo这张表的数据里有4个mgid，所以这个语句的作用是筛掉已经没有使用的那第四个mgid的数据
其他回答(1)
0
select mgid, count(1) as goalcount, sum(truecount) as truecount from (
select c.mgid, (case when d.submittime is null then 0 else 1 end) as truecount
from custinfo c
left join contactrecord d on d.custid = c.custid
where c.ctime >= to_date('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c.ctime <= to_date('2011-11-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and c.mgid in (select e.mgid from marketgroup e)) as t1
group by mgid
你试一试！
谢谢
