一张表里数据筛选显示
例如下表
字段no 字段time1 字段time2
1 2014 2015
2 2015 2014
3 2013 2017
4 2013 2013
需要得出这样的数据,字段TIME3取TIME1,time2中的最大值,如果一样取time1
字段no 字段time3
1 2015
2 2015
3 2017
4 2013
------解决思路----------------------
select [字段no],(case when [字段time1]<[字段time2] then 字段time2] else [字段time1] end ) as [字段time3] from tbl
------解决思路----------------------
with data(id, t1,t2) as
(
select 1, 2014 , 2015 union all
select 2, 2015, 2014 union all
select 3, 2013, 2017 union all
select 4, 2013, 2013
)
select id,
case when t1 < t2 then t2
else t1
end as t3
from data
------解决思路----------------------
补充一下:完整的SQL语句应当是:
SELECT 字段no, CASE WHEN 字段time1>字段time2 THEN 字段time1 ELSE CASE WHEN 字段time1<字段time2 THEN 字段time2 ELSE 字段time1 END END AS 字段time3
------解决思路----------------------
--临时表数据
with data(id, t1,t2) as
(
select 1, 2014 , 2015 union all
select 2, 2015, 2014 union all
select 3, 2013, 2017 union all
select 4, 2013, 2013
)
--使用Case when 进行数据的比对判断该取哪个字段的数据
select id,
case when t1 < t2 then t2
else t1
end as t3
from data