MSSQL 静态值查询

有些时候可能会需要查询一些静态值

SELECT * FROM( VALUES (1), (2),(3)) AS tabName  ( c1 )

 

演变1.多列

SELECT * FROM( VALUES (1,1), (2,2),(3,5)) AS tabName  ( c1,c2 )

 

演变2.聚合

SELECT MAX(c1) FROM( VALUES (1), (2),(3)) AS tabName  ( c1 )

SELECT SUM(c1) FROM( VALUES (1), (2),(3)) AS tabName  ( c1 )

SELECT COUNT(c1) FROM( VALUES (1), (2),(3)) AS tabName  ( c1 )

 

演变3.排序

SELECT c1 FROM( VALUES (1), (2),(3)) AS tabName  ( c1 ) ORDER BY tabName.c1

SELECT c1 FROM( VALUES (1), (2),(3)) AS tabName  ( c1 ) ORDER BY tabName.c1 DESC

 演变4.取中间到这里就全活了.

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY c1 ) Ind, c1  FROM( VALUES (1), (2),(3)) AS tabName  ( c1 ) ) tempTab WHERE tempTab.Ind=2

 

便于理解可以转化成表

SELECT * FROM (SELECT ID,CName FROM Student) AS ta (id,Tname)

 

这里相信大家都能看懂了.有什么好的想法.欢迎给我流言.小弟不才.喷手勿扰.