如何删除仅仅第一列相同的记录?

问题描述:关系数据库中手工输入表InputData中有如下记录,

TagNameTimeStampValue
MM00082007-08-12 0:00:001000
MM00082007-08-13 0:00:001001
MM00082007-08-14 0:00:001002
MM00082007-08-15 0:00:001003
MM00082007-08-16 0:00:001004
MM00082007-08-17 0:00:001005
MM00082007-09-07 9:50:224444
MM00082007-09-07 15:10:56100
MM00082007-09-07 15:11:56200
MM00082007-09-11 15:53:13500
MM00082007-09-11 15:54:51500
MM00082007-09-11 15:55:03123
MM00082007-09-11 15:58:08500
MM00082007-09-11 16:01:580
MM00092007-09-11 15:58:08501
MM00092007-09-11 16:01:580
MM00102007-09-11 15:58:08502
MM00102007-09-11 16:01:580
MM00112007-09-11 15:58:08503
MM00112007-09-11 16:01:580
MM00182007-09-11 15:58:08504
MM00182007-09-11 16:01:580
MM00192007-09-11 15:58:08505
MM00192007-09-11 16:01:580
MM00202007-09-11 15:58:08506
MM00202007-09-11 16:01:580
MM00212007-09-11 15:58:08507
MM00212007-09-11 16:01:580
MM00222007-09-11 15:58:08508
MM00222007-09-11 16:01:580
MM00232007-09-11 15:58:08509
MM00232007-09-11 16:01:580
MM00242007-09-11 15:58:08510
MM00242007-09-11 16:01:580
MM00252007-09-11 15:58:08511
MM00252007-09-11 16:01:580

如何获得每个TagName离当前时间最近的一条记录,把它的值及其时间插入到实时数据库中?最后得到的记录集如下所示:

TagNameTimeStampValue

MM0008 2007-09-11 16:01:58 0
MM0009 2007-09-11 16:01:58 0
MM0010 2007-09-11 16:01:58 0
MM0011 2007-09-11 16:01:58 0
MM0018 2007-09-11 16:01:58 0
MM0019 2007-09-11 16:01:58 0
MM0020 2007-09-11 16:01:58 0
MM0021 2007-09-11 16:01:58 0
MM0022 2007-09-11 16:01:58 0
MM0023 2007-09-11 16:01:58 0
MM0024 2007-09-11 16:01:58 0
MM0025 2007-09-11 16:01:58 0

解决方案:

1. 使用distinct:可以删除完全相同的记录,但是我现在只需要删除TagName列相同的记录,不是很好实现。

2. 使用group by

SELECT     Tagname, TimeStamp, Value
FROM         InputData
WHERE     (TimeStamp IN
                          (SELECT     MAX(TimeStamp) AS TimeStamp
                            FROM          InputData 
                            GROUP BY Tagname)) 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值