首页新闻找找看学习计划

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

求高手指导一下我那段代码应该怎么改。谢谢了

Mr.ch的主页 Mr.ch | 初学一级 | 园豆:6
提问于:2011-12-16 14:40
< >
分享
最佳答案
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
stalwartwill | 初学一级 |园豆:139 | 2011-12-16 18:33

万分感谢,你的代码给了我很大的启示,周一去试试行不行。

肯定会有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的数据

Mr.ch | 园豆:6 (初学一级) | 2011-12-16 19:55
其他回答(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

你试一试!

小小刀 | 园豆:1991 (小虾三级) | 2011-12-16 23:26

谢谢

支持(0) 反对(0) Mr.ch | 园豆:6 (初学一级) | 2011-12-19 22:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册