34,575
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb')is not null drop table tb
if object_id('tempdb..#tb')is not null drop table #tb
go
create table tb (客户号 int,证券代码 char(6),成交数量 int,证券余额 int,交易日期 date,交易时间 time)
go
insert into tb
select 1517016164,'000631',2300,2300,'20091123','18:02:36' union all
select 1517016164,'000631',-2300,0,'20091202','18:22:51' union all
select 1517016164,'002062',1500,1500,'20091113','19:38:05' union all
select 1517016164,'002062',-1500,0,'20091123','18:02:36' union all
select 1517016168,'000559',100,100,'20091123','18:02:36' union all
select 1517016168,'000559',11000,11100,'20091124','18:19:09' union all
select 1517016168,'000559',-500,10600,'20091204','18:07:11' union all
select 1517016168,'000559',-600,10000,'20100111','18:18:49' union all
select 1517016168,'000559',-5000,5000,'20100121','18:33:00' union all
select 1517016168,'000559',-4000,1000,'20100329','18:17:09' union all
select 1517016168,'000626',2500,2500,'20100420','17:54:39' union all
select 1517016168,'000626',-2500,0,'20100617','19:13:05' union all
select 1517016168,'000983',100,100,'20091118','17:58:28' union all
select 1517016168,'000983',-100,0,'20091124','18:19:09' union all
select 1517016168,'002062',1100,1100,'20091026','18:04:32' union all
select 1517016168,'002062',1800,2900,'20091026','18:04:32' union all
select 1517016168,'002062',1900,4800,'20091026','18:04:32' union all
select 1517016168,'002062',-4800,0,'20091109','17:48:36' union all
select 1517016168,'002091',1500,1500,'20090929','18:07:57' union all
select 1517016168,'002091',-1500,0,'20090930','17:49:08' union all
select 1517016168,'002181',8300,8300,'20091112','17:40:12' union all
select 1517016168,'002181',-8300,0,'20091113','19:38:05' union all
select 1517016168,'002200',3200,3200,'20091113','19:38:05' union all
select 1517016168,'002200',-100,3100,'20091117','17:45:04' union all
select 1517016168,'002200',-3100,0,'20091124','18:19:09' union all
select 1517016168,'002216',100,100,'20090512','18:35:09'
go
with cte as (
select row_number()over(order by 客户号)id,* from tb where not 证券余额=0)
select 客户号,max(交易日期) riqi into #tb from cte
group by 客户号
select tb.* from tb,#tb where tb.客户号=#tb.客户号 and tb.交易日期=#tb.riqi and not tb.证券余额=0
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(客户号 int, 证券代码 varchar(8), 成交数量 int, 证券余额 int, 交易日期 int, 交易时间 int)
insert into #
select 1517016164, '000631', 2300, 2300, '20091123', 180236 union all
select 1517016164, '000631', -2300, 0, '20091202', 182251 union all
select 1517016164, '002062', 1500, 1500, '20091113', 193805 union all
select 1517016164, '002062', -1500, 0, '20091123', 180236 union all
select 1517016168, '000559', 100, 100, '20091123', 180236 union all
select 1517016168, '000559', 11000, 11100, '20091124', 181909 union all
select 1517016168, '000559', -500, 10600, '20091204', 180711 union all
select 1517016168, '000559', -600, 10000, '20100111', 181849 union all
select 1517016168, '000559', -5000, 5000, '20100121', 183300 union all
select 1517016168, '000559', -4000, 1000, '20100329', 181709 union all
select 1517016168, '000626', 2500, 2500, '20100420', 175439 union all
select 1517016168, '000626', -2500, 0, '20100617', 191305 union all
select 1517016168, '000983', 100, 100, '20091118', 175828 union all
select 1517016168, '000983', -100, 0, '20091124', 181909 union all
select 1517016168, '002062', 1100, 1100, '20091026', 180432 union all
select 1517016168, '002062', 1800, 2900, '20091026', 180432 union all
select 1517016168, '002062', 1900, 4800, '20091026', 180432 union all
select 1517016168, '002062', -4800, 0, '20091109', 174836 union all
select 1517016168, '002091', 1500, 1500, '20090929', 180757 union all
select 1517016168, '002091', -1500, 0, '20090930', 174908 union all
select 1517016168, '002181', 8300, 8300, '20091112', 174012 union all
select 1517016168, '002181', -8300, 0, '20091113', 193805 union all
select 1517016168, '002200', 3200, 3200, '20091113', 193805 union all
select 1517016168, '002200', -100, 3100, '20091117', 174504 union all
select 1517016168, '002200', -3100, 0, '20091124', 181909 union all
select 1517016168, '002216', 100, 100, '20090512', 183509
select * from # t where 证券余额>0 and checksum(*) = (select top 1 checksum(*) from # where 客户号=t.客户号 and 证券代码=t.证券代码 order by 交易日期 desc, 交易时间 desc)
/*
客户号 证券代码 成交数量 证券余额 交易日期 交易时间
----------- -------- ----------- ----------- ----------- -----------
1517016168 000559 -4000 1000 20100329 181709
1517016168 002216 100 100 20090512 183509
*/
--可以实现,自己可以优化下
with cte as (
select 客户号,证券代码,成交数量,
case when 成交数量<0 then min(证券余额) else max(证券余额) end as 证券余额,
交易日期,交易时间
from [table] t
where 证券余额 <> 0
and 交易日期+交易时间 =
(select max(交易日期+交易时间)
from [table]
where 客户号 = t.客户号
and 证券代码 = t.证券代码
and 证券余额 <> 0)
group by 客户号,证券代码,成交数量,交易日期,交易时间
order by 客户号,证券代码,交易日期+交易时间 )
select * from cte a where not exists
(select 1 from cte where 客户号 = a.客户号 and 证券代码 = a.证券代码 and
交易日期=a.交易日期 and 交易时间=a.交易时间)
select 客户号,证券代码,成交数量,
case when 成交数量<0 then min(证券余额) else max(证券余额) end as 证券余额,
交易日期,交易时间
from [table] t
where 证券余额 <> 0
and 交易日期+交易时间 =
(select max(交易日期+交易时间)
from [table]
where 客户号 = t.客户号
and 证券代码 = t.证券代码
and 证券余额 <> 0)
group by 客户号,证券代码,成交数量,交易日期,交易时间
order by 客户号,证券代码,交易日期+交易时间
select * from [table] t
where 证券余额<>0
and not exsits
(select 1 from [table] where 证券余额<>0 and 客户号=t.客户号 and 证券代码=t.证券代码
and (交易日期+交易时间)>(t.交易日期+t.交易时间))
order by 客户号,证券代码,交易日期+交易时间
select * from [table] t
where 证券余额<>0
and not exsits
(select 1 from [table] where 客户号=t.客户号 and 证券代码=t.证券代码
and (交易日期+交易时间)>(t.交易日期+t.交易时间))
如果交易日期是时间型则如下:
select t.* from tb t where 证券余额 <> 0 and (convert(varchar(10),交易日期,120)+cast(交易时间 as varchar))= (select max((convert(varchar(10),交易日期,120)+cast(交易时间 as varchar))) from tb where 客户号 = t.客户号 and 证券代码 = t.证券代码 and 证券余额 <> 0)
select t.* from tb t where 证券余额 <> 0 and not exists(select 1 from tb where 客户号 = t.客户号 and 证券代码 = t.证券代码 and 证券余额 <> 0 and (convert(varchar(10),交易日期,120)+cast(交易时间 as varchar)) < (convert(varchar(10),t.交易日期,120)+cast(t.交易时间 as varchar)))
??
SELECT * FROM [Table] a
WHERE 证券余额<>0 and
NOT EXISTS(SELECT 1 FROM [Table] WHERE 证券余额<>0 and 客户号=a.客户号 and 证券代码=a.证券代码
AND 交易日期+交易时间>a.交易日期+a.交易时间
)
select t.* from tb t where 证券余额 <> 0 and 交易日期+交易时间 = (select max(交易日期+交易时间) from tb where 客户号 = t.客户号 and 证券代码 = t.证券代码 and 证券余额 <> 0)
select t.* from tb t where 证券余额 <> 0 and not exists(select 1 from tb where 客户号 = t.客户号 and 证券代码 = t.证券代码 and 证券余额 <> 0 and 交易日期+交易时间 < t.交易日期+t.交易时间)