按人汇总与聚合的SQL,请教

Question

如下的一个情况，统计销售信息。 user--销售名字     amount--销售量   date                customer-客户名
 
sales1              200             2011-07-4            customer1sales3              128             2011-03-9             customeraasales8              1001             2011-04-11           customer5sales2              500             2011-06-14           customer5
sales3              300             2011-04-19           customer6sales8              450             2011-04-21          customer15sales2              800             2011-07-13          customer8
sales3              640             2011-02-09          customer5
sales8              140             2011-04-15          customer3
1. 求每个月，按人（sales)汇总，其销售额（amount)最高的那条销售信息。（user/amount/date/customer)比如四月，应该结果是：sales3              300             2011-04-19           customer6sales8              1001            2011-04-11           customer5
2. 当月，汇总每个sales,最后一条销售记录。（user/amount/date/customer)
比如四月，应该结果是： 
 
sales3              300             2011-04-19           customer6sales8              450             2011-04-21           customer15
求情况1，与情况2，的SQL应该怎么写？ 

Answer 1

1:
select * from tb a
 where not exists(select 1 from tb where user=a.user and amount>a.amount)
2:
select * from tb a
 where not exists(select 1 from tb where user=a.user and date>a.date)

Answer 2

1.
select * from tb a
where not exists
(select 1 from tb b where a.[user]=b.[user] 
 and b.amount>a.amount 
 and month(a.date)=month(b.date))

Answer 3

create table tb([user] nvarchar(10),amount int,date datetime,customer nvarchar(10))
insert into tb select 'sales1',200,'2011-07-4','customer1'
insert into tb select 'sales3',128,'2011-03-9','customeraa'
insert into tb select 'sales8',1001,'2011-04-11','customer5'
insert into tb select 'sales2',500,'2011-06-14','customer5'
insert into tb select 'sales3',300,'2011-04-19','customer6'
insert into tb select 'sales8',450,'2011-04-21','customer15'
insert into tb select 'sales2',800,'2011-07-13','customer8'
insert into tb select 'sales3',640,'2011-02-09','customer5'
insert into tb select 'sales8',140,'2011-04-15','customer3'
go
select * from tb a where not exists(
select 1 from tb where [user]=a.[user] and convert(varchar(7),date,120)=convert(varchar(7),a.date,120) and amount>a.amount
)
/*
user       amount      date                    customer
---------- ----------- ----------------------- ----------
sales1     200         2011-07-04 00:00:00.000 customer1
sales3     128         2011-03-09 00:00:00.000 customeraa
sales8     1001        2011-04-11 00:00:00.000 customer5
sales2     500         2011-06-14 00:00:00.000 customer5
sales3     300         2011-04-19 00:00:00.000 customer6
sales2     800         2011-07-13 00:00:00.000 customer8
sales3     640         2011-02-09 00:00:00.000 customer5(7 行受影响)
*/
select * from tb a where not exists(
select 1 from tb where [user]=a.[user] and convert(varchar(7),date,120)=convert(varchar(7),a.date,120) and date>a.date
)
/*
user       amount      date                    customer
---------- ----------- ----------------------- ----------
sales1     200         2011-07-04 00:00:00.000 customer1
sales3     128         2011-03-09 00:00:00.000 customeraa
sales2     500         2011-06-14 00:00:00.000 customer5
sales3     300         2011-04-19 00:00:00.000 customer6
sales8     450         2011-04-21 00:00:00.000 customer15
sales2     800         2011-07-13 00:00:00.000 customer8
sales3     640         2011-02-09 00:00:00.000 customer5(7 行受影响)
*/
go
drop table tb

Answer 4

--2.
select * from tb a
where not exists
(select 1 from tb b where a.[user]=b.[user] 
 and b.date>a.date 
 and month(a.date)=month(b.date))

Answer 5

记得加条件,帖子上方管理有移动帖子，请移动到mssql 基础类，谢谢
select * from tb a
 where not exists(select 1 from tb where user=a.user and amount>a.amount)
 and month(date)=4

Answer 6

select
   *
from
  tb t
where
  amount=(select min(amount) from tb where user=t.user)
and
  month(date)=4