select *, lastyearTotal=isnull((select sum(thisyearTotal) from tb where [year]<t.[year]),0) from tb t
create table temp(year1 int ,thisyearTotal int) insert into temp select 2001,100 union all select 2002,200 union all select 2003,300 goselect *, lastyearTotal=isnull((select sum(thisyearTotal) from temp where year1<t.year1),0) from temp tyear1 thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 300(所影响的行数为 3 行)
SELECT [year], thisyearTotal, (SELECT ISNULL(thisyearTotal,0) FROM TB WHERE [year]=T.[YEAR]-1)AS lastyearTotal FROM TB T??
ALTER TABLE temp ADD lastyearTotal int
select A.[year], A.[thisyearTotal], [lastyearTotal]=isnull(B.[thisyearTotal],0) from temp A left join temp B on A.[year]=B.[year]+1
select [year], thisyearTotal, lastyearTotal=case when [year]=(select MIN([year]) from tb ) then 0 else (select top 1 thisyearTotal from tb where [year]<k.[year] order by [year] ) end from tb k
--> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([year] int,[thisyearTotal] int) Insert [tb] Select '2001',100 union all Select '2002',200 union all Select '2003',300 Go --Select * from [tb]-->SQL查询如下: --1. select *, [lastyearTotal]=isnull((select [thisyearTotal] from tb where [year]=t.[year]-1),0) from tb t--2. select a.*,ISNULL(b.[thisyearTotal],0) [lastyearTotal] from tb a left join tb b on a.[year]=b.[year]+1 /*year thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200(3 行受影响) */
create table tab(year varchar(4),total int) insert tab select '2000',100 union all select '2001',200 union all select '2002',300select T1.*,isnull(T2.total,0) from tab T1 left join tab T2 on T1.year=cast(cast(t2.year as int)+1 as varchar)drop table tab (3 行受影响) year total ---- ----------- ----------- 2000 100 0 2001 200 100 2002 300 200(3 行受影响)
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([year] int,[thisyearTotal] int) insert [TB] select 2001,100 union all select 2002,200 union all select 2003,300select *,lastyeartatle=isnull((select top 1 [thisyearTotal] from TB where t.year>year order by [year] desc),0) from TB t/* year thisyearTotal lastyeartatle ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200(3 行受影响)*/drop table TB
create table tb(year int,thisyearTotal int) insert into tb values(2001 , 100) insert into tb values(2002 , 200) insert into tb values(2003 , 300) go select t.*,lastyearTotal=isnull((select top 1 thisyearTotal from tb where year < t.year order by year desc),0) from tb t drop table tb/* year thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200(所影响的行数为 3 行) */
create table temp(year int ,thisyearTotal int) insert into temp select 2001,100 union all select 2002,200 union all select 2003,300 go select [year], thisyearTotal, lastyearTotal=case when [year]=(select MIN([year]) from temp ) then 0 else (select top 1 thisyearTotal from temp where [year]<k.[year] order by [year] desc ) end from temp kyear thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200
create table TB(year1 int ,thisyearTotal int) insert into TB select 2001,100 union all select 2002,200 union all select 2003,300 go--DROP TABLE TBSELECT [year1], thisyearTotal, ISNULL((SELECT thisyearTotal FROM TB WHERE [year1]=T.[YEAR1]-1),0)AS lastyearTotal FROM TB Tyear1 thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200(所影响的行数为 3 行) 修改一下
create table temp(year1 int ,thisyearTotal int) insert into temp select 2001,100 union all select 2002,200 union all select 2003,300 goselect *, lastyearTotal=isnull((select thisyearTotal from temp where year1 = t.year1-1),'0') from temp tyear1 thisyearTotal lastyearTotal ----------- ------------- ------------- 2001 100 0 2002 200 100 2003 300 200(所影响的行数为 3 行)
IF OBJECT_ID('TEMP') IS NOT NULL DROP TABLE TEMP GO CREATE TABLE TEMP([YEAR] INT,thisyearTotal INT) INSERT INTO TEMP select 2001,100 union all select 2002,200 union all select 2003,300 go SELECT T1.[YEAR],T1.THISYEARTOTAL,ISNULL(T2.THISYEARTOTAL,0) FROM TEMP T1 OUTER APPLY( SELECT TOP 1 T2.[YEAR],T2.THISYEARTOTAL FROM TEMP T2 WHERE T2.[YEAR]<T1.[YEAR] ORDER BY [YEAR] DESC ) T2 /* 2001 100 0 2002 200 100 2003 300 200 */
drop table tb create table tb(year int,thisyearTotal int) insert into tb values(2001 , 100) insert into tb values(2002 , 200) insert into tb values(2003 , 300) goselect year,thisyearTotal,thisyearTotal-100 as lastyearTotal from tb
select *, lastyearTotal=isnull((select thisyearTotal from tb where year = t.year-1),'0') from tb t
*,
lastyearTotal=isnull((select sum(thisyearTotal) from tb where [year]<t.[year]),0)
from
tb t
create table temp(year1 int ,thisyearTotal int)
insert into temp
select 2001,100 union all
select 2002,200 union all
select 2003,300
goselect
*,
lastyearTotal=isnull((select sum(thisyearTotal) from temp where year1<t.year1),0)
from
temp tyear1 thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 300(所影响的行数为 3 行)
(SELECT ISNULL(thisyearTotal,0) FROM TB WHERE [year]=T.[YEAR]-1)AS lastyearTotal
FROM TB T??
from temp A left join temp B
on A.[year]=B.[year]+1
[year],
thisyearTotal,
lastyearTotal=case when [year]=(select MIN([year]) from tb ) then 0
else (select top 1 thisyearTotal from tb where [year]<k.[year] order by [year] ) end
from tb k
Drop table [tb]
Go
Create table [tb]([year] int,[thisyearTotal] int)
Insert [tb]
Select '2001',100 union all
Select '2002',200 union all
Select '2003',300
Go
--Select * from [tb]-->SQL查询如下:
--1.
select *,
[lastyearTotal]=isnull((select [thisyearTotal] from tb where [year]=t.[year]-1),0)
from tb t--2.
select a.*,ISNULL(b.[thisyearTotal],0) [lastyearTotal]
from tb a
left join tb b
on a.[year]=b.[year]+1
/*year thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200(3 行受影响)
*/
insert tab
select '2000',100 union all
select '2001',200 union all
select '2002',300select T1.*,isnull(T2.total,0)
from tab T1 left join
tab T2 on T1.year=cast(cast(t2.year as int)+1 as varchar)drop table tab
(3 行受影响)
year total
---- ----------- -----------
2000 100 0
2001 200 100
2002 300 200(3 行受影响)
if object_id('[TB]') is not null drop table [TB]
create table [TB]([year] int,[thisyearTotal] int)
insert [TB]
select 2001,100 union all
select 2002,200 union all
select 2003,300select *,lastyeartatle=isnull((select top 1 [thisyearTotal] from TB where t.year>year order by [year] desc),0) from TB t/*
year thisyearTotal lastyeartatle
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200(3 行受影响)*/drop table TB
insert into tb values(2001 , 100)
insert into tb values(2002 , 200)
insert into tb values(2003 , 300)
go
select t.*,lastyearTotal=isnull((select top 1 thisyearTotal from tb where year < t.year order by year desc),0) from tb t
drop table tb/*
year thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200(所影响的行数为 3 行)
*/
insert into temp
select 2001,100 union all
select 2002,200 union all
select 2003,300
go
select
[year],
thisyearTotal,
lastyearTotal=case when [year]=(select MIN([year]) from temp ) then 0
else (select top 1 thisyearTotal from temp where [year]<k.[year] order by [year] desc ) end
from temp kyear thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200
insert into TB
select 2001,100 union all
select 2002,200 union all
select 2003,300
go--DROP TABLE TBSELECT [year1], thisyearTotal,
ISNULL((SELECT thisyearTotal FROM TB WHERE [year1]=T.[YEAR1]-1),0)AS lastyearTotal
FROM TB Tyear1 thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200(所影响的行数为 3 行)
修改一下
create table temp(year1 int ,thisyearTotal int)
insert into temp
select 2001,100 union all
select 2002,200 union all
select 2003,300
goselect
*,
lastyearTotal=isnull((select thisyearTotal from temp where year1 = t.year1-1),'0')
from
temp tyear1 thisyearTotal lastyearTotal
----------- ------------- -------------
2001 100 0
2002 200 100
2003 300 200(所影响的行数为 3 行)
IF OBJECT_ID('TEMP') IS NOT NULL DROP TABLE TEMP
GO
CREATE TABLE TEMP([YEAR] INT,thisyearTotal INT)
INSERT INTO TEMP
select 2001,100 union all
select 2002,200 union all
select 2003,300
go
SELECT T1.[YEAR],T1.THISYEARTOTAL,ISNULL(T2.THISYEARTOTAL,0)
FROM TEMP T1
OUTER APPLY(
SELECT TOP 1 T2.[YEAR],T2.THISYEARTOTAL
FROM TEMP T2 WHERE T2.[YEAR]<T1.[YEAR] ORDER BY [YEAR] DESC
) T2
/*
2001 100 0
2002 200 100
2003 300 200
*/
create table tb(year int,thisyearTotal int)
insert into tb values(2001 , 100)
insert into tb values(2002 , 200)
insert into tb values(2003 , 300)
goselect year,thisyearTotal,thisyearTotal-100 as lastyearTotal from tb
*,
lastyearTotal=isnull((select thisyearTotal from tb where year = t.year-1),'0')
from
tb t