create table promotions
(
name varchar2(20),
promo_id number(6),
promo_name varchar2(30),
promo_subcategory varchar2(30),
promo_category varchar2(30),
promo_cost number(10,3),
promo_begin_date date,
promo_end_date date
);
此语句居然不会报错
select promo_category, sum(promo_cost)
from promotions
group by promo_category
having min(promo_end_date-promo_begin_date) > 30;
可以理解为
select promo_category, sum(promo_cost),min(promo_end_date-promo_begin_date)
from promotions
group by promo_category
having min(promo_end_date-promo_begin_date) > 30;
验证一下:
--第一组符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('A',1, sysdate,sysdate-31);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values ('A',2, sysdate, sysdate -32 );
--第二组不符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('B',5, sysdate, sysdate -29);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('B',4, sysdate, sysdate -32);
--第三组不符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('C',5, sysdate, sysdate -29);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('C',6, sysdate, sysdate -28);
commit;
执行查询语句,只有第一组会被筛选出来
![](//img.blog.itpub.net/blog/attachment/201608/25/31142205_1472109505GNdZ.png?x-oss-process=style/bb)
(
name varchar2(20),
promo_id number(6),
promo_name varchar2(30),
promo_subcategory varchar2(30),
promo_category varchar2(30),
promo_cost number(10,3),
promo_begin_date date,
promo_end_date date
);
此语句居然不会报错
select promo_category, sum(promo_cost)
from promotions
group by promo_category
having min(promo_end_date-promo_begin_date) > 30;
可以理解为
select promo_category, sum(promo_cost),min(promo_end_date-promo_begin_date)
from promotions
group by promo_category
having min(promo_end_date-promo_begin_date) > 30;
验证一下:
--第一组符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('A',1, sysdate,sysdate-31);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values ('A',2, sysdate, sysdate -32 );
--第二组不符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('B',5, sysdate, sysdate -29);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('B',4, sysdate, sysdate -32);
--第三组不符合条件
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('C',5, sysdate, sysdate -29);
insert into promotions(promo_category,promo_cost,promo_end_date,promo_begin_date) values('C',6, sysdate, sysdate -28);
commit;
执行查询语句,只有第一组会被筛选出来
![](http://img.blog.itpub.net/blog/attachment/201608/25/31142205_1472109505GNdZ.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31142205/viewspace-2124030/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31142205/viewspace-2124030/