22,210
社区成员
发帖
与我相关
我的任务
分享
用户ID 交易ID 交易值
userId jiaoyiID value fangxiang
20100608 20100625001 50 买
20100608 20100625002 20 卖
20111609 20100625004 28 卖
20100608 20100625003 30 卖
20100608 20100625005 39 卖
20100608 20100625006 15 买
20100608 20100625007 11 买
20111609 20100625008 28 买
20111608 20100625009 10 买
20111608 20100625010 3 买
--结果要:
userId 平掉数量 买jiaoyiID 买value 卖jiaoyiID 卖value
20100608 20 20100625001 50 20100625002 20
20111609 28 20100625008 28 20100625004 28
20100608 30 20100625001 50 20100625002 30
20100608 15 20100625006 39 20100625005 15
20111608 11 20100625007 39 20100625005 11
20111608 10 20100625009 39 20100625005 10
20111608 3 20100625010 39 20100625005 3
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(userId int, jiaoyiID bigint, value int, fangxiang varchar(8))
insert into #
select 20100608, 20100625001, 50, '买' union all
select 20100608, 20100625002, 20, '卖' union all
select 20111609, 20100625004, 28, '卖' union all
select 20100608, 20100625003, 30, '卖' union all
select 20100608, 20100625005, 39, '卖' union all
select 20100608, 20100625006, 15, '买' union all
select 20100608, 20100625007, 11, '买' union all
select 20111609, 20100625008, 28, '买' union all
select 20111608, 20100625009, 10, '买' union all
select 20111608, 20100625010, 3, '买'
;with i as
(
select *, (select sum(value) from # where fangxiang='买' and jiaoyiID<=t.jiaoyiID)si from # t where fangxiang='买'
),
o as
(
select *, (select sum(value) from # where fangxiang='卖' and jiaoyiID<=t.jiaoyiID)so from # t where fangxiang='卖'
)
select i.userId, 平掉数量=
case
when (si-i.value)<=(so-o.value) then
case
when si<=so then si-(so-o.value)
else o.value
end
else
case
when si<=so then i.value
else so-(si-i.value)
end
end,
[买.jiaoyiID] = i.jiaoyiID,
[买.value] = i.value,
[卖.jiaoyiID] = o.jiaoyiID,
[卖.value] = o.value
from i,o where (si>so and si-so<i.value) or (si<=so and so-si<o.value) order by 3
/*
userId 平掉数量 买.jiaoyiID 买.value 卖.jiaoyiID 卖.value
----------- ----------- -------------------- ----------- -------------------- -----------
20100608 20 20100625001 50 20100625002 20
20100608 30 20100625001 50 20100625003 30
20100608 15 20100625006 15 20100625004 28
20100608 11 20100625007 11 20100625004 28
20111609 2 20100625008 28 20100625004 28
20111609 26 20100625008 28 20100625005 39
20111608 10 20100625009 10 20100625005 39
20111608 3 20100625010 3 20100625005 39
*/