34,590
社区成员
发帖
与我相关
我的任务
分享
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
单据号 int,
hyid char(4),
ypjine int,
riqi datetime
)
go
--插入测试数据
insert into tb select 1,'005',10,'2011-03-10'
union all select 1,'005',5,'2011-03-15'
union all select 2,'001',30,'2011-03-18'
union all select 2,'001',8,'2011-03-27'
go
--代码实现
select *
from (select 单据号,hyid,ypjine=sum(ypjine),riqi=convert(varchar(7),riqi,120)
from tb
group by 单据号,hyid,convert(varchar(7),riqi,120)
)t
where riqi=convert(varchar(7),dateadd(m,-1,getdate()),120) and ypjine>36
/*测试结果
单据号 hyid ypjine riqi
------------------------------
2 001 38 2011-03
(1 行受影响)
*/
create table #temp
(
单据号 int,
hyid varchar(10),
ypjine int,
riqi datetime
)
insert #temp
select '1', '005', '10', '2011-03-10' union all
select '1', '005', '5', '2011-03-15' union all
select '2', '001', '30', '2011-03-18' union all
select '2', '001', '8', '2011-03-27'
go
--SQL:
SELECT 单据号, hyid, ypjine = sum(ypjine), riqi=CONVERT(varchar(7), riqi, 120)
FROM #temp
where CONVERT(varchar(7), riqi, 120) = CONVERT(varchar(7), DATEADD(month, -1, getdate()), 120)
and hyid = '001'
group by 单据号, hyid, CONVERT(varchar(7), riqi, 120)
having sum(ypjine) > 36
/*
单据号 hyid ypjine riqi
2 001 38 2011-03
*/
select 单据号,hyid,sum(ypjine) as ypjine,convert(varchar(7),riqi,120) as riqi
from tb
group by 单据号,hyid,convert(varchar(7),riqi,120)