34,590
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(c1 varchar(10),
c2 datetime,
c3 int,
c4 varchar(10)
)
go
insert into tb
select 'A1','2015-09-01',10,'张三' union all
select 'A1','2015-09-10',10,'张三' union all
select 'A1','2015-09-13',20,'李四' union all
select 'A1','2015-09-20',10,'李四'
go
select * from tb a where not exists(select 1 from tb where c1=a.c1 and CONVERT(varchar(6),c2,112)=CONVERT(varchar(6),a.c2,112) and c2>a.c2)
WITH t(商品代码, 采购日期,采购数量,采购员) AS (
select 'A1','2015-09-01' ,10 , '张三' union all
select 'A1','2015-09-10' ,10 , '张三' union all
select 'A1','2015-09-13' ,20 , '李四' union all
select 'A1','2015-09-13' ,20 , '李四' union all
select 'A2','2015-09-20' ,10 , '李四' union all
select 'A1','2015-08-20' ,10 , '李四' union all
select 'A2','2015-08-20' ,10 , '张三'
)
,tt as (
select
CONVERT(varchar(7),采购日期,120) 采购月份, 商品代码
,SUM(采购数量) 采购总量
from t
group by CONVERT(varchar(7),采购日期,120), 商品代码
)
select tt.* ,ttt.采购员
from tt
cross apply (select top 1 t.采购员 from t where t.商品代码=tt.商品代码 and CONVERT(varchar(7),采购日期,120)=tt.采购月份 order by t.采购日期 desc) ttt