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 select 5,3,'2012-05-23',2 union all select 6,5,'2012-05-27',1 union all select 7,7,'2012-05-27',2 union all select 13,3,'2012-06-01',1 union all select 12,2,'2012-06-02',6 union all select 10,6,'2012-06-21',3 union all select 8,7,'2012-06-21',2 union all select 9,7,'2012-06-22',2 union all select 11,1,'2012-06-28',5 union all select 14,2,'2012-07-01',2 union all select 15,5,'2012-07-01',22 union all select 16,4,'2012-07-21',27 union all select 17,6,'2012-07-21',1 union all select 18,7,'2012-07-21',1 union all select 19,3,'2014-07-22',5 goselect t1.id ,t1.name ,t1.cnt,t1.月份 from ( select t.id ,t.name ,t.cnt,t.月份,ROW_NUMBER() over (partition by t.月份 order by t.cnt desc) as rn1 from ( select a.id ,b.name ,a.cnt,Rtrim(year(a.time))+'-'+Rtrim(Month(a.time)) as 月份 ,ROW_NUMBER () over ( partition by Rtrim(year(a.time))+'-'+Rtrim(Month(a.time)) ,b.name order by cnt desc) as rn from bills a left join goods b on a .good_id =b.id )t where t.rn=1 )t1 where rn1<=3--id name cnt 月份 --3 book 211 2012-5 --2 box 22 2012-5 --7 desk 2 2012-5 --12 pen 6 2012-6 --11 brush 5 2012-6 --10 book 3 2012-6 --16 box 27 2012-7 --15 oil 22 2012-7 --14 pen 2 2012-7 --19 pecil 5 2014-7
use test; drop table if exists test; create table test ( id int primary key, cid int, author varchar(30) ) engine=myisam; insert into test values (1,1,'test1'), (2,1,'test1'), (3,1,'test2'), (4,1,'test2'), (5,1,'test2'), (6,1,'test3'), (7,1,'test3'), (8,1,'test3'), (9,1,'test3'), (10,2,'test11'), (11,2,'test11'), (12,2,'test22'), (13,2,'test22'), (14,2,'test22'), (15,2,'test33'), (16,2,'test33'), (17,2,'test33'), (18,2,'test33');
-- select * from (select cid,author,count(*) as number from test group by cid,author) a where 2>( select count(*) from (select cid,author,count(*) as number from test group by cid,author) b where a.cid=b.cid and a.number<b.number )order by cid,number desc;例子 分组求前二
虽然没有表连接、没有求和,不过道理我明白了,我把自己的sql改了改能用了
select mon, name, total from ( select bills.id, month(time) as mon, name, sum(cnt) as total from bills join goods on bills.good_id=goods.id group by mon, good_id ) t1 where 3>( select count(*) from ( select bills.id, month(time) as mon, name, sum(cnt) as total from bills join goods on bills.good_id=goods.id group by mon, good_id ) t2 where t2.mon = t1.mon and t2.total > t1.total ) order by mon, total desc;
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
select 5,3,'2012-05-23',2 union all
select 6,5,'2012-05-27',1 union all
select 7,7,'2012-05-27',2 union all
select 13,3,'2012-06-01',1 union all
select 12,2,'2012-06-02',6 union all
select 10,6,'2012-06-21',3 union all
select 8,7,'2012-06-21',2 union all
select 9,7,'2012-06-22',2 union all
select 11,1,'2012-06-28',5 union all
select 14,2,'2012-07-01',2 union all
select 15,5,'2012-07-01',22 union all
select 16,4,'2012-07-21',27 union all
select 17,6,'2012-07-21',1 union all
select 18,7,'2012-07-21',1 union all
select 19,3,'2014-07-22',5
goselect t1.id ,t1.name ,t1.cnt,t1.月份 from
(
select t.id ,t.name ,t.cnt,t.月份,ROW_NUMBER() over (partition by t.月份 order by t.cnt desc) as rn1 from
(
select a.id ,b.name ,a.cnt,Rtrim(year(a.time))+'-'+Rtrim(Month(a.time)) as 月份 ,ROW_NUMBER () over ( partition by Rtrim(year(a.time))+'-'+Rtrim(Month(a.time)) ,b.name order by cnt desc) as rn from bills a left join
goods b on a .good_id =b.id
)t
where t.rn=1
)t1 where rn1<=3--id name cnt 月份
--3 book 211 2012-5
--2 box 22 2012-5
--7 desk 2 2012-5
--12 pen 6 2012-6
--11 brush 5 2012-6
--10 book 3 2012-6
--16 box 27 2012-7
--15 oil 22 2012-7
--14 pen 2 2012-7
--19 pecil 5 2014-7
drop table if exists test;
create table test (
id int primary key,
cid int,
author varchar(30)
) engine=myisam;
insert into test values
(1,1,'test1'),
(2,1,'test1'),
(3,1,'test2'),
(4,1,'test2'),
(5,1,'test2'),
(6,1,'test3'),
(7,1,'test3'),
(8,1,'test3'),
(9,1,'test3'),
(10,2,'test11'),
(11,2,'test11'),
(12,2,'test22'),
(13,2,'test22'),
(14,2,'test22'),
(15,2,'test33'),
(16,2,'test33'),
(17,2,'test33'),
(18,2,'test33');
--
select * from (select cid,author,count(*) as number from test group by cid,author) a
where
2>(
select count(*)
from (select cid,author,count(*) as number from test group by cid,author) b
where a.cid=b.cid and a.number<b.number
)order by cid,number desc;例子 分组求前二
select bills.id, month(time) as mon, name, sum(cnt) as total from bills join goods on bills.good_id=goods.id group by mon, good_id
) t1
where 3>(
select count(*) from (
select bills.id, month(time) as mon, name, sum(cnt) as total from bills join goods on bills.good_id=goods.id group by mon, good_id
) t2 where t2.mon = t1.mon and t2.total > t1.total
) order by mon, total desc;