征求SQL语句

nuaawyd 2010-10-15 10:05:02
表数据如下:
客户号 证券代码 成交数量 证券余额 交易日期 交易时间
1517016164 000631 2300 2300 20091123 180236
1517016164 000631 -2300 0 20091202 182251
1517016164 002062 1500 1500 20091113 193805
1517016164 002062 -1500 0 20091123 180236
1517016168 000559 100 100 20091123 180236
1517016168 000559 11000 11100 20091124 181909
1517016168 000559 -500 10600 20091204 180711
1517016168 000559 -600 10000 20100111 181849
1517016168 000559 -5000 5000 20100121 183300
1517016168 000559 -4000 1000 20100329 181709
1517016168 000626 2500 2500 20100420 175439
1517016168 000626 -2500 0 20100617 191305
1517016168 000983 100 100 20091118 175828
1517016168 000983 -100 0 20091124 181909
1517016168 002062 1100 1100 20091026 180432
1517016168 002062 1800 2900 20091026 180432
1517016168 002062 1900 4800 20091026 180432
1517016168 002062 -4800 0 20091109 174836
1517016168 002091 1500 1500 20090929 180757
1517016168 002091 -1500 0 20090930 174908
1517016168 002181 8300 8300 20091112 174012
1517016168 002181 -8300 0 20091113 193805
1517016168 002200 3200 3200 20091113 193805
1517016168 002200 -100 3100 20091117 174504
1517016168 002200 -3100 0 20091124 181909
1517016168 002216 100 100 20090512 183509

要求:选出不同客户号,不同证券代码的最后一个交易日期、交易时间下证券余额不为0的记录
...全文
137 点赞 收藏 18
写回复
18 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
luojie81 2010-10-16
问下楼主,红色部分需要怎么处理?
1517016164 000631 2300 2300 20091123 180236
1517016164 000631 -2300 0 20091202 182251
1517016164 002062 1500 1500 20091113 193805
1517016164 002062 -1500 0 20091123 180236
1517016168 000559 100 100 20091123 180236
1517016168 000559 11000 11100 20091124 181909
1517016168 000559 -500 10600 20091204 180711
1517016168 000559 -600 10000 20100111 181849
1517016168 000559 -5000 5000 20100121 183300
1517016168 000559 -4000 1000 20100329 181709
1517016168 000626 2500 2500 20100420 175439
1517016168 000626 -2500 0 20100617 191305
1517016168 000983 100 100 20091118 175828
1517016168 000983 -100 0 20091124 181909
1517016168 002062 1100 1100 20091026 180432
1517016168 002062 1800 2900 20091026 180432
1517016168 002062 1900 4800 20091026 180432
1517016168 002062 -4800 0 20091109 174836
1517016168 002091 1500 1500 20090929 180757
1517016168 002091 -1500 0 20090930 174908
1517016168 002181 8300 8300 20091112 174012
1517016168 002181 -8300 0 20091113 193805
1517016168 002200 3200 3200 20091113 193805
1517016168 002200 -100 3100 20091117 174504
1517016168 002200 -3100 0 20091124 181909
1517016168 002216 100 100 20090512 183509
回复
luojie81 2010-10-16
问下楼主,红色部分需要怎么处理?
1517016164 000631 2300 2300 20091123 180236
1517016164 000631 -2300 0 20091202 182251
1517016164 002062 1500 1500 20091113 193805
1517016164 002062 -1500 0 20091123 180236
1517016168 000559 100 100 20091123 180236
1517016168 000559 11000 11100 20091124 181909
1517016168 000559 -500 10600 20091204 180711
1517016168 000559 -600 10000 20100111 181849
1517016168 000559 -5000 5000 20100121 183300
1517016168 000559 -4000 1000 20100329 181709
1517016168 000626 2500 2500 20100420 175439
1517016168 000626 -2500 0 20100617 191305
1517016168 000983 100 100 20091118 175828
1517016168 000983 -100 0 20091124 181909
1517016168 002062 1100 1100 20091026 180432
1517016168 002062 1800 2900 20091026 180432
1517016168 002062 1900 4800 20091026 180432
1517016168 002062 -4800 0 20091109 174836
1517016168 002091 1500 1500 20090929 180757
1517016168 002091 -1500 0 20090930 174908
1517016168 002181 8300 8300 20091112 174012
1517016168 002181 -8300 0 20091113 193805
1517016168 002200 3200 3200 20091113 193805
1517016168 002200 -100 3100 20091117 174504
1517016168 002200 -3100 0 20091124 181909
1517016168 002216 100 100 20090512 183509
回复
bashen1101 2010-10-15


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
回复
SQLCenter 2010-10-15
--> 测试数据:#
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
*/
回复
fpzgm 2010-10-15

--可以实现,自己可以优化下

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.交易时间)

回复
nuaawyd 2010-10-15
拜托各位了,很急啊
回复
nuaawyd 2010-10-15
要不就简单一点如果客户号和股票代码,就保留证券余额最大的一条记录,语句上怎么处理?
回复
nuaawyd 2010-10-15
楼上的也不完全对,按照你的方法还是有重复的
1517016164 000631 2300.00 2300.00 20091123 180236
1517016164 002062 1500.00 1500.00 20091113 193805
1517016168 000559 -5000.00 5000.00 20100121 183300
1517016168 000626 2500.00 2500.00 20100420 175439
1517016168 000983 100.00 100.00 20091118 175828
1517016168 002062 1100.00 1100.00 20091026 180432
1517016168 002062 1800.00 2900.00 20091026 180432
1517016168 002062 1900.00 4800.00 20091026 180432

你看客户号1517016168,股票代码002062还是有三条记录呢,我只要最后一条
回复
fpzgm 2010-10-15
[Quote=引用 7 楼 nuaawyd 的回复:]
dawugui (爱新觉罗.毓华) ,你好:
有重复数据,因为有可能(交易日期+交易时间)有相同的情况,这个时候,对于不同的客户号、证券代码,如果(成交数量<0)就取相同情况下证券余额最小的那条记录,如果(成交数量>0)就取证券余额最大的那条记录
[/Quote]



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 客户号,证券代码,交易日期+交易时间

回复
nuaawyd 2010-10-15
dawugui (爱新觉罗.毓华) ,你好:
有重复数据,因为有可能(交易日期+交易时间)有相同的情况,这个时候,对于不同的客户号、证券代码,如果(成交数量<0)就取相同情况下证券余额最小的那条记录,如果(成交数量>0)就取证券余额最大的那条记录
回复
fpzgm 2010-10-15


select * from [table] t
where 证券余额<>0
and not exsits
(select 1 from [table] where 证券余额<>0 and 客户号=t.客户号 and 证券代码=t.证券代码
and (交易日期+交易时间)>(t.交易日期+t.交易时间))
order by 客户号,证券代码,交易日期+交易时间
回复
fpzgm 2010-10-15

select * from [table] t
where 证券余额<>0
and not exsits
(select 1 from [table] where 客户号=t.客户号 and 证券代码=t.证券代码
and (交易日期+交易时间)>(t.交易日期+t.交易时间))
回复
dawugui 2010-10-15
如果交易日期是时间型则如下:

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)))
回复
chuifengde 2010-10-15
??
SELECT * FROM [Table] a
WHERE 证券余额<>0 and
NOT EXISTS(SELECT 1 FROM [Table] WHERE 证券余额<>0 and 客户号=a.客户号 and 证券代码=a.证券代码
AND 交易日期+交易时间>a.交易日期+a.交易时间
)
回复
dawugui 2010-10-15
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.交易时间)
回复
nuaawyd 2010-10-15
现在的数据按照已经按照客户号、证券代码、交易日期、交易时间排序
回复
chenxippt 2010-10-15
select [客户号],
[证券代码],
[成交数量],
[证券余额],
[交易日期],
[交易时间]
from ( select *,
row_number()over(partition by [客户号],[证券代码]
order by [交易日期]desc,[交易时间] desc ) as ordr
from STG_transaction -- 交易数据
) a
where ordr = 1
and [证券余额] <> 0
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2010-10-15 10:05
社区公告
暂无公告