工作中有许多比较常用的SQL脚本,今天开始分几章分享给大家。
1、行转列的用法PIVOT
( , (), , ) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) *
结果:
select ID,NAME, [1] as '一季度', [2] as '二季度', [3] as '三季度', [4] as '四季度' from test pivot ( sum(number) for quarter in ([1],[2],[3],[4]) ) as pvt
结果:
2、列转行的用法UNPIOVT
create table test2 (id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int) insert into test2 values(1,'苹果',1000,2000,4000,5000) insert into test2 values(2,'梨子',3000,3500,4200,5500) select * from test2
(提示:可以左右滑动代码)
结果:
--列转行 select id,name,quarter,number from test2 unpivot ( number for quarter in ([Q1],[Q2],[Q3],[Q4]) ) as unpvt
结果:
3、字符串替换SUBSTRING/REPLACE
SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
结果:
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
结果:
SELECT REPLACE('12345678@qq.com','1234567','******')
结果:
4、查询一个表内相同纪录 HAVING
如果一个ID可以区分的话,可以这么写
SELECT * FROM HR.Employees
结果:
select * from HR.Employees where title in ( select title from HR.Employees group by title having count(1)>1)
结果:
对比一下发现,ID为1,2的被过滤掉了,因为他们只有一条记录
如果几个ID才能区分的话,可以这么写
select * from HR.Employees where title+titleofcourtesy in (select title+titleofcourtesy from HR.Employees group by title,titleofcourtesy having count(1)>1)
结果:
title在和titleofcourtesy进行拼接后符合条件的就只有ID为6,7,8,9的了
5、把多行SQL数据变成一条多列数据,即新增列
SELECT id, name, SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度', SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度', SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度', SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度' FROM test GROUP BY id,name
结果:
我们将原来的4列增加到了6列。细心的朋友可能发现了这个结果和上面的行转列怎么一模一样?其实上面的行转列是省略写法,这种是比较通用的写法。
6、表复制
语法1:Insert INTO table(field1,field2,...) values(value1,value2,...)
语法2:Insert into Table2(field1,field2,...) select value1,value2,... from Table1
(要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。)
语法3:SELECT vale1, value2 into Table2 from Table1
(要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。)
语法4:使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】,那么在大数据表的复制就会有问题?因为复制到一定程度就不再动了,内存爆了?它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道了。
7、利用带关联子查询Update语句更新数据
--方法1: Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null --方法2: update A set newqiantity=B.qiantity from A,B where A.bnum=B.bnum --方法3: update (select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum=B.bnum) AS C set C.newqiantity = C.qiantity where C.bnum ='001'
8、连接远程服务器
--方法1: select * from openrowset( 'SQLOLEDB', 'server=192.168.0.1;uid=sa;pwd=password', 'SELECT * FROM dbo.test') --方法2: select * from openrowset( 'SQLOLEDB', '192.168.0.1'; 'sa'; 'password', 'SELECT * FROM dbo.test')
当然也可以参考以前的示例,建立DBLINK进行远程连接
9、Date 和 Time 样式 CONVERT
CONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。
语法
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。
可以使用的 style 值:
Style ID |
Style 格式 |
100 或者 0 |
mon dd yyyy hh:miAM (或者 PM) |
101 |
mm/dd/yy |
102 |
yy.mm.dd |
103 |
dd/mm/yy |
104 |
dd.mm.yy |
105 |
dd-mm-yy |
106 |
dd mon yy |
107 |
Mon dd, yy |
108 |
hh:mm:ss |
109 或者 9 |
mon dd yyyy hh:mi:ss:mmmAM(或者 PM) |
110 |
mm-dd-yy |
111 |
yy/mm/dd |
112 |
yymmdd |
113 或者 13 |
dd mon yyyy hh:mm:ss:mmm(24h) |
114 |
hh:mi:ss:mmm(24h) |
120 或者 20 |
yyyy-mm-dd hh:mi:ss(24h) |
121 或者 21 |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
126 |
yyyy-mm-ddThh:mm:ss.mmm(没有空格) |
130 |
dd mon yyyy hh:mi:ss:mmmAM |
131 |
dd/mm/yy hh:mi:ss:mmmAM |
SELECT CONVERT(varchar(100), GETDATE(), 0) --结果: 12 7 2020 9:33PM SELECT CONVERT(varchar(100), GETDATE(), 1) --结果: 12/07/20 SELECT CONVERT(varchar(100), GETDATE(), 2) --结果: 20.12.07 SELECT CONVERT(varchar(100), GETDATE(), 3) --结果: 07/12/20 SELECT CONVERT(varchar(100), GETDATE(), 4) --结果: 07.12.20 SELECT CONVERT(varchar(100), GETDATE(), 5) --结果: 07-12-20 SELECT CONVERT(varchar(100), GETDATE(), 6) --结果: 07 12 20 SELECT CONVERT(varchar(100), GETDATE(), 7) --结果: 12 07, 20 SELECT CONVERT(varchar(100), GETDATE(), 8) --结果: 21:33:18 SELECT CONVERT(varchar(100), GETDATE(), 9) --结果: 12 7 2020 9:33:18:780PM SELECT CONVERT(varchar(100), GETDATE(), 10) --结果: 12-07-20 SELECT CONVERT(varchar(100), GETDATE(), 11) --结果: 20/12/07 SELECT CONVERT(varchar(100), GETDATE(), 12) --结果: 201207 SELECT CONVERT(varchar(100), GETDATE(), 13) --结果: 07 12 2020 21:33:18:780 SELECT CONVERT(varchar(100), GETDATE(), 14) --结果: 21:33:18:780 SELECT CONVERT(varchar(100), GETDATE(), 20) --结果: 2020-12-07 21:33:18 SELECT CONVERT(varchar(100), GETDATE(), 21) --结果: 2020-12-07 21:33:18.780 SELECT CONVERT(varchar(100), GETDATE(), 22) --结果: 12/07/20 9:33:18 PM SELECT CONVERT(varchar(100), GETDATE(), 23) --结果: 2020-12-07 SELECT CONVERT(varchar(100), GETDATE(), 24) --结果: 21:33:18 SELECT CONVERT(varchar(100), GETDATE(), 25) --结果: 2020-12-07 21:33:18.780 SELECT CONVERT(varchar(100), GETDATE(), 100) --结果: 12 7 2020 9:33PM SELECT CONVERT(varchar(100), GETDATE(), 101) --结果: 12/07/2020 SELECT CONVERT(varchar(100), GETDATE(), 102) --结果: 2020.12.07 SELECT CONVERT(varchar(100), GETDATE(), 103) --结果: 07/12/2020 SELECT CONVERT(varchar(100), GETDATE(), 104) --结果: 07.12.2020 SELECT CONVERT(varchar(100), GETDATE(), 105) --结果: 07-12-2020 SELECT CONVERT(varchar(100), GETDATE(), 106) --结果: 07 12 2020 SELECT CONVERT(varchar(100), GETDATE(), 107) --结果: 12 07, 2020 SELECT CONVERT(varchar(100), GETDATE(), 108) --结果: 21:33:18 SELECT CONVERT(varchar(100), GETDATE(), 109) --结果: 12 7 2020 9:33:18:780PM SELECT CONVERT(varchar(100), GETDATE(), 110) --结果: 12-07-2020 SELECT CONVERT(varchar(100), GETDATE(), 111) --结果: 2020/12/07 SELECT CONVERT(varchar(100), GETDATE(), 112) --结果: 20201207 SELECT CONVERT(varchar(100), GETDATE(), 113) --结果: 07 12 2020 21:33:18:780 SELECT CONVERT(varchar(100), GETDATE(), 114) --结果: 21:33:18:780 SELECT CONVERT(varchar(100), GETDATE(), 120) --结果: 2020-12-07 21:33:18 SELECT CONVERT(varchar(100), GETDATE(), 121) --结果: 2020-12-07 21:33:18.780
以上内容,在工作中比较常用,能记住最好。不能记住就收藏起来,在需要的时候查询即可。
原文地址:https://mp.weixin.qq.com/s/vS78Ewm0NWj4c9sDV7bUfQ