SQL语句一张表数据筛选

[fancydong]

一张表里数据筛选显示
例如下表
字段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

[tianxino0815]

select [字段no],(case when [字段time1]<[字段time2] then 字段time2] else [字段time1] end ) as [字段time3] from tbl

[shoppo0505]

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

[njwup]

"需要得出这样的数据，字段TIME3取TIME1,time2中的最大值，如果一样取time1" 实际上就是取二者中较大的值！相等可以理解为是其一种特殊形式，既然相等，取time2不行麽？为什么一定要取time1? 所以二楼的语句是正确的！

[njwup]

补充一下：完整的SQL语句应当是： SELECT 字段no, CASE WHEN 字段time1>字段time2 THEN 字段time1 ELSE CASE WHEN 字段time1<字段time2 THEN 字段time2 ELSE 字段time1 END END AS 字段time3

[许晨旭]

create table #tb
(
	ID INT,
	COL1 INT,
	COL2 INT
)

INSERT INTO #tb
SELECT 1,2014,2015 UNION ALL
SELECT 2,2015,2014 UNION ALL
SELECT 3,2013,2017 UNION ALL
SELECT 4,2013,2013

;WITH CTE AS
(
	SELECT ID,COL1 AS COL
	FROM #tb
	UNION ALL
	SELECT ID,COL2
	FROM #tb
)

SELECT ID,MAX(COL)
FROM CTE
GROUP BY ID

/*
ID          
----------- -----------
1           2015
2           2015
3           2017
4           2013

(4 行受影响)

*/

[hyde100]

这就是简单的CASE WHEN判断语句，楼上那些已经有了语句了。。。

[Cherise_huang]

CREATE table  test
(
	[no] INT, 
	time1 INT ,
	time2 INT
)
INSERT INTo test
select 1,   2014, 2015 union all
select 2,   2015, 2014 union all
select 3,   2013, 2017 union all
select 4,   2013, 2013 

--SELECT * FROM DATA
select [no], 
       case when time1 < time2 then time2
       else time1
       end as time3
from test 

[Pact_Alice]

--临时表数据 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