小数转整数:

--round() 遵循四舍五入把原值转化为指定小数位数,如:SELECT round(1.45,0) -- =1SELECT round(1.55,0) -- =2--floor() 向下舍入为指定小数位数 如:SELECT floor(1.45) -- =1SELECT floor(1.55) -- =1--ceiling() 向上舍入为指定小数位数 如:SELECT ceiling(1.45) -- =2SELECT ceiling(1.55) -- =2

其他参考:

 

科学计数法格式转换成数字格式:

SELECT CONVERT(INT, CAST(CAST('1.6e+006' AS FLOAT) AS DECIMAL (19,6)))

 

根据表B的一个字段 更新表A的字段数据:

UPDATE A SET A1=B.B1 FROM B WHERE A.ID=B.ID

 

把表A中的数据复制到表B中(不存在的表):

SELECT * INTO B FROM A WHERE A.TYPE=1

 

把表A中的数据复制到表B中(存在的表):

INSERT INTO B(B1,B2,...) SELECT A1,A2,... FROM A

 行转列(group by  max case when)：

select Student as '姓名',max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”，就选此行作为列max(case Subject when '英语' then Score else 0 end ) as '英语'from Scoresgroup by Studentorder by Student

 or

select Student as '姓名',avg(语文) as '语文',avg(英语) as '英语'from Scorespivot(    avg(Score) for Subject     in (语文,英语)    )as NewScoresgroup by Studentorder by Student asc

列转行（group by  union all）：

SELECT Student,'语文' AS 'Subject',  MAX(YuWen) AS Score FROM TestA GROUP BY StudentUNION ALLSELECT Student,'数学' AS 'Subject',  MAX(ShuXue) AS Score FROM TestA GROUP BY Student

 

数据库截取字符串中特定字符的左右部分:

--截取A表 F1 字段 _ 字符左边SELECT LEFT(F1,CHARINDEX('_',F1)-1) FROM A--截取A表 F1 字段 _ 字符右边SELECT substring(F1,CHARINDEX('_',F1)+1,len(F1)) FROM A

 

 

t-sql: