简单有用的SQL脚本 (行列互转,查询一个表内相同
网络编程 2021-07-05 16:33www.168986.cn编程入门
本文推荐的SQL脚本都是很实用的哦，学会了，对在工作中得心应手，一起来学习学习吧。
行列互转
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select from test
--行转列
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "5"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4],[5])
)
as pvt
create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,'a',1000,2000,4000,5000)
insert into test2 values(2,'b',3000,3500,4200,5500)
select from test2
--列转行
select id,name,quarter,profile
from
test2
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
sql替换字符串 substring replace
--例子1
update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'') where ID = 1
--例子2
update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'') where ID = 1
--例子3
update tbPersonalInfo set Email = replace(Email,'chinamobile','') where ID = 1
SQL查询一个表内相同纪录 having
如果一个ID可以区分的话，可以这么写
select from 表 where ID in (
select ID from 表 group by ID having sum(1)>1))
如果几个ID才能区分的话，可以这么写
select from 表 where ID1+ID2+ID3 in
(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1))
其他回答数据表是zy_bho,想找出ZYH字段名相同的记录
--方法1
SELECT FROM zy_bho a WHERE EXISTS
(SELECT 1 FROM zy_bho WHERE [PK] <> a.[PK] AND ZYH = a.ZYH)
--方法2
select a. from zy_bho a join zy_bho b
on (a.[pk]<>b.[pk] and a.zyh=b.zyh)
--方法3
select from zy_bbo where zyh in
(select zyh from zy_bbo group by zyh having count(zyh)>1)
--其中pk是主键或是 unique的字段。
把多行SQL数据变成一条多列数据，即新增列
Select
DeptName=O.OUName,
'9G'=Sum(Case When PersonalGrade=9 Then 1 Else 0 End),
'8G'=Sum(Case When PersonalGrade=8 Then 1 Else 0 End),
'7G4'=Sum(Case When PersonalGrade=7 AND JobGrade =4 Then 1 Else 0 End),
'7G3'=Sum(Case When PersonalGrade=7 AND JobGrade =3 Then 1 Else 0 End),
'6G'=Sum(Case When PersonalGrade=6 Then 1 Else 0 End),
'5G3'=Sum(Case When PersonalGrade=5 AND JobGrade =3 Then 1 Else 0 End),
'5G2'=Sum(Case When PersonalGrade=5 AND JobGrade =2 Then 1 Else 0 End),
'4G'=Sum(Case When PersonalGrade=4 Then 1 Else 0 End),
'3G2'=Sum(Case When PersonalGrade=3 AND JobGrade =2 Then 1 Else 0 End),
'3G1'=Sum(Case When PersonalGrade=3 AND JobGrade =1 Then 1 Else 0 End),
'2G'=Sum(Case When PersonalGrade=2 Then 1 Else 0 End),
'1G'=Sum(Case When PersonalGrade=1 Then 1 Else 0 End),
--' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End)
表复制
insert into PhoneChange_Num ([IMSI],Num)
SELECT [IMSI]
,count([IMEI]) as num
FROM [Test].[dbo].[PhoneChange] group by [IMSI] order by num desc
语法1Insert INTO table(field1,field2,...) values(value1,value2,...)
语法2Insert into Table2(field1,field2,...) select value1,value2,... from Table1（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）
语法3SELECT vale1, value2 into Table2 from Table1（要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。）
语法4使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。
利用带关联子查询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 =XX
连接远程服务器
--方法1
select from openrowset('SQLOLEDB','server=192.168.0.67;uid=sa;pwd=password','SELECT FROM BCM2.dbo.tbAppl')
--方法2
select from openrowset('SQLOLEDB','192.168.0.67';'sa';'password','SELECT FROM BCM2.dbo.tbAppl')
TRUNCATE TABLE [Table Name]
狼蚁网站SEO优化是对Truncate语句在MSSQLServer2000中用法和原理的说明
Truncate是SQL中的一个删除数据表内容的语句，用法是
Truncate table 表名 速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
参考文献
作者听风吹雨
代码如下:
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select from test
--行转列
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "5"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4],[5])
)
as pvt
create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,'a',1000,2000,4000,5000)
insert into test2 values(2,'b',3000,3500,4200,5500)
select from test2
--列转行
select id,name,quarter,profile
from
test2
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
sql替换字符串 substring replace
代码如下:
--例子1
update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'') where ID = 1
--例子2
update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'') where ID = 1
--例子3
update tbPersonalInfo set Email = replace(Email,'chinamobile','') where ID = 1
SQL查询一个表内相同纪录 having
如果一个ID可以区分的话，可以这么写
代码如下:
select from 表 where ID in (
select ID from 表 group by ID having sum(1)>1))
如果几个ID才能区分的话，可以这么写
代码如下:
select from 表 where ID1+ID2+ID3 in
(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1))
其他回答数据表是zy_bho,想找出ZYH字段名相同的记录
代码如下:
--方法1
SELECT FROM zy_bho a WHERE EXISTS
(SELECT 1 FROM zy_bho WHERE [PK] <> a.[PK] AND ZYH = a.ZYH)
--方法2
select a. from zy_bho a join zy_bho b
on (a.[pk]<>b.[pk] and a.zyh=b.zyh)
--方法3
select from zy_bbo where zyh in
(select zyh from zy_bbo group by zyh having count(zyh)>1)
--其中pk是主键或是 unique的字段。
把多行SQL数据变成一条多列数据，即新增列
代码如下:
Select
DeptName=O.OUName,
'9G'=Sum(Case When PersonalGrade=9 Then 1 Else 0 End),
'8G'=Sum(Case When PersonalGrade=8 Then 1 Else 0 End),
'7G4'=Sum(Case When PersonalGrade=7 AND JobGrade =4 Then 1 Else 0 End),
'7G3'=Sum(Case When PersonalGrade=7 AND JobGrade =3 Then 1 Else 0 End),
'6G'=Sum(Case When PersonalGrade=6 Then 1 Else 0 End),
'5G3'=Sum(Case When PersonalGrade=5 AND JobGrade =3 Then 1 Else 0 End),
'5G2'=Sum(Case When PersonalGrade=5 AND JobGrade =2 Then 1 Else 0 End),
'4G'=Sum(Case When PersonalGrade=4 Then 1 Else 0 End),
'3G2'=Sum(Case When PersonalGrade=3 AND JobGrade =2 Then 1 Else 0 End),
'3G1'=Sum(Case When PersonalGrade=3 AND JobGrade =1 Then 1 Else 0 End),
'2G'=Sum(Case When PersonalGrade=2 Then 1 Else 0 End),
'1G'=Sum(Case When PersonalGrade=1 Then 1 Else 0 End),
--' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End)
表复制
代码如下:
insert into PhoneChange_Num ([IMSI],Num)
SELECT [IMSI]
,count([IMEI]) as num
FROM [Test].[dbo].[PhoneChange] group by [IMSI] order by num desc
语法1Insert INTO table(field1,field2,...) values(value1,value2,...)
语法2Insert into Table2(field1,field2,...) select value1,value2,... from Table1（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）
语法3SELECT vale1, value2 into Table2 from Table1（要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。）
语法4使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。
利用带关联子查询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 =XX
连接远程服务器
代码如下:
--方法1
select from openrowset('SQLOLEDB','server=192.168.0.67;uid=sa;pwd=password','SELECT FROM BCM2.dbo.tbAppl')
--方法2
select from openrowset('SQLOLEDB','192.168.0.67';'sa';'password','SELECT FROM BCM2.dbo.tbAppl')
TRUNCATE TABLE [Table Name]
狼蚁网站SEO优化是对Truncate语句在MSSQLServer2000中用法和原理的说明
Truncate是SQL中的一个删除数据表内容的语句，用法是
Truncate table 表名 速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
参考文献
作者听风吹雨
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名：提升您网站曝光率的关键
- 卢龙关键词优化：提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化：提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化：提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化：提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名：详尽指南