declare @t table(Price money,[date] datetime,ID int) insert into @t select 135.50,'2005-01-15',1001 insert into @t select 256.78,'2004-11-24',1003 insert into @t select 205.05,'2005-01-24',1001 insert into @t select 175.42,'2005-02-07',1002 insert into @t select 116.94,'2004-11-24',1001 select ID,sum(case when [date]>'2005-01-01' then Price else -Price end) as Difference from @t group by ID order by ID/* ID Difference ------------------- 1001 223.61 1002 175.42 1003 -256.78 */
declare @t table(Price money,[date] datetime,ID int) insert into @t select 135.50,'2005-01-15',1001 insert into @t select 256.78,'2004-11-24',1003 insert into @t select 205.05,'2005-01-24',1001 insert into @t select 175.42,'2005-02-07',1002 insert into @t select 116.94,'2004-11-24',1001 select ID, sum(case when [date]>'2005-01-01' then Price when [date]>'2005-01-01' then -Price else 0 end) as Difference from @t group by ID order by ID/* ID Difference ------------------- 1001 223.61 1002 175.42 1003 -256.78 */
declare @t table(Price money,[date] datetime,ID int) insert into @t select 135.50,'2005-01-15',1001 insert into @t select 256.78,'2004-11-24',1003 insert into @t select 205.05,'2005-01-24',1001 insert into @t select 175.42,'2005-02-07',1002 insert into @t select 116.94,'2004-11-24',1001 select ID, sum(case when [date]>'2005-01-01' then Price when [date]<'2005-01-01' then -Price else 0 end) as Difference from @t group by ID order by ID/* ID Difference ------------------- 1001 223.61 1002 175.42 1003 -256.78 */
应该是-1*字段名吧select [ID], sum(case when [date] >= '2005-01-01' then Price when [date] < '2005-01-01' then -1 * isnull(Price,0) end ) as 差值 from tname group by [ID] order by [ID]
select ID,Differance=sum(Differance) from ( select ID,Differance=Price where [date]>'2005-01-01' unoin all select ID,Differance=-Price where [date]<='2005-01-01') tmp group by ID
那么如果有张相关表呢,比如将显示ID 改为显示 名称。 Table A: Price date ID 135.50 2005-01-15 1001 256.78 2004-11-24 1003 205.05 2005-01-24 1001 175.42 2005-02-07 1002 116.94 2004-11-24 1001 ... ... ...Table B: ID Name 1001 张三 1002 李四 1003 王五结果显示: Name Difference 张三 214.87 李四 196.23 王五 276.35 ... ...
select B.[name], sum(case when [date] >= '2005-01-01' then Price when [date] < '2005-01-01' then -1 * isnull(Price,0) end ) as 差值 from A inner join B on A.[ID]=B.[ID] group by [ID],B.[name] order by [ID],B.[name]
select B.[name], sum(case when [date] >= '2005-01-01' then Price when [date] < '2005-01-01' then -1 * isnull(Price,0) end ) as 差值 from A inner join B on A.[ID]=B.[ID] group by A.[ID],B.[name] order by A.[ID],B.[name]
insert into @t select 135.50,'2005-01-15',1001
insert into @t select 256.78,'2004-11-24',1003
insert into @t select 205.05,'2005-01-24',1001
insert into @t select 175.42,'2005-02-07',1002
insert into @t select 116.94,'2004-11-24',1001
select ID,sum(case when [date]>'2005-01-01' then Price else -Price end) as Difference
from @t group by ID order by ID/*
ID Difference
-------------------
1001 223.61
1002 175.42
1003 -256.78
*/
insert into @t select 135.50,'2005-01-15',1001
insert into @t select 256.78,'2004-11-24',1003
insert into @t select 205.05,'2005-01-24',1001
insert into @t select 175.42,'2005-02-07',1002
insert into @t select 116.94,'2004-11-24',1001
select
ID,
sum(case when [date]>'2005-01-01' then Price
when [date]>'2005-01-01' then -Price
else 0
end) as Difference
from @t group by ID order by ID/*
ID Difference
-------------------
1001 223.61
1002 175.42
1003 -256.78
*/
insert into @t select 135.50,'2005-01-15',1001
insert into @t select 256.78,'2004-11-24',1003
insert into @t select 205.05,'2005-01-24',1001
insert into @t select 175.42,'2005-02-07',1002
insert into @t select 116.94,'2004-11-24',1001
select
ID,
sum(case when [date]>'2005-01-01' then Price
when [date]<'2005-01-01' then -Price
else 0
end) as Difference
from @t group by ID order by ID/*
ID Difference
-------------------
1001 223.61
1002 175.42
1003 -256.78
*/
sum(case
when [date] >= '2005-01-01' then Price
when [date] < '2005-01-01' then -1 * isnull(Price,0) end
) as 差值
from tname
group by [ID]
order by [ID]
select ID,Differance=Price where [date]>'2005-01-01'
unoin all
select ID,Differance=-Price where [date]<='2005-01-01') tmp
group by ID
Table A: Price date ID
135.50 2005-01-15 1001
256.78 2004-11-24 1003
205.05 2005-01-24 1001
175.42 2005-02-07 1002
116.94 2004-11-24 1001
... ... ...Table B: ID Name
1001 张三
1002 李四
1003 王五结果显示: Name Difference
张三 214.87
李四 196.23
王五 276.35
... ...
sum(case
when [date] >= '2005-01-01' then Price
when [date] < '2005-01-01' then -1 * isnull(Price,0) end
) as 差值
from A inner join B on A.[ID]=B.[ID]
group by [ID],B.[name]
order by [ID],B.[name]
select B.[name],
sum(case
when [date] >= '2005-01-01' then Price
when [date] < '2005-01-01' then -1 * isnull(Price,0) end
) as 差值
from A inner join B on A.[ID]=B.[ID]
group by A.[ID],B.[name]
order by A.[ID],B.[name]