求SQL语句,急!!
表A:
id peo dateconsumemoney fundmoneysurplusmoneycount
1984918 003610162007-07-20 16:22:403.60 .0000 3.2500 171
2352268003610162007-07-24 21:08:200.00 150.00153.25 172
2352400003610162007-07-24 21:09:590.00 150.00153.25 172
2352402003610162007-07-24 21:10:000.00 150.00153.25 172
2483697 003610162007-07-25 11:16:532.00.0000151.25 173
2308706000099082007-07-24 11:55:504.2000.0000244.45002833
2419063000099082007-07-25 09:39:000.00150.0000394.45002834
2419085000099082007-07-25 09:39:310.00150.0000544.45002835
2468308000099082007-07-25 12:05:484.2000.0000540.25002836
2450131000099082007-07-25 15:04:370.00150.0000690.25002837
2617442 000027262007-07-26 11:47:184.2000.00002.00002149
2597997000027262007-07-26 11:57:01.000050.000052.00002150
2597998000027262007-07-26 11:57:02.000050.000052.00002150
如何通过SQL语句得到如下结果:
id peo dateconsumemoney fundmoneysurplusmoneycount
1984918 003610162007-07-20 16:22:403.60 .0000 3.2500 171
2352402003610162007-07-24 21:10:000.00 150.00153.25 172
2483697 003610162007-07-25 11:16:532.00.0000151.25 173
2308706000099082007-07-24 11:55:504.2000.0000244.45002833
2419063000099082007-07-25 09:39:000.00150.0000394.45002834
2419085000099082007-07-25 09:39:310.00150.0000544.45002835
2468308000099082007-07-25 12:05:484.2000.0000540.25002836
2450131000099082007-07-25 15:04:370.00150.0000690.25002837
2617442 000027262007-07-26 11:47:184.2000.00002.00002149
2597997000027262007-07-26 11:57:01.000050.000052.00002150
急,求各位帮帮忙!谢谢!
[解决办法]
select * from a x
where not exists (
select 1 from a
where peo=x.peo
and consumemoney=x.consumemoney
and fundmoney=x.fundmoney
and surplusmoney=x.surplusmoney
and [count]=x.[count]
and id> x.id
)
[解决办法]
你看这样可以吗?
--创建表
DECLARE @t table
(peo int,date datetime, consumemoney money,
fundmoney money, surplusmoney money ,[count] int, [type] varchar(20))
--插入数据
insert @t
select 00361016, '2007-07-20 16:22 ',3.60,.0000,3.2500,171, '消费 '
union all select 00361016, '2007-07-24 21:08 ',0.00,150.0,153.25,172, '充值 '
union all select 00361016, '2007-07-24 21:09 ',0.00,150.0,153.25,172, '充值 '
union all select 00361016, '2007-07-24 21:10 ',0.00,150.0,153.25,172, '充值 '
union all select 00361016, '2007-07-25 11:16 ',2.00,.0000,151.25,173, '消费 '
union all select 00009908, '2007-07-24 11:55 ',4.20,.0000,244.45,2833, '消费 '
union all select 00009908, '2007-07-25 09:39 ',0.00,150.0,394.45,2834, '充值 '
union all select 00009908, '2007-07-25 09:39 ',0.00,150.0,544.45,2835, '充值 '
union all select 00009908, '2007-07-25 12:05 ',4.20,.0000,540.25,2836, '消费 '
union all select 00009908, '2007-07-25 15:04 ',0.00,150.0,690.25,2837, '充值 '
union all select 00002726, '2007-07-26 11:47 ',4.20,.0000,2.0000,2149, '消费 '
union all select 00002726, '2007-07-26 11:57 ',.000,50.00,52.000,2150, '充值 '
union all select 00002726, '2007-07-26 11:57 ',.000,50.00,52.000,2150, '充值 '
--测试数据
select * from @t
--查询数据
select distinct * from @t where date in (
select max(date) from @t
group by [type],[count]);
/* Result : "2 recordsets returned (250 ms) " */
[解决办法]
---创建测试数据
declare @t table(peo varchar(8),date datetime,consumemoney money,
fundmoney money,surplusmoney money ,[count] int,[type] varchar(20))
insert @t select '00361016 ', '2007-07-20 16:22 ',3.60,.0000,3.2500,171, '消费 '
union all select '00361016 ', '2007-07-24 21:08 ',0.00,150.0,153.25,172, '充值 '
union all select '00361016 ', '2007-07-24 21:09 ',0.00,150.0,153.25,172, '充值 '
union all select '00361016 ', '2007-07-24 21:10 ',0.00,150.0,153.25,172, '充值 '
union all select '00361016 ', '2007-07-25 11:16 ',2.00,.0000,151.25,173, '消费 '
union all select '00009908 ', '2007-07-24 11:55 ',4.20,.0000,244.45,2833, '消费 '
union all select '00009908 ', '2007-07-25 09:39 ',0.00,150.0,394.45,2834, '充值 '
union all select '00009908 ', '2007-07-25 09:39 ',0.00,150.0,544.45,2835, '充值 '
union all select '00009908 ', '2007-07-25 12:05 ',4.20,.0000,540.25,2836, '消费 '
union all select '00009908 ', '2007-07-25 15:04 ',0.00,150.0,690.25,2837, '充值 '
union all select '00002726 ', '2007-07-26 11:47 ',4.20,.0000,2.0000,2149, '消费 '
union all select '00002726 ', '2007-07-26 11:57 ',.000,50.00,52.000,2150, '充值 '
union all select '00002726 ', '2007-07-26 11:57 ',.000,50.00,52.000,2150, '充值 '
---查看测试数据
select * from @t
---查看结果
select * from @t a where not exists
(select 1 from @t where peo=a.peo
and convert(char(10),date,120)=convert(char(10),a.date,120)
and type=a.type and date> a.date)
/*建议LZ要是有ID的话将 date> a.date 改成 id> a.id
用date> a.date 在你的数据里有四行date相等处理不到
2007-07-25 09:39
2007-07-25 09:39
2007-07-26 11:57
2007-07-26 11:57
改用id> a.id可以很好的实现你想要的结果*/
/*
peo date consumemoney fundmoney surplusmoney count type
-------- ---------------------------- --------------------- --------------------- --------------------- ----------- --------------------
00361016 2007-07-20 16:22:00.000 3.6000 .0000 3.2500 171 消费
00361016 2007-07-24 21:10:00.000 .0000 150.0000 153.2500 172 充值
00361016 2007-07-25 11:16:00.000 2.0000 .0000 151.2500 173 消费
00009908 2007-07-24 11:55:00.000 4.2000 .0000 244.4500 2833 消费
00009908 2007-07-25 12:05:00.000 4.2000 .0000 540.2500 2836 消费
00009908 2007-07-25 15:04:00.000 .0000 150.0000 690.2500 2837 充值
00002726 2007-07-26 11:47:00.000 4.2000 .0000 2.0000 2149 消费
00002726 2007-07-26 11:57:00.000 .0000 50.0000 52.0000 2150 充值
00002726 2007-07-26 11:57:00.000 .0000 50.0000 52.0000 2150 充值
（所影响的行数为 9 行）
*/
