select a.*
from test a join
( select reportyear,max(reportmonth) as maxmonth
from test
group by reportyear
) b
on a.reportyear=b.reportyear and a.reportmonth=b.maxmonth
from test a join
( select reportyear,max(reportmonth) as maxmonth
from test
group by reportyear
) b
on a.reportyear=b.reportyear and a.reportmonth=b.maxmonth
inner join
(
select projectid,reportyear,max(reportmonth) as reportmonth from test
group by projectid,reportyear
) a
on test.projectid=a.projectid and test.reportyear=a.reportyear and test.reportmonth=a.reportmonth
[pkid] [int] IDENTITY (1, 1) NOT NULL ,
[projectId] [int] NULL ,
[reportYear] [int] NULL ,
[reportMonth] [int] NULL
) ON [PRIMARY]
GOinsert into test(projectId,reportYear,reportMonth) values(1,2004,2)
insert into test(projectId,reportYear,reportMonth) values(1,2004,5)
insert into test(projectId,reportYear,reportMonth) values(2,2004,6)
insert into test(projectId,reportYear,reportMonth) values(1,2004,8)
insert into test(projectId,reportYear,reportMonth) values(2,2004,2)
insert into test(projectId,reportYear,reportMonth) values(1,2003,8)
insert into test(projectId,reportYear,reportMonth) values(1,2003,10)
insert into test(projectId,reportYear,reportMonth) values(2,2004,5)
select test.pkid,test.projectId,a.reportYear,a.reportMonth from
(select reportyear,max(reportmonth)as reportmonth from test group by reportyear)a left join test
on test.reportYear=a.reportYear and test.reportMonth=a.reportMonth
GO
drop table test
----------test--------------
7 1 2003 10
4 1 2004 8
from test a,(select projectId,reportYear,max(reportMonth) reportMonth
from test group by projectId,reportYear) b
where a.projectId=b.projectId and a.reportYear=b.reportYear
and a.reportMonth=b.reportMonth
order by a.projectId
能不能告诉我,用标准SQL语句怎样写????
from (select max(pkid) as pkid,projectID,reportYear from test group by projectID,reportYear) a
,test b where b.pkid=a.pkid and b.projectid=a.projectid and b.reportYear=a.reportYear
-----------------------------
pkid a b c d e f g h i j k
1 2 3 4 5 0 0 0 0 0 0 0
2 4 3 0 0 0 0 0 0 0 0 0
3 7 6 8 0 0 0 0 0 0 0 0
-----------------------------
返回的结果集为
-----------------------------
pkid a b c d e f g h i j k
1 2 5 9 14 0 0 0 0 0 0 0
2 4 7 0 0 0 0 0 0 0 0 0
3 7 13 21 0 0 0 0 0 0 0 0
-----------------------------
意思是说将b=a+b,c=a+b+c,....但当到为零是就不向下总和,
这样的SQL语句怎样写啊
(case when b=0 then 0 else a+b end) b,
(case when c=0 then 0 else a+b+c end) c,
(case when d=0 then 0 else a+b+c+d end) d,
(case when e=0 then 0 else a+b+c+d+e end) e,
(case when f=0 then 0 else a+b+c+d+e+f end) f,
(case when g=0 then 0 else a+b+c+d+e+f+g end) g,
(case when h=0 then 0 else a+b+c+d+e+f+g+h end) h,
(case when i=0 then 0 else a+b+c+d+e+f+g+h+i end) i,
(case when g=0 then 0 else a+b+c+d+e+f+g+h+i+j end) j,
(case when k=0 then 0 else a+b+c+d+e+f+g+h+i+j+k end) k
from tab