有一张表t_warn 其中有四个字段如下,其他字段省略:id stationId warnType startTime
1 2 6 2011-03-06 22:20:01.140
2 2 1 2011-03-06 22:20:01.140
3 1 1 2011-03-06 22:20:01.140
4 3 5 2011-03-06 22:30:01.217
5 4 3 2011-03-06 22:30:01.217
id为自增字段 stationId为站点 warnType为告警类型 startTime为告警时间请问怎么写SQL语句查询出每个站点最新的那条告警
1 2 6 2011-03-06 22:20:01.140
2 2 1 2011-03-06 22:20:01.140
3 1 1 2011-03-06 22:20:01.140
4 3 5 2011-03-06 22:30:01.217
5 4 3 2011-03-06 22:30:01.217
id为自增字段 stationId为站点 warnType为告警类型 startTime为告警时间请问怎么写SQL语句查询出每个站点最新的那条告警
select *
from tb t
where id = (select top 1 id from tb where stationid = t.stationid order by starttime desc)
select *
from tb t
where not exists (select 1 from tb where stationid = t.stationid and starttime > t.starttime)
select *
from tb t
where starttime = (select max(starttime) from tb where stationid = t.stationid)
推荐第一个,第二第三个,如果最大的时间有两条,那么这两条都会出来,如果同一站点最大时间是不重复的,那么三个都可以。
from tb t
where id = (select top 1 id from tb
where stationid = t.stationid order by starttime desc)
select *
from tb t
where id = (select top 1 id from tb where stationid = t.stationid order by starttime desc,id desc)
貌似楼主时间相同的也有啊!再根据id排个序!
select *
from tb t
where not exists (select 1 from tb
where stationid = t.stationid and (starttime > t.starttime or (starttime = t.starttime and id > t.id)))
*
from
tb t
where
starttime = (select max(starttime) from tb where stationid = t.stationid)
*
from
tb t
where
starttime = (select max(starttime) from tb where stationid = t.stationid)
CREATE TABLE t_warn
(
id INT IDENTITY(1,1),
stationId INT,
warnType INT,
startTime DATETIME
)INSERT INTO t_warn
SELECT 2, 6, '2011-03-06 22:20:01.140'
UNION ALL
SELECT 2 ,1 ,'2011-03-06 22:19:01.140'
UNION ALL
SELECT 1 ,1 ,'2011-03-06 22:20:01.140'
UNION ALL
SELECT 3 ,5 ,'2011-03-06 22:30:01.217'
UNION ALL
SELECT 4 ,3 ,'2011-03-06 22:30:01.217'
UNION ALL
SELECT 4 ,1 ,'2011-03-06 22:20:01.217'SELECT * FROM t_warnSELECT id,stationId,warnType,startTime FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY stationId ORDER BY startTime desc) num FROM t_warn) a
WHERE a.num=1
ORDER BY idSELECT * FROM t_warn a
WHERE NOT EXISTS(SELECT * FROM t_warn WHERE stationId=a.stationId AND startTime>a.startTime)id stationId warnType startTime
----------- ----------- ----------- -----------------------
1 2 6 2011-03-06 22:20:01.140
3 1 1 2011-03-06 22:20:01.140
4 3 5 2011-03-06 22:30:01.217
5 4 3 2011-03-06 22:30:01.217(4 row(s) affected)