各位高手,现在我有个需求,要用sql语句写出来,我用的是sql sever 2005
要求:是查询出最近5年的数据,分为两类显示,最远的那一年(2007)是当年的数据,下一年要加上,上一年的数据,以此类推。表里有日期列inputdate,要按日期分组。
例子:2007 a=1,b=2;2008 a=1,b=1;2009 a=2,b=3;2010 a=2,b=2;2011 a=2,b=2
year a b
2007 1 2
2008 2 3
2009 5 8
2010 7 10
2011 9 12有没有好的办法呢?
要求:是查询出最近5年的数据,分为两类显示,最远的那一年(2007)是当年的数据,下一年要加上,上一年的数据,以此类推。表里有日期列inputdate,要按日期分组。
例子:2007 a=1,b=2;2008 a=1,b=1;2009 a=2,b=3;2010 a=2,b=2;2011 a=2,b=2
year a b
2007 1 2
2008 2 3
2009 5 8
2010 7 10
2011 9 12有没有好的办法呢?
Create Table Tb
(Year_,a,b)
insert into Tb
Select '2007',1,1 Union All
Select '2008',1,1 Union All
Select '2009',2,3 Union All
Select '2010',2,2 Union All
Select '2011',2,2Select Years,(Select Sum(a) From TB a Where a.Years<=B.Years) As d,
(Select Sum(b) From TB c Where c.Years<=B.Years) As s From TB b
(Years int,a int ,b int)
insert into Tb
Select '2007',1,1 Union All
Select '2008',1,1 Union All
Select '2009',2,3 Union All
Select '2010',2,2 Union All
Select '2011',2,2Select Years,(Select Sum(a) From TB a Where a.Years<=B.Years) As d,
(Select Sum(b) From TB c Where c.Years<=B.Years) As s From TB b/*
2007 1 1
2008 2 2
2009 4 5
2010 6 7
2011 8 9
*/刚才搞错了字段,我不会像大牛那样把结果弄出来
但是这应该是你要的结果
学习
select y.[years],(select SUM(a) from Tb x where x.Years<=y.Years ) as a,(select SUM(b) from Tb x where x.Years<=y.Years) as b from Tb y
高手问你下,你的B.years是什么意思?
就是TB表的years字段啊,把TB表给个名字(B,我写的时候没区分大小写而已)
declare @table table (inputdate int,a int,b int)
insert into @table
select 2007,1,2 union all
select 2008,1,1 union all
select 2009,2,3 union all
select 2010,2,2 union all
select 2011,2,2;WITH maco
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY inputdate ) AS rowid ,
*
FROM @table
)
SELECT c.inputdate ,
CASE WHEN c.inputdate - 1 > d.inputdate THEN c.a + d.a
ELSE c.a
END AS a,
CASE WHEN c.inputdate - 1 > d.inputdate THEN c.b + d.b
ELSE c.b
END AS b
FROM ( SELECT inputdate ,
( SELECT SUM(a)
FROM maco
WHERE inputdate < a.inputdate
OR inputdate = a.inputdate
) AS a ,
( SELECT SUM(b)
FROM maco
WHERE inputdate < a.inputdate
OR inputdate = a.inputdate
) AS b
FROM maco a
) c
CROSS JOIN ( SELECT *
FROM maco
WHERE inputdate = 2007
) d
/*
inputdate a b
----------- ----------- -----------
2007 1 2
2008 2 3
2009 5 8
2010 7 10
2011 9 12
*/
例子:2007 a=1,b=2;2008 a=1,b=1;2009 a=2,b=3;2010 a=2,b=2;2011 a=2,b=2
fzrq 生产 使用
2007 1 2
2008 2 3
2009 5 8
2010 7 10
2011 9 12逐年递增,最后一年(2011)是前面4年加上2011年的所有数据。
要按时间分组,按公司类型分类。
例子:2007 a=1,b=2;2008 a=1,b=1;2009 a=2,b=3;2010 a=2,b=2;2011 a=2,b=2
这个是你想要的结果是吧
declare @table table (fzrq int,a int,b int)
insert into @table
select 2007,1,2 union all
select 2008,1,1 union all
select 2009,2,3 union all
select 2010,2,2 union all
select 2011,2,2
--得到最小的年限
DECLARE @minyear INT
SELECT @minyear=MIN(fzrq ) FROM @table;WITH maco
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY fzrq ) AS rowid ,
* FROM @table)
SELECT c.fzrq ,
CASE WHEN c.fzrq - 1 > d.fzrq THEN c.a + d.a ELSE c.a END AS a,
CASE WHEN c.fzrq - 1 > d.fzrq THEN c.b + d.b ELSE c.b END AS b
FROM ( SELECT fzrq ,
( SELECT SUM(a)
FROM maco
WHERE fzrq < a.fzrq
OR fzrq = a.fzrq
) AS a ,
( SELECT SUM(b)
FROM maco
WHERE fzrq < a.fzrq
OR fzrq = a.fzrq
) AS b
FROM maco a
) c
CROSS JOIN ( SELECT * FROM maco
WHERE fzrq = @minyear
) d
/*
fzrq a b
----------- ----------- -----------
2007 1 2
2008 2 3
2009 5 8
2010 7 10
2011 9 12
*/这样改了下,你试试
drop table tb
drop table #tb
create table tb
(
product int,
used int,
fzrq datetime
)
create table #tb
(
totalproduct int,
totalused int,
fzrq datetime
)
insert into tb values(1,2,'2007-01-01')
insert into tb values(1,1,'2008-01-01')
insert into tb values(2,3,'2009-01-01')
insert into tb values(2,2,'2010-01-01')
insert into tb values(2,2,'2011-01-01')select * from tbDECLARE @totalproduct int
DECLARE @totalused int
DECLARE @product int
DECLARE @used int
DECLARE @fzrq datetime
set @totalproduct=0
set @totalused=0DECLARE my_Cursor Cursor FOR
SELECT product,used,fzrq
FROM tb
order by fzrq
OPEN my_Cursor
FETCH NEXT FROM my_Cursor
into @product,@used,@fzrq
WHILE @@FETCH_STATUS = 0
begin
set @totalproduct=@totalproduct+@product
set @totalused=@totalused+@used
insert into #tb values(@totalproduct,@totalused,@fzrq)
FETCH NEXT FROM my_Cursor into @product,@used,@fzrq
end
CLOSE my_Cursor
DEALLOCATE my_Cursor select * from #tb
Select
datepart(year,b.fzrq),
(Select count(comtype) From lacompany a Where comtype='生产' and datepart(year,a.fzrq)<=datepart(year,b.fzrq)) As ddddd,
(Select count(comtype) From lacompany c Where comtype='使用' and datepart(year,c.fzrq)<=datepart(year,b.fzrq)) As sssss
From lacompany b
where datediff(year,fzrq,getdate())<5
group by datepart(year,b.fzrq)我是这么写的,得出来的数据是:
years ddddd sssss
2007 31 95
2008 33 118
2009 49 144
2010 52 156
2011 60 171
这个数据是以2007年为基础,把库里的所有数据都给显示出来了,可是库里还有06年03年等等的数据呢,怎么能把2007年以前的数据屏蔽了呢?还有要是实际年又增加了一年(2012年),那能把2007年的数据屏蔽了吗?
结果保存于临时表 #tbdrop table tb
drop table #tb
create table tb
(
product int,
used int,
fzrq datetime
)
create table #tb
(
totalproduct int,
totalused int,
fzrq int
)
insert into tb values(1,4,'2003-01-01')
insert into tb values(2,2,'2006-01-01')
insert into tb values(1,2,'2007-01-01')
insert into tb values(1,1,'2008-01-01')
insert into tb values(2,3,'2009-01-01')
insert into tb values(2,2,'2010-01-01')
insert into tb values(2,2,'2011-01-01')DECLARE @lastyear int
set @lastyear=year(getdate())-5
DECLARE @totalproduct int
DECLARE @totalused int
DECLARE @product int
DECLARE @used int
DECLARE @fzrq datetime
set @totalproduct=0
set @totalused=0DECLARE my_Cursor Cursor FOR
SELECT product,used,fzrq
FROM tb
where year(fzrq)>@lastyear
order by fzrq
OPEN my_Cursor
FETCH NEXT FROM my_Cursor
into @product,@used,@fzrq
WHILE @@FETCH_STATUS = 0
begin
set @totalproduct=@totalproduct+@product
set @totalused=@totalused+@used
insert into #tb values(@totalproduct,@totalused,year(@fzrq))
FETCH NEXT FROM my_Cursor into @product,@used,@fzrq
end
CLOSE my_Cursor
DEALLOCATE my_Cursor select * from #tb
Select
year(yearb.fzrq),
(Select count(comtype) From lacompany a Where comtype='生产' and year(a.fzrq)<=year(b.fzrq) and year(getdate())-year(b.fzrq)<5) As ddddd,
(Select count(comtype) From lacompany c Where comtype='使用' and year(c.fzrq)<=year(b.fzrq) and year(getdate())-year(b.fzrq)<5) As sssss
From lacompany b
group by year(b.fzrq)
Select
year(b.fzrq),
(Select count(comtype) From lacompany a Where comtype='生产' and year(a.fzrq)<=year(b.fzrq) and year(getdate())-year(b.fzrq)<5) As ddddd,
(Select count(comtype) From lacompany c Where comtype='使用' and year(c.fzrq)<=year(b.fzrq) and year(getdate())-year(b.fzrq)<5) As sssss
From lacompany b
group by year(b.fzrq)
Create Table Tb
([Year] int,a int,b int)
insert into Tb
Select '2007',1,2 Union All
Select '2008',1,1 Union All
Select '2009',2,3 Union All
Select '2010',2,2 Union All
Select '2011',2,2gowith ABC(i,j,k,l,m)
as
(
select MAX([Year]),(SELECT top 1 a from tb order by [Year] desc),SUM(a),(SELECT top 1 b from tb order by [Year] desc),SUM(b) from tb
union all
select ([Year]),a,k - (j),b,m - (l) from tb inner join ABC ON [Year] = i - 1
)
select i,k,m from ABC drop table tb
适用于我的sql:
Select
datepart(year,b.fzrq) as years,
(Select count(fl) From animal_xkz a Where fl='生产' and datepart(year,a.fzrq)<=datepart(year,b.fzrq) and datediff(year,a.fzrq,getdate())<=5) As sc,
(Select count(fl) From animal_xkz c Where fl='使用' and datepart(year,c.fzrq)<=datepart(year,b.fzrq) and datediff(year,c.fzrq,getdate())<=5) As sy
From animal_xkz b
where datediff(year,b.fzrq,getdate())<=5
group by datepart(year,b.fzrq);