物品表：
mysql> select * from goods;
+----+-------+
| id | name |
+----+-------+
| 1 | brush |
| 2 | pen |
| 3 | pecil |
| 4 | box |
| 5 | oil |
| 6 | book |
| 7 | desk |
+----+-------+
订单表：
mysql> select * from bills order by time;
+----+---------+------------+-----+
| id | good_id | time | cnt |
+----+---------+------------+-----+
| 1 | 4 | 2012-05-21 | 4 |
| 2 | 4 | 2012-05-22 | 22 |
| 3 | 6 | 2012-05-22 | 211 |
| 4 | 6 | 2012-05-23 | 1 |
| 5 | 3 | 2012-05-23 | 2 |
| 6 | 5 | 2012-05-27 | 1 |
| 7 | 7 | 2012-05-27 | 2 |
| 13 | 3 | 2012-06-01 | 1 |
| 12 | 2 | 2012-06-02 | 6 |
| 10 | 6 | 2012-06-21 | 3 |
| 8 | 7 | 2012-06-21 | 2 |
| 9 | 7 | 2012-06-22 | 2 |
| 11 | 1 | 2012-06-28 | 5 |
| 14 | 2 | 2012-07-01 | 2 |
| 15 | 5 | 2012-07-01 | 22 |
| 16 | 4 | 2012-07-21 | 27 |
| 17 | 6 | 2012-07-21 | 1 |
| 18 | 7 | 2012-07-21 | 1 |
| 19 | 3 | 2014-07-22 | 5 |
+----+---------+------------+-----+
用一句sql解决：
每月中，销售量最大的3件商品，输出列：月份、商品名称、销售量。（注意：某月中，同一件商品可能有多个订单，需要聚合这些订单）。
我目前可以用两句解决：
create view v_tmp as
select
month(`bills`.`time`) AS `mon`,`goods`.`name` AS `name`,sum(`bills`.`cnt`) AS `total`
from
(`bills` join `goods` on((`bills`.`good_id` = `goods`.`id`)))
group by month(`bills`.`time`),`goods`.`id`
order by month(`bills`.`time`),sum(`bills`.`cnt`) desc;
select * from v_tmp vt where (
select count(*) from v_tmp where mon=vt.mon and total>vt.total
) < 3;
------解决方案--------------------
if object_id('[goods]') is not null drop table goods
go
create table goods (id int,name varchar(10) )
insert into goods
select 1,'brush'union all
select 2,'pen' union all
select 3,'pecil'union all
select 4,'box' union all
select 5,'oil' union all
select 6,'book' union all
select 7,'desk'
go
if object_id('[bills]') is not null drop table bills
create table bills (id int,good_id int ,time datetime ,cnt int )
insert into bills
select 1,4,'2012-05-21',4 union all
select 2,4,'2012-05-22',22 union all
select 3,6,'2012-05-22',211 union all
select 4,6,'2012-05-23',1 union all