SQL Server 2008中Values的作用

众所周知 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 @t
values
(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)

 

结果为:

No
1
2
3

　　

多列:

SELECT * FROM
(
    VALUES
    (1,20,'name1'),
    (2,30,'name2'),
    (3,40,'name3')
) AS T(No1,No2,Name)

 

结果为:

No1	No2	Name
1	20	name1
2	30	name2
3	40	name3

3. 替换Case和UNPIVOT 表达式

测试数据

declare @t table (id int , col1 int, col2 int, col3 int)
insert into @t select 1, 14, 12, 24
insert into @t select 2, 55, 31, 122
insert into @t select 3, 121, 50, 28
insert into @t select 4, 53, 67, 24
insert into @t select 5, 2, 39, 47

求每行的列的最大值

得到的结果结果应该是:

id	maximum_value
1	24
2	122
3	121
4	67
5	47

　第一种解决方法(使用CASE):

select id,
case
    when col1>col2 and col1>col3 then col1
    when col2>col3 then col2
    else col3
end  as maximum_value
from @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

参考文章:http://beyondrelational.com/modules/2/blogs/70/posts/10905/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx