SQL 如何从表中每天仅取一条记录

Question

从下表中查询出如结果所示，取每天的时间最大的一条记录即可。wellname thedate                    LiquidFaceDepth          
升34_20 2011-08-19 19:39:38.000 1225.133
升34_20 2011-08-19 20:10:27.000 1226.267
升34_20 2011-08-19 20:41:25.000 1227.4
升34_20 2011-08-19 21:12:13.000 1227.4
升34_20 2011-08-19 21:43:05.000 1226.267
升34_20 2011-08-19 22:13:58.000 1225.133
升34_20 2011-08-19 22:44:50.000 1224
升34_20 2011-08-19 23:15:45.000 1224
升34_20 2011-08-19 23:46:38.000 1224
升34_20 2011-08-20 00:17:32.000 1221.733
升34_20 2011-08-20 00:48:25.000 1221.733
升34_20 2011-08-20 01:19:17.000 1220.6
升34_20 2011-08-20 01:50:13.000 1220.6
升34_20 2011-08-20 02:21:04.000 1220.6
升34_20 2011-08-20 02:51:59.000 1220.6
升34_20 2011-08-20 03:22:52.000 1220.6
查询结果如下：升34_20 2011-08-19 23:46:38.000 1224
升34_20 2011-08-20 03:22:52.000 1220.6
Please help me.Thanks.

Answer 1

select * from tb t where LiquidFaceDepth=(select max(LiquidFaceDepth) from tb where thedate=t.thedate)

Answer 2

select wellname,thedate,max(LiquidFaceDepth) from tb t
GROUP BY wellname,thedate

Answer 3

可能我没有表示清楚，不好意思。
是这样的，这个表是三个字段：分别是 wellname,gettime,LiquidFaceDepth                   
升34_20 (wellname)
2011-08-19 19:39:38.000 (gettime)
1225.133 (LiquidFaceDepth)
是这个意思，不知道说清楚没有。
另外，您刚才那个SQL语句我试了，不正确。

Answer 4

i'm sorry----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-06 22:10:00
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([wellname] varchar(7),[thedate] datetime,[LiquidFaceDepth] numeric(7,3))
insert [tb]
select '升34_20','2011-08-19 19:39:38.000',1225.133 union all
select '升34_20','2011-08-19 20:10:27.000',1226.267 union all
select '升34_20','2011-08-19 20:41:25.000',1227.4 union all
select '升34_20','2011-08-19 21:12:13.000',1227.4 union all
select '升34_20','2011-08-19 21:43:05.000',1226.267 union all
select '升34_20','2011-08-19 22:13:58.000',1225.133 union all
select '升34_20','2011-08-19 22:44:50.000',1224 union all
select '升34_20','2011-08-19 23:15:45.000',1224 union all
select '升34_20','2011-08-19 23:46:38.000',1224 union all
select '升34_20','2011-08-20 00:17:32.000',1221.733 union all
select '升34_20','2011-08-20 00:48:25.000',1221.733 union all
select '升34_20','2011-08-20 01:19:17.000',1220.6 union all
select '升34_20','2011-08-20 01:50:13.000',1220.6 union all
select '升34_20','2011-08-20 02:21:04.000',1220.6 union all
select '升34_20','2011-08-20 02:51:59.000',1220.6 union all
select '升34_20','2011-08-20 03:22:52.000',1220.6
--------------开始查询--------------------------
select distinct * from tb t where thedate=(select max(thedate) from tb where convert(varchar(10),thedate,120)=convert(varchar(10),t.thedate,120))
----------------结果----------------------------
/* wellname thedate                 LiquidFaceDepth
-------- ----------------------- ---------------------------------------
升34_20   2011-08-19 23:46:38.000 1224.000
升34_20   2011-08-20 03:22:52.000 1220.600(2 行受影响)*/

Answer 5

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-06 22:10:00
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([wellname] varchar(7),[thedate] datetime,[LiquidFaceDepth] numeric(7,3))
insert [tb]
select '升34_20','2011-08-19 19:39:38.000',1225.133 union all
select '升34_20','2011-08-19 20:10:27.000',1226.267 union all
select '升34_20','2011-08-19 20:41:25.000',1227.4 union all
select '升34_20','2011-08-19 21:12:13.000',1227.4 union all
select '升34_20','2011-08-19 21:43:05.000',1226.267 union all
select '升34_20','2011-08-19 22:13:58.000',1225.133 union all
select '升34_20','2011-08-19 22:44:50.000',1224 union all
select '升34_20','2011-08-19 23:15:45.000',1224 union all
select '升34_20','2011-08-19 23:46:38.000',1224 union all
select '升34_20','2011-08-20 00:17:32.000',1221.733 union all
select '升34_20','2011-08-20 00:48:25.000',1221.733 union all
select '升34_20','2011-08-20 01:19:17.000',1220.6 union all
select '升34_20','2011-08-20 01:50:13.000',1220.6 union all
select '升34_20','2011-08-20 02:21:04.000',1220.6 union all
select '升34_20','2011-08-20 02:51:59.000',1220.6 union all
select '升34_20','2011-08-20 03:22:52.000',1220.6
--------------开始查询--------------------------
select distinct * from tb t where thedate=(select max(thedate) from tb where convert(varchar(10),thedate,120)=convert(varchar(10),t.thedate,120))
----------------结果----------------------------
/* wellname thedate                 LiquidFaceDepth
-------- ----------------------- ---------------------------------------
升34_20   2011-08-19 23:46:38.000 1224.000
升34_20   2011-08-20 03:22:52.000 1220.600(2 行受影响)*/

Answer 6

没看清楚是要时间最大 SORRY

Answer 7

是我没表示清楚。
上面的SQL语句还是不行，速度特别特别慢，我等不到查询结束了。
我写成下面这样了
select wellname,convert(varchar(10),gettime,120) as gettime,LiquidFaceDepth from 
( 
select convert(varchar(10),max(gettime),120) as d from LiquidInfo 
group by convert(varchar(10),gettime,120)
) a,LiquidInfo l 
where convert(varchar(10),a.d,120)=convert(varchar(10),l.gettime,120) and WellName='升24_22' And GetTime>='2011-08-21' And GetTime<='2011-09-06'可是查出来的结果是这样的：
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 770.6667
升24_22 2011-09-02 771.8
升24_22 2011-09-02 771.8
升24_22 2011-09-02 771.8
升24_22 2011-09-02 772.9333
升24_22 2011-09-02 772.9333
升24_22 2011-09-02 774.0667
升24_22 2011-09-02 775.2
升24_22 2011-09-02 774.0667
升24_22 2011-09-02 776.3333
升24_22 2011-09-02 775.2
升24_22 2011-09-02 776.3333
升24_22 2011-09-02 776.3333
升24_22 2011-09-02 776.3333
升24_22 2011-09-02 776.3333
升24_22 2011-09-02 778.6
升24_22 2011-09-02 777.4667
升24_22 2011-09-02 777.4667
升24_22 2011-09-02 778.6
升24_22 2011-09-02 778.6
升24_22 2011-09-02 778.6
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 782
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 780.8666
升24_22 2011-09-02 779.7333
升24_22 2011-09-02 780.8666
升24_22 2011-09-03 780.8666
升24_22 2011-09-03 779.7333
升24_22 2011-09-03 778.6
升24_22 2011-09-03 779.7333
升24_22 2011-09-03 778.6
升24_22 2011-09-03 778.6
升24_22 2011-09-03 778.6
升24_22 2011-09-03 777.4667
升24_22 2011-09-03 777.4667
升24_22 2011-09-03 777.4667
升24_22 2011-09-03 776.3333
升24_22 2011-09-03 776.3333
升24_22 2011-09-03 776.3333
升24_22 2011-09-03 777.4667
升24_22 2011-09-03 777.4667
升24_22 2011-09-03 778.6
升24_22 2011-09-03 778.6
升24_22 2011-09-03 779.7333
升24_22 2011-09-03 778.6
升24_22 2011-09-03 780.8666
升24_22 2011-09-03 780.8666
升24_22 2011-09-03 782
升24_22 2011-09-03 782
升24_22 2011-09-03 783.1333
升24_22 2011-09-03 783.1333
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 785.4
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 785.4
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 785.4
升24_22 2011-09-03 785.4
升24_22 2011-09-03 786.5333
升24_22 2011-09-03 785.4
升24_22 2011-09-03 786.5333
升24_22 2011-09-03 785.4
升24_22 2011-09-03 786.5333
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 785.4
升24_22 2011-09-03 784.2667
升24_22 2011-09-03 785.4
升24_22 2011-09-03 785.4
升24_22 2011-09-03 785.4
升24_22 2011-09-03 784.2667
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 783.1333
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 783.1333
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 783.1333
升24_22 2011-09-04 782
升24_22 2011-09-04 783.1333
升24_22 2011-09-04 784.2667
升24_22 2011-09-04 783.1333
升24_22 2011-09-04 785.4
升24_22 2011-09-04 785.4
升24_22 2011-09-04 786.5333
升24_22 2011-09-04 785.4
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 788.8
升24_22 2011-09-04 788.8
升24_22 2011-09-04 787.6667
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 788.8
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 791.0667
升24_22 2011-09-04 791.0667
升24_22 2011-09-04 791.0667
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 791.0667
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-04 789.9333
升24_22 2011-09-05 789.9333
升24_22 2011-09-05 788.8
升24_22 2011-09-05 787.6667
升24_22 2011-09-05 788.8
升24_22 2011-09-05 788.8
升24_22 2011-09-05 788.8
升24_22 2011-09-05 787.6667
升24_22 2011-09-05 788.8
升24_22 2011-09-05 787.6667
升24_22 2011-09-05 787.6667
升24_22 2011-09-05 787.6667
升24_22 2011-09-05 788.8
升24_22 2011-09-05 788.8
升24_22 2011-09-05 788.8
升24_22 2011-09-05 788.8
升24_22 2011-09-05 789.9333
升24_22 2011-09-05 789.9333
升24_22 2011-09-05 789.9333
升24_22 2011-09-05 789.9333
升24_22 2011-09-05 791.0667
升24_22 2011-09-05 791.0667
升24_22 2011-09-05 791.0667
升24_22 2011-09-05 791.0667
升24_22 2011-09-05 792.2
升24_22 2011-09-05 791.0667
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 792.2
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 792.2
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 793.3333
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2
升24_22 2011-09-05 792.2该怎么改呢？

Answer 8

不能用 distinct 我的表里有 image类型的字段。

Answer 9

神马情况?
create table [tb]([wellname] varchar(7),[thedate] datetime,[LiquidFaceDepth] numeric(7,3))
insert [tb]
select '升34_20','2011-08-19 19:39:38.000',1225.133 union all
select '升34_20','2011-08-19 20:10:27.000',1226.267 union all
select '升34_20','2011-08-19 20:41:25.000',1227.4 union all
select '升34_20','2011-08-19 21:12:13.000',1227.4 union all
select '升34_20','2011-08-19 21:43:05.000',1226.267 union all
select '升34_20','2011-08-19 22:13:58.000',1225.133 union all
select '升34_20','2011-08-19 22:44:50.000',1224 union all
select '升34_20','2011-08-19 23:15:45.000',1224 union all
select '升34_20','2011-08-19 23:46:38.000',1224 union all
select '升34_20','2011-08-20 00:17:32.000',1221.733 union all
select '升34_20','2011-08-20 00:48:25.000',1221.733 union all
select '升34_20','2011-08-20 01:19:17.000',1220.6 union all
select '升34_20','2011-08-20 01:50:13.000',1220.6 union all
select '升34_20','2011-08-20 02:21:04.000',1220.6 union all
select '升34_20','2011-08-20 02:51:59.000',1220.6 union all
select '升34_20','2011-08-20 03:22:52.000',1220.6
go
select * from tb t where not exists(select 1 from tb 
where wellname=t.wellname and month(thedate)=month(t.thedate) and day(thedate)=day(t.thedate) and thedate>t.thedate)
/*
wellname thedate                 LiquidFaceDepth
-------- ----------------------- ---------------------------------------
升34_20   2011-08-19 23:46:38.000 1224.000
升34_20   2011-08-20 03:22:52.000 1220.600(2 行受影响)*/
go
drop table tb