--語法：
--SELECT * FROM (
--VALUES
-- (1,2,3,......)
-- ,(1,2,3,......)
-- ,(1,2,3,......)
-- ,(1,2,3,......)
-- ,(1,2,3,......)
-- ,(1,2,3,......)
--) AS t(c1,c2,c3......)

SELECT * FROM (
　　VALUES (123456),(123456),(123456),(123456),(123456)
) AS T(c1)

試檢1

-- 試檢1
SELECT * FROM (
　　VALUES (123456),(123456),(123456),(123456),(123456)
) AS T(c1)

試檢2

-- 試檢2 生成1個列的臨時表
select * into #tmpTable FROM (
 VALUES (123456),(123456),(123456),(123456),(123456)
) AS T(c1)

試檢3

-- 生成5個列的臨時表 #tmpTable2
select * into #tmpTable2 FROM (
 VALUES (1,2,3,4,123456)
  ,(2,3,4,5,123456)
  ,(3,4,5,6,123456)
  ,(4,100,100,3,123456)
  ,(23,4,5,5,123456)
) AS T(c1,c2,c3,c4,c5)

select * from #tmpTable2

成功了高級一點的例子

計算5列的平均值，求各，最大值，最小值，計數(shù)
使用values子句，將每行數(shù)據(jù)構(gòu)造為只有一個字段的表，以后求最大值，非常巧妙

-- 計算5列的平均值，求各，最大值，最小值，計數(shù)
select * into #tmpTable3 FROM (
 VALUES (1,2,3,4,123456)
  ,(2,3,40000,5000,123456)
  ,(3,4222,5,6000,123456)
  ,(4,100,100,3,123456)
  ,(23,4,5,5,123456)
) AS T(c1,c2,c3,c4,c5)

select a.*
 ,(select Max(NewData) from (values (C1),(C2),(C3),(C4),(C5)) as #temp(NewData)) as nMax 
 ,(select Min(NewData) from (values (C1),(C2),(C3),(C4),(C5)) as #temp(NewData)) as nMai 
 ,(select Sum(NewData) from (values (C1),(C2),(C3),(C4),(C5)) as #temp(NewData)) as nSum 
 ,(select Avg(NewData) from (values (C1),(C2),(C3),(C4),(C5)) as #temp(NewData)) as nAvg 
 ,(select count(NewData) from (values (C1),(C2),(C3),(C4),(C5)) as #temp(NewData)) as nCount 
 from #tmpTable3 as a

到此這篇關(guān)于SQL 使用 VALUES 生成帶數(shù)據(jù)的臨時表實例代碼詳解的文章就介紹到這了,更多相關(guān)sql 臨時表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: