看了别人的一道面试题,后面贴主也给出了自己的作法,是用存储过程做的,我感觉有点复杂,想了想,我自己也试着做了一下,还是可以用一条语句做出来的.
题目如下:
他们公司网站上的广告位是轮播的,每天某一广告位最多可轮播的广告数量是有限制的,比如A广告位,每天只能轮播三个广告,但销售人员在销售广告位时并不考虑此限制,要求查询出合同表中,超过广告位轮播数量的合同。
合同表 Orders
OrderID | Positioncode | Startdate | Enddate |
1 | A | 2006-11-01 | 2006-11-03 |
2 | C | 2006-11-02 | 2006-11-03 |
3 | B | 2006-11-01 | 2006-11-04 |
4 | A | 2006-11-03 | 2006-11-04 |
5 | C | 2006-11-01 | 2006-11-02 |
6 | B | 2006-11-02 | 2006-11-05 |
7 | A | 2006-11-02 | 2006-11-03 |
8 | A | 2006-11-04 | 2006-11-05 |
9 | C | 2006-11-03 | 2006-11-04 |
10 | C | 2006-11-02 | 2006-11-04 |
广告位表 Product
Positioncode | Showcount |
A | 2 |
B | 1 |
C | 3 |
说明:对于广告位A来讲,轮播情况如下表
OrderID | 2006-11-01 | 2006-11-02 | 2006-11-03 | 2006-11-04 | 2006-11-05 |
1 |
|
|
|
|
|
4 |
|
|
|
|
|
7 |
|
|
|
|
|
8 |
|
|
|
|
|
我的做法:
select o.positioncode, d.day, count(*)
from orders o,
(select to_date((select min(o1.startdate) from orders o1),
'yyyy-mm-dd') + rownum - 1 day
from all_objects
where rownum < 30) d
where d.day >= to_date(o.startdate, 'yyyy-mm-dd')
and d.day <= to_date(o.enddate, 'yyyy-mm-dd')
group by o.positioncode, d.day
having count(*) > (select p.showcnt
from product p
where p.positioncode = o.positioncode);
是否正解,欢迎大家指正!同时也欢迎大家拿出更好的方法.
ps:我建的测试表:
create table orders (orderid number,positioncode number,startdate varchar2(10),enddate varchar2(10));
create table product (positioncode number,showcnt number);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9375/viewspace-605133/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9375/viewspace-605133/