这样的Sql语句怎么写(SQL Server)？

2007-06-12 07:35:37 234 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL Server基础Sql语句复习 1.创建表 create table Course( Cno char(4) primary key not null, --创建主键，非空 Cname char(40) not null, Cpno char(4), Ccredit smallint, primary key(Cno,Cname), --双主键 foreign key(Cpno) references Course(Cno) --外键连接Coures表的（Cno列） ) 2插入语句 --添加数据 select * from St 阅读详情

做项目用的数据库是SQL Server，在这过程中碰到这么一个问题：
数据库中主要有三列：
Year Month Je
2007 1 100
2007 2 200
2007 3 300
. . .
. . .
. . .
2008 1 1000
2008 2 2000
. . .
现在想得到各个月的累积数。
如现在是2007年6月，我想得到的是2007-1到2007-6月的累积。
需要的结果如下：
Year Month Je
2007 1 100
2007 2 300
2007 3 600
. . .
. . .
. . .
2008 1 1000
2008 2 3000
. . .
这样的Sql 语句应该怎样写，请各位大虾帮忙，一旦解决，立即给分。

...全文

EMQX 结合MySQL实现精细化ACL权限管理实战本文详细介绍了如何通过EMQX结合MySQL实现精细化的ACL权限管理，提升物联网设备接入和消息传输的安全性。内容包括环境准备、基础配置、权限规则设计技巧以及高级应用与性能优化方案，帮助开发者快速掌握动态生效、便于管理的权限控制方法。阅读详情

fireswolfs

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

fireswolfs 2007-06-20

打赏
举报

最后使用了SQL SERVER中的Case,问题解决:
CREATE VIEW view_Pf_Budget/*建立外连接，获得累积批复预算*/
AS
SELECT TOP 100 PERCENT
a.Nf as Year,a.Yf as Month,a.DwId,a.DwNm,a.FyId,a.FyNm,a.FyAllId,a.FyAllNM,
case a. Yf
when '1' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 1 ))
when ' 2' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 2 ))
when '3' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 3 ))
when '4' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 4 ))
when '5' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 5 ))
when '6' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 6 ))
when '7' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 7 ))
when '8' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 8 ))
when '9' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 9 ))
when '10' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 10 ))
when '11' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 11 ))
when '12' then (select sum(IsNull(Je,0)) as Je from view_last_monthbudget_Je b where a.Nf=b.Nf and a.DwId=b.DwId and a.FyId=b.FyId and (b.Yf between 1 and 12 ))
end as Pf_Budget
from view_last_monthbudget_Je a
order by a.Nf,a.Yf, a.DwId,a.FyId

lang8134 2007-06-13

打赏
举报

select year,month,sum(je) group by year,month
是这个意思吗?
注:楼主上面的和下面想要的结果好象是一模一样的,...

ojuju10 2007-06-13

打赏
举报

create table tbl(
syear varchar(4),
smonth varchar(2),
je int
)
insert into tbl
select '2007','1',100 union all
select '2007','2',200 union all
select '2007','3',300 union all
select '2007','4',500 union all
select '2008','1',1000 union all
select '2008','2',2000
go

select syear, smonth,(select sum(je) from tbl where a.syear=syear and a.smonth>=smonth) from tbl a

Zack999 2007-06-13

打赏
举报

create table test(
syear varchar(4),
smonth varchar(2),
je int
)
insert into test
select '2007','1',100 union all
select '2007','2',200 union all
select '2007','3',300 union all
select '2007','4',500 union all
select '2008','1',1000 union all
select '2008','2',2000
go

create view TestView as
select syear,smonth,(select sum(je) from test as b where a.syear=b.syear and b.smonth<=a.smonth) as je from test as a
go

select * from TestView

drop table test
drop view TestView

fireswolfs 2007-06-12

打赏
举报

忘了注明：这里给的是表中的数据，需要得到的是写一个视图：这个视图对Je进行累积。

这样的Sql语句怎么写(SQL Server)？

相关推荐