按照指定列分组(例如 stationId)
统计指定列(例如T)的最大值要查出T最大对应整行数据。T最大时可能有多个相同值,只要其中任一行就可以。stationID T F ......(表示多列)
1 5 9 |
1 5 8 | 查出这两行中的一行
2 6 7
2 7 6 | 这行要查出
统计指定列(例如T)的最大值要查出T最大对应整行数据。T最大时可能有多个相同值,只要其中任一行就可以。stationID T F ......(表示多列)
1 5 9 |
1 5 8 | 查出这两行中的一行
2 6 7
2 7 6 | 这行要查出
解决方案 »
- 查询两张表,拼接其中某字段值
- INSERT 语句与 CHECK 约束"ck_stuNo"冲突。该冲突发生于数据库"stuDB",表"dbo.stuinfo", column 'stuNo'。
- READTEXT 如何将读取的数据赋值给一个变量?
- 求一个SQL语句:不知道如何用一句话说清 请进来看
- 请教:数据库恢复的问题,怎么给非sa用户增加对master的EXECUTE 权限,使其能顺利执行killspid 过程
- 关于更新TEXT字段和备份数据库的SQL语句
- 有关插入数据后返回主键
- 菜鸟请教:SQL Server 的连接问题!
- 使用ACESS数据库时报错:system resource exceeded怎么解决?
- 求助关于使用sql删除记录的语句
- 求助一条SQL如何写,自表错位连接。
- 请人帮忙改一个触发器
select * from
(select row_number() over(partition by stationID,T order by stationID,T desc) ID,*
from tb) where ID=1
from tb
group by stationID
(
stationID int,
T int,
F int
);insert TB
select 1,5,9 union all
select 1,5,8 union all
select 2,6,7 union all
select 2,7,6
select stationID,T,F=max(F)
from TB a
where not exists
(
select 1
from TB b
where a.stationID=b.stationID
and a.T>b.T
)
group by stationID,T
stationID T F
----------- ----------- -----------
1 5 9
2 6 7(2 行受影响)
from TB a
where not exists
(
select 1
from TB b
where a.stationID=b.stationID
and a.T<b.T
)
group by stationID,T
stationID T F
----------- ----------- -----------
1 5 9
2 7 6(2 行受影响)
没有查出,要求T最大时的整行
/*if object_id('O') is not null drop table Ocreate table O
(
stationID int,
T int,
F int
);insert O
select 1,5,9 union all
select 1,5,8 union all
select 2,6,7 union all
select 2,7,6
*/
with a as(select idd=row_number()over(order by stationID),* from O t where T = (select max(T) from O where stationID = t.stationID ) )
select stationID,T,F from a b
where idd = (select min(idd) from a where stationID = b.stationID)
比较复杂,不过应该能符合lz的需求
select 除了PX字段外的其他字段 from
(
select t.* , px = (select count(1) from tb where stationID = t.stationID and (t > t.t or (F < t.F))) + 1 from tb t
) m
where px = 1--sql 2005
select 除了PX字段外的其他字段 from
(
select t.* , px = row_number() over(partition by stationID order by t desc , f asc) from tb t
) m
where px = 1
(
stationID int,
T int,
F int,
F1 int ,
F2 int
);insert TB
select 1,5,9,1,2 union all
select 1,5,8,3,4 union all
select 2,6,7,5,6 union all
select 2,7,6,7,8
--SQL2005
SELECT stationID,T,F,F1,F2
FROM
(
select rn=row_number()
over(
partition by stationID
order by T desc,F asc
),*
from TB
) T
WHERE rn=1
stationID T F F1 F2
----------- ----------- ----------- ----------- -----------
1 5 8 3 4
2 7 6 7 8(2 行受影响)
(select *,Row_number() over(partition by stationid order by T asc) as r from tb ) as a
inner join
(select *,Row_number() over(partition by stationid order by F desc) as r from tb) as b
on a.stationid=b.stationid and a.t=b.t and a.f=b.f and a.r=b.r