SQL常用脚本整理，你保存了吗？
2022-10-13 09:02:05来源：SQL数据库开发
工作中有许多比较常用的SQL脚本，今天开始分几章分享给大家。
1、行转列的用法PIVOT( , (), , ) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) (,N,,) *
结果：
(相关资料图)
select ID,NAME,[1] as "一季度",[2] as "二季度",[3] as "三季度",[4] as "四季度"fromtestpivot(sum(number)for quarter in([1],[2],[3],[4]))as pvt
结果：
2、列转行的用法UNPIOVTcreate 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,numberfromtest2unpivot(numberfor quarter in([Q1],[Q2],[Q3],[Q4]))as unpvt
结果：
3、字符串替换SUBSTRING/REPLACESELECT 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.Employeeswhere title in (select title from HR.Employeesgroup by titlehaving count(1)>1)
结果：
对比一下发现，ID为1,2的被过滤掉了，因为他们只有一条记录
如果几个ID才能区分的话，可以这么写
select * from HR.Employeeswhere title+titleofcourtesy in(select title+titleofcourtesyfrom HR.Employeesgroup by title,titleofcourtesyhaving 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 testGROUP 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 Table1set c = (select c from Table2 where a = Table1.a)where c is null--方法2：update Aset newqiantity=B.qiantityfrom A,Bwhere A.bnum=B.bnum--方法3：update(select A.bnum ,A.newqiantity,B.qiantity from Aleft join B on A.bnum=B.bnum) AS Cset C.newqiantity = C.qiantitywhere 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 样式 CONVERTCONVERT() 函数是把日期转换为新数据类型的通用函数。
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:33PMSELECT CONVERT(varchar(100), GETDATE(), 1)--结果：12/07/20SELECT CONVERT(varchar(100), GETDATE(), 2)--结果：20.12.07SELECT CONVERT(varchar(100), GETDATE(), 3)--结果：07/12/20SELECT CONVERT(varchar(100), GETDATE(), 4)--结果：07.12.20SELECT CONVERT(varchar(100), GETDATE(), 5)--结果：07-12-20SELECT CONVERT(varchar(100), GETDATE(), 6)--结果：07 12 20SELECT CONVERT(varchar(100), GETDATE(), 7)--结果：12 07, 20SELECT CONVERT(varchar(100), GETDATE(), 8)--结果：21:33:18SELECT CONVERT(varchar(100), GETDATE(), 9)--结果：12 7 2020 9:33:18:780PMSELECT CONVERT(varchar(100), GETDATE(), 10)--结果：12-07-20SELECT CONVERT(varchar(100), GETDATE(), 11)--结果：20/12/07SELECT CONVERT(varchar(100), GETDATE(), 12)--结果：201207SELECT CONVERT(varchar(100), GETDATE(), 13)--结果：07 12 2020 21:33:18:780SELECT CONVERT(varchar(100), GETDATE(), 14)--结果：21:33:18:780SELECT CONVERT(varchar(100), GETDATE(), 20)--结果：2020-12-07 21:33:18SELECT CONVERT(varchar(100), GETDATE(), 21)--结果：2020-12-07 21:33:18.780SELECT CONVERT(varchar(100), GETDATE(), 22)--结果：12/07/20 9:33:18 PMSELECT CONVERT(varchar(100), GETDATE(), 23)--结果：2020-12-07SELECT CONVERT(varchar(100), GETDATE(), 24)--结果：21:33:18SELECT CONVERT(varchar(100), GETDATE(), 25)--结果：2020-12-07 21:33:18.780SELECT CONVERT(varchar(100), GETDATE(), 100)--结果：12 7 2020 9:33PMSELECT CONVERT(varchar(100), GETDATE(), 101)--结果：12/07/2020SELECT CONVERT(varchar(100), GETDATE(), 102)--结果：2020.12.07SELECT CONVERT(varchar(100), GETDATE(), 103)--结果：07/12/2020SELECT CONVERT(varchar(100), GETDATE(), 104)--结果：07.12.2020SELECT CONVERT(varchar(100), GETDATE(), 105)--结果：07-12-2020SELECT CONVERT(varchar(100), GETDATE(), 106)--结果：07 12 2020SELECT CONVERT(varchar(100), GETDATE(), 107)--结果：12 07, 2020SELECT CONVERT(varchar(100), GETDATE(), 108)--结果：21:33:18SELECT CONVERT(varchar(100), GETDATE(), 109)--结果：12 7 2020 9:33:18:780PMSELECT CONVERT(varchar(100), GETDATE(), 110)--结果：12-07-2020SELECT CONVERT(varchar(100), GETDATE(), 111)--结果：2020/12/07SELECT CONVERT(varchar(100), GETDATE(), 112)--结果：20201207SELECT CONVERT(varchar(100), GETDATE(), 113)--结果：07 12 2020 21:33:18:780SELECT CONVERT(varchar(100), GETDATE(), 114)--结果：21:33:18:780SELECT CONVERT(varchar(100), GETDATE(), 120)--结果：2020-12-07 21:33:18SELECT CONVERT(varchar(100), GETDATE(), 121)--结果：2020-12-07 21:33:18.780
以上内容，在工作中比较常用，能记住最好。不能记住就收藏起来，在需要的时候查询即可。
