小数转整数:
--round() 遵循四舍五入把原值转化为指定小数位数,如: SELECT round(1.45,0) -- =1 SELECT round(1.55,0) -- =2 --floor() 向下舍入为指定小数位数 如: SELECT floor(1.45) -- =1 SELECT floor(1.55) -- =1 --ceiling() 向上舍入为指定小数位数 如: SELECT ceiling(1.45) -- =2 SELECT ceiling(1.55) -- =2
其他参考:https://www.w3cschool.cn/sql/sql-numeric-functions.html
科学计数法格式转换成数字格式:
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 Scores group by Student order by Student
or
select Student as '姓名', avg(语文) as '语文', avg(英语) as '英语' from Scores pivot( avg(Score) for Subject in (语文,英语) )as NewScores group by Student order by Student asc
列转行(group by union all):
SELECT Student,'语文' AS 'Subject', MAX(YuWen) AS Score FROM TestA GROUP BY Student UNION ALL SELECT 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
http://www.w3school.com.cn/sql/index.asp
t-sql: