搞错了,应该是这样: coil time code 1 1 F 1 2 F 1 3 B 2 1 F 3 1 O显示code为F 和B 的记录,如果coil 相同,显示time最大的数据 最后显示的应该是 1 2 F 1 3 B
declare @b table(coil int,time int,code varchar(10)) insert into @b select 1,1,'f' union all select 1,2,'f' union all select 1,3,'b' union all select 2,1,'f' union all select 3,1,'o' select max(coil) coil ,min(time) time,code from @b where code='f' or code='b' group by code --结果 coil time code 1 3 b 2 1 f
declare @b table(coil int,time int,code varchar(10)) insert into @b select 1,1,'f' union all select 1,2,'f' union all select 1,3,'b' union all select 2,1,'f' union all select 3,1,'o'select min(coil) ,max(time),code from @b where code='f' or code='b' group by code
declare @b table(coil int,time int,code varchar(10)) insert into @b select 1,1,'f' union all select 1,2,'f' union all select 1,3,'b' union all select 2,1,'f' union all select 3,1,'o'select min(coil) coil,max(time) time,code from @b where code='f' or code='b' group by code order by time--测试结果coil time code 1 2 f 1 3 b
借用楼上的数据: select coil ,time ,code from table a inner join ( select max(time) as time,code from table group by code ) as b on a.code = b.code and a.time = b.time where a.code='f' or a.code='b'
select coil ,time ,code from table a inner join ( select max(time) as time,code from table group by code ) as b on a.code = b.code and a.time = b.time where a.code='f' or a.code='b'
select coil ,time,code from table a inner join ( select max(time) as time , code from table group by code ) as b where a.code=b.code and a.time=b.time order by charindex(flag,'fb')
SELECT MIN(coin) coil, MAX(ti) time, cn FROM TEMP WHERE cn = 'f' OR cn = 'b' GROUP BY cn 测试 coin ti cn 1 3 b 1 2 f
coil time code
1 1 F
1 2 F
1 3 B
2 1 F
3 1 O显示code为F 和B 的记录,如果coil 相同,显示time最大的数据
最后显示的应该是
1 2 F
1 3 B
insert into @b select 1,1,'f'
union all select 1,2,'f'
union all select 1,3,'b'
union all select 2,1,'f'
union all select 3,1,'o'
select max(coil) coil ,min(time) time,code
from @b
where code='f' or code='b'
group by code
--结果
coil time code
1 3 b
2 1 f
insert into @b select 1,1,'f'
union all select 1,2,'f'
union all select 1,3,'b'
union all select 2,1,'f'
union all select 3,1,'o'select min(coil) ,max(time),code from @b
where code='f' or code='b'
group by code
insert into @b select 1,1,'f'
union all select 1,2,'f'
union all select 1,3,'b'
union all select 2,1,'f'
union all select 3,1,'o'select min(coil) coil,max(time) time,code
from @b
where code='f' or code='b'
group by code
order by time--测试结果coil time code
1 2 f
1 3 b
select coil ,time ,code
from table a
inner join
(
select max(time) as time,code from table group by code
) as b
on a.code = b.code and a.time = b.time
where a.code='f' or a.code='b'
from table a
inner join
(
select max(time) as time,code from table group by code
) as b
on a.code = b.code and a.time = b.time
where a.code='f' or a.code='b'
from table a
inner join (
select max(time) as time , code from table group by code
) as b
where a.code=b.code and a.time=b.time
order by charindex(flag,'fb')
FROM TEMP
WHERE cn = 'f' OR
cn = 'b'
GROUP BY cn
测试
coin ti cn
1 3 b
1 2 f