按月查询

[byzcm]

我有个表
DailyReport(OrderID,OrderPrice,OrderDate)
OrderID:订单编号；OrderPrice:订单金额；OrderDate:订单日期
1001 100 2004-01-01
1002 300 2004-01-02
1003 200 2004-01-03
1004 600 2004-01-04
1005 300 2004-02-01
1006 500 2004-02-02
1007 200 2004-02-04
1008 600 2004-02-09
查询按月订单金额最大的３条记录
想得到的结果
OrderID OrderPrice OrderDate
1004 600 2004-01-04
1002 300 2004-01-02
1003 200 2004-01-03
1008 600 2004-02-09
1006 500 2004-02-02
1005 300 2004-02-01

[dulei115]

select *
from DailyReport a
where OrderId in (select top 3 OrderId from DailyReport
where convert(varchar(7), OrderDate, 120) =
convert(varchar(7), a.OrderDate, 120)
order by OrderPrice desc)

[xiaomeixiang]

select * from DailyReport a where
(select count(*) from DailyReport where year(OrderDate)=year(OrderDate) and month(OrderDate)=month(a.OrderDate) and OrderPrice>a.OrderPrice )<3

[dulei115]

当消费金额各不相同时才能用这个：
select * from DailyReport a where exists
(select count(*) from DailyReport where year(OrderDate)=year(a.OrderDate) and month(OrderDate)=month(a.OrderDate) and OrderPrice>a.OrderPrice )<3

[子陌红尘]

select a.* from DailyReport a where a.OrderPrice in
(select top 3 OrderPrice from DailyReport where convert(varchar(7),OrderDate,120) = convert(varchar(7),a.OrderDate,120) order by OrderPrice desc)
order by order by convert(varchar(7),a.OrderDate,120),OrderPrice desc

[LBYYBL]

create table DailyReport(OrderID char(4),OrderPrice int,OrderDate datetime)
insert into DailyReport select '1001', 100 ,'2004-01-01'
insert into DailyReport select '1002', 300 ,'2004-01-02'
insert into DailyReport select '1003', 200 ,'2004-01-03'
insert into DailyReport select '1004', 600 ,'2004-01-04'
insert into DailyReport select '1005', 300 ,'2004-02-01'
insert into DailyReport select '1006', 500 ,'2004-02-02'
insert into DailyReport select '1007', 200 ,'2004-02-04'
insert into DailyReport select '1008', 600 ,'2004-02-09'

select * from DailyReport a where (select count(1) from DailyReport where month(a.OrderDate)=month(OrderDate) and a.OrderPrice<OrderPrice)<3
order by OrderDate desc,OrderPrice desc

drop table DailyReport

OrderID OrderPrice OrderDate
------- ----------- ------------------------------------------------------
1008 600 2004-02-09 00:00:00.000
1006 500 2004-02-02 00:00:00.000
1005 300 2004-02-01 00:00:00.000
1004 600 2004-01-04 00:00:00.000
1003 200 2004-01-03 00:00:00.000
1002 300 2004-01-02 00:00:00.000

（所影响的行数为 6 行）

[vinsonshen]

select * from DailyReport as a where (select count(*) from DailyReport where month(OrderDate)=a.month(OrderDate) and OrderPrice>=a.OrderPrice)<=3

[vinsonshen]

若你的年份不止一年，而又要根据分月来统计，则用：
select * from DailyReport as a where (select count(*) from DailyReport where left(convert(varchar(10),OrderDate,120),7)=left(convert(varchar(10),a.OrderDate,120),7) and OrderPrice>=a.OrderPrice)<=3 order by left(convert(varchar(10),a.OrderDate,120),7) asc,a.OrderPrice desc

[lin_li_00]

同意楼上

但是如果记录数比较大时，以上语句效果不会很理想，因为对DailyReport中每条记录都需要统计一次DailyReport。

: )