深入讲解如何删除仅为第一列相同的记录
dn001
问题：关系数据库中手工输入表InputData中有如下记录:
TagName TimeStamp Value
MM0008 2007-08-12 0:00:00 1000
MM0008 2007-08-13 0:00:00 1001
MM0008 2007-08-14 0:00:00 1002
MM0008 2007-08-15 0:00:00 1003
MM0008 2007-08-16 0:00:00 1004
MM0008 2007-08-17 0:00:00 1005
MM0008 2007-09-07 9:50:22 4444
MM0008 2007-09-07 15:10:56 100
MM0008 2007-09-07 15:11:56 200
MM0008 2007-09-11 15:53:13 500
MM0008 2007-09-11 15:54:51 500
MM0008 2007-09-11 15:55:03 123
MM0008 2007-09-11 15:58:08 500
MM0008 2007-09-11 16:01:58 0
MM0009 2007-09-11 15:58:08 501
MM0009 2007-09-11 16:01:58 0
MM0010 2007-09-11 15:58:08 502
MM0010 2007-09-11 16:01:58 0
MM0011 2007-09-11 15:58:08 503
MM0011 2007-09-11 16:01:58 0
MM0018 2007-09-11 15:58:08 504
MM0018 2007-09-11 16:01:58 0
MM0019 2007-09-11 15:58:08 505
MM0019 2007-09-11 16:01:58 0
MM0020 2007-09-11 15:58:08 506
MM0020 2007-09-11 16:01:58 0
MM0021 2007-09-11 15:58:08 507
MM0021 2007-09-11 16:01:58 0
MM0022 2007-09-11 15:58:08 508
MM0022 2007-09-11 16:01:58 0
MM0023 2007-09-11 15:58:08 509
MM0023 2007-09-11 16:01:58 0
MM0024 2007-09-11 15:58:08 510
MM0024 2007-09-11 16:01:58 0
MM0025 2007-09-11 15:58:08 511
MM0025 2007-09-11 16:01:58 0 |
如何获得每个TagName离当前时间最近的一条记录，把它的值及其时间插入到实时数据库中？最后得到的记录集如下所示：
TagName TimeStamp Value
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)) |
