众所周知 Vaules 用于插入数据
declare @t table (id int , col1 int, col2 int, col3 int)insert into @t values(1, 14, 12, 24) select * from @t
1. 用Values插入多行,如:
declare @t table (id int , col1 int, col2 int, col3 int)insert into @tvalues(1, 14, 12, 24),(2, 55, 31, 122),(3, 121, 50, 28)select * from @t
2. 用Values模拟表变量
单列:
SELECT * FROM( VALUES (1), (2), (3)) AS T(No)
结果为:
No123
多列:
SELECT * FROM( VALUES (1,20,'name1'), (2,30,'name2'), (3,40,'name3')) AS T(No1,No2,Name)
结果为:
No1 No2 Name1 20 name12 30 name23 40 name3
3. 替换Case和UNPIVOT 表达式
测试数据
declare @t table (id int , col1 int, col2 int, col3 int)insert into @t select 1, 14, 12, 24insert into @t select 2, 55, 31, 122insert into @t select 3, 121, 50, 28insert into @t select 4, 53, 67, 24insert into @t select 5, 2, 39, 47
求每行的列的最大值
得到的结果结果应该是:
id maximum_value1 242 1223 1214 675 47
第一种解决方法(使用CASE):
select id,case when col1>col2 and col1>col3 then col1 when col2>col3 then col2 else col3end as maximum_valuefrom @t
第二种解决方法(使用UNPIVOT):
SELECT id,MAX(col) FROM( SELECT id, col FROM @t unpivot (col FOR cols in (col1,col2,col3)) AS unpivott) AS P GROUP BY id
第三种解决方法(使用VALUES):
select id,( SELECT max(n) FROM (VALUES (col1),(col3),(col3) ) as t(n))FROM @t