stationum maxtemp timemaxtemp59843 130 19:01
59758 138 19:04
59842 140 19:01
59951 359 19:01
59954 37 19:02
59981 216 19:38
59848 null 19:01
59849 146 19:05
59851 - 19:11
59854 47 19:01现在想得到以下结果:即找出最大值及其对就的时间59951 359 19:01
59758 138 19:04
59842 140 19:01
59951 359 19:01
59954 37 19:02
59981 216 19:38
59848 null 19:01
59849 146 19:05
59851 - 19:11
59854 47 19:01现在想得到以下结果:即找出最大值及其对就的时间59951 359 19:01
select max(maxtemp) a from 表)
select *
from tb as a
where not exists(select 1 from tb where cast(maxtemp as int)>cast(a.maxtemp as int))
isnull(maxtemp,'')<>''
stationum maxtemp timemaxtemp 59843 130 19:01
59758 138 19:04
59842 140 19:01
59951 359 19:01
59954 37 19:02
59981 216 19:38
59848 null 19:01
59849 146 19:05
59851 - 19:11
59854 47 19:01 现在想得到以下结果:即找出最大值及其对就的时间 59951 359 19:01 最小值及其对就的时间
59954 37 19:02
INSERT @TB
SELECT 59843, '130', '19:01' UNION ALL
SELECT 59758, '138', '19:04' UNION ALL
SELECT 59842, '140', '19:01' UNION ALL
SELECT 59951, '359', '19:01' UNION ALL
SELECT 59954, '37', '19:02' UNION ALL
SELECT 59981, '216', '19:38' UNION ALL
SELECT 59848, null, '19:01' UNION ALL
SELECT 59849, '146', '19:05' UNION ALL
SELECT 59851, '', '19:11' UNION ALL
SELECT 59854, '47', '19:01'select * from @tb as a where not exists(select 1 from @tb where cast(maxtemp as int)>cast(a.maxtemp as int)) AND isnull(maxtemp,'')<>''
/*
stationum maxtemp timemaxtemp
----------- ---------- -----------
59951 359 19:01
*/
DECLARE @TB TABLE(stationum INT,maxtemp VARCHAR(10),timemaxtemp VARCHAR(5))
INSERT @TB
SELECT 59843, '130', '19:01' UNION ALL
SELECT 59758, '138', '19:04' UNION ALL
SELECT 59842, '140', '19:01' UNION ALL
SELECT 59951, '359', '19:01' UNION ALL
SELECT 59954, '37', '19:02' UNION ALL
SELECT 59981, '216', '19:38' UNION ALL
SELECT 59848, null, '19:01' UNION ALL
SELECT 59849, '146', '19:05' UNION ALL
SELECT 59851, '', '19:11' UNION ALL
SELECT 59854, '47', '19:01'select * from @tb as a where not exists(select 1 from @tb where isnull(maxtemp,'')<>'' AND cast(maxtemp as int)>cast(a.maxtemp as int)) AND isnull(maxtemp,'')<>''
/*
stationum maxtemp timemaxtemp
----------- ---------- -----------
59951 359 19:01
*/
select * from @tb as a where not exists(select 1 from @tb where isnull(maxtemp,'')<>'' AND cast(maxtemp as int)<cast(a.maxtemp as int)) AND isnull(maxtemp,'')<>''
/*
stationum maxtemp timemaxtemp
----------- ---------- -----------
59954 37 19:02
*/
INSERT @TB
SELECT 59843, '130', '19:01' UNION ALL
SELECT 59758, '138', '19:04' UNION ALL
SELECT 59842, '140', '19:01' UNION ALL
SELECT 59951, '359', '19:01' UNION ALL
SELECT 59954, '37', '19:02' UNION ALL
SELECT 59981, '216', '19:38' UNION ALL
SELECT 59848, null, '19:01' UNION ALL
SELECT 59849, '146', '19:05' UNION ALL
SELECT 59851, '', '19:11' UNION ALL
SELECT 59854, '47', '19:01'
select * from @tb where maxtemp=(select max(cast(maxtemp as int))maxtemp from @tb) --maxselect * from @tb where maxtemp=(select min(cast(maxtemp as int))maxtemp from @tb where isnull(maxtemp,'')<>'') --min
/*stationum maxtemp timemaxtemp
----------- ---------- -----------
59951 359 19:01Warning: Null value is eliminated by an aggregate or other SET operation.
stationum maxtemp timemaxtemp
----------- ---------- -----------
59954 37 19:02*/
SELECT 59851, '-', '19:11' UNION ALL
改后结果怎么写?isnull(maxtemp,'')<>''
谢谢!
INSERT @TB
SELECT 59843, '130', '19:01' UNION ALL
SELECT 59758, '138', '19:04' UNION ALL
SELECT 59842, '140', '19:01' UNION ALL
SELECT 59951, '359', '19:01' UNION ALL
SELECT 59954, '37', '19:02' UNION ALL
SELECT 59981, '216', '19:38' UNION ALL
SELECT 59848, null, '19:01' UNION ALL
SELECT 59849, '146', '19:05' UNION ALL
SELECT 59851, '-', '19:11' UNION ALL
SELECT 59854, '47', '19:01'
select * from @tb where maxtemp=(select max(cast(maxtemp as int))maxtemp from @tb) --maxselect * from @tb where maxtemp=(select min(cast(maxtemp as int))maxtemp from @tb where isnull(maxtemp,'')<>'' and maxtemp<>'-') --min
/*stationum maxtemp timemaxtemp
----------- ---------- -----------
59951 359 19:01Warning: Null value is eliminated by an aggregate or other SET operation.
stationum maxtemp timemaxtemp
----------- ---------- -----------
59954 37 19:02*/
if object_id('tb') is not null
drop table tb
go
create table tb(stationum int,maxtemp varchar(10),timemaxtemp varchar(10))
go
insert into tb
select 59843,'130','19:01' union all
select 59758,'138','19:04' union all
select 59842,'140','19:01' union all
select 59951,'359','19:01' union all
select 59954,'37','19:02' union all
select 59981,'216','19:38' union all
select 59848,null,'19:01' union all
select 59849,'146','19:05' union all
select 59851,'-','19:11' union all
select 59854,'47','19:11'
go
select * from tb a where not exists(select 1 from tb where cast(isnull(maxtemp,0) as int)>cast(isnull(a.maxtemp,0) as int))
再加这句却运行不通 and maxtemp<>'// /'
出现将 varchar 值 '// /' 转换为数据类型为 int 的列时发生语法错误。
请再帮忙一下