sql 一些偶尔会用到的写法和函数 不定时更新

小数转整数:

--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

https://www.w3cschool.cn/sql/

t-sql:

https://www.w3cschool.cn/t_sql/