简单有用的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

语法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文件就知道了。

利用带关联子查询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]
下面是对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 不能用于参与了索引视图的表。

参考文献
数据库表行转列,列转行终极方案

行列互转(动态脚本)

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

非常有用的sql脚本

作者:听风吹雨

(0)

相关推荐

  • mysql中多表删除其中ID相同记录的方法

      问题源自一个5年前的百度知道回答,刚看到这个问题时我刚上班21天,遂根据自己的那一丁点经验和临时操作检验做出了当时认为正确的回答:  但很快就被人否定,我猜主要原因就是我说的最后一句话过于绝对,但由于自己太相信自己了,而且有一大堆理由支撑着,所以没有深入研究. 最近又有网友评论了我的这个回答,我才开始查了一下百度,发现Mysql4.0以上版本已经可以通过一条delete删除多个表的数据了.看来我确实在误人子弟了,只能说声抱歉,好在楼主没有选我的答案,要不然罪过大了. 最后总结一下: MySq

  • php中批量删除Mysql中相同前缀的数据表的代码

    方法一: 复制代码 代码如下: <?php mysql_connect('','',''); mysql_select_db(''); $rs=mysql_query('show tables'); while($arr=mysql_fetch_array($rs)){ $TF=strpos($arr[0],'class_'); if($TF===0){ $FT=mysql_query("drop table $arr[0]"); if($FT){ echo "$arr

  • SQL 合并多行记录的相同字段值

    1.从数据库中先查询符合条件的记录,存放于一个DataTable中,在使用c#等开始遍历这张表,利用DataRow中的主键,再去读取相应的符合条件的多条记录,合并这些第二次读取到的记录内容,返回给前面的这个DataRow数据行.这样做没有错,但是如果数据量大,我们可能面临无数次的打开断开数据库链接,速度效率将会很低. 2.从数据库中一次读取数据到一张表中返回并显示到UI层.说起来谁都想这么做,但是以前太笨,没有去研究这个,今天因为数据量较大的原因,让我不得不想些其他办法来提高点效率. Googl

  • sql函数实现去除字符串中的相同的字符串

    复制代码 代码如下: ---去除字符串中重復的值函數 create function StringRemove(@str nvarchar(2000)) returns varchar(2000) as begin declare @result nvarchar(2000),@temp nvarchar(1000) set @result='' set @temp='' while(charindex(',',@str)<>0) begin set @temp=substring(@str,

  • SQL中遇到多条相同内容只取一条的最简单实现方法

    SQL中经常遇到如下情况,在一张表中有两条记录基本完全一样,某个或某几个字段有些许差别, 这时候可能需要我们踢出这些有差别的数据,即两条或多条记录中只保留一项. 如下:表timeand 针对time字段相同时有不同total和name的情形,每当遇到相同的则只取其中一条数据,最简单的实现方法有两种 1.select time,max(total) as total,name from timeand group by time;//取记录中total最大的值 或 select time,min(

  • 一个用来统计相同姓名人数的SQl语句

    sql语句查询 表结构是这样: ID 姓名 性别 1 张三 男 2 王四 男 3 丽丽 女 4 张三 男 5 赵柳 男 6 高洁 男 7 王四 女 8 高洁 女 9 张三 女 怎么能用一条SQL语句查询出如下的结果 姓名 人员个数 男人数 女人数 包含ID 张三 3 2 1 1,4,9 王四 2 1 1 2,7 丽丽 1 0 1 3 高洁 2 1 1 6,8 赵柳 1 1 0 5 复制代码 代码如下: SELECT * FROM (SELECT DISTINCT Name,Count(ID) a

  • sqlserver通用的删除服务器上的所有相同后缀的临时表

    复制代码 代码如下: use tempdb if object_id('tempdb..#table') is not null drop table tempdb..#table select name into tempdb..#table from (select * from sysobjects where xtype='U') a where a.name like '%test_select' declare @table varchar(100),@count int selec

  • 如何使用MySQL查询某个列中相同值的数量统计

    数据现在是这样的,我想确定出type列中的news和image....甚至以后有其他值,他们分别有多少个. SELECT type, count(1) AS counts FROM material GROUP BY type count(1),代表统计第一列,写上1 比写 *的效率高! 以上所述就是本文的全部内容了,希望大家能够喜欢.

  • 用SQL语句查询数据库中某一字段下相同值的记录方法

    今天接到一任务,有一张学生信息表(Excel表),里面有一万多条记录,现在要把这张表导入到数据库中,并设置学生学号为主键,但是现在这张表中的学生学号有重复的记录,我必须先找出这些重复的记录,然后再进行筛选,经过研究问题终于得到解决.      以上问题实际上就是查询数据库表中某一字段值重复的记录,这里省略如何将Excel表导入到数据库步骤,只讨论用SQL查询数据库中某一字段下相同值的记录方法. 现在假设数据库表名为student,里面有字段Sno(学号),ID(身份证),这里提供两种查询的方法:

  • 简单有用的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 tes

  • SQL Server数据库按百分比查询出表中的记录数

    SQL Server数据库查询时,能否按百分比查询出记录的条数呢?答案是肯定的.本文我们就介绍这一实现方法. 实现该功能的代码如下: create procedure pro_topPercent ( @ipercent [int] =0 --默认不返回 ) as begin select top (@ipercent ) percent * from books end 或 create procedure pro_topPercent ( @ipercent [int] =0 ) as be

  • 在postgreSQL中运行sql脚本和pg_restore命令方式

    今天踩坑了,把powerdesign生成的sql脚本文件,用pg_restore命令一直运行... 过程惨不忍睹,一直以为是编码问题,修改了serve和client的encoding,结果... 记录一下这个错误: postgreSQL运行sql脚本文件: psql -d dbname -U username -f (脚本所在位置).sql postgerSQL的pg_restore命令 用法: pg_restore [选项]- [文件名] 一般选项: -d, --dbname=名字 连接数据库

  • 简单实用SQL脚本Part SQLServer 2005 链接服务器

    适用场景:对远程的DB进行操作. 2000与2005对比:在SQL Server 2000版本中也有链接远程DB的SQL,但是功能比较弱,扩展性差,支持的查询比较简单.而SQL Server 2005版本的SSMS中已经有了 服务器对象->链接服务器 的功能点,用户首先创建一个远程DB的链接对象,之后就可以像本地表一样执行表的DML了. 创建步骤:在SQL Server 2005版本打开SSMS,服务器对象->链接服务器->右击 新建链接服务器,在图2中是一种设置方式,也有其它的设置方式

  • 合并SQL脚本文件的方法分享

    概述 -------------------------------------------------------------------------------- 在平时的工作中,我会经常的碰到这样需要合并SQL脚本的问题.如,有很多的SQL脚本文件,需要按照一定的先后顺序,再生成一个合并SQL脚本文件,然后再发布到用户SQL Server服务器上. 合并SQL脚本文件,最直接的方法就是新建1个空白的SQL脚本文件,再把需要合并的SQL脚本文件内容复制到新建的SQL文件中.当初,我合并脚本的

  • 一些有用的sql语句整理 推荐收藏

    1.说明:创建数据库 CREATE DATABASE database-name 2.说明:删除数据库 drop database dbname 3.说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4.说明:创建新表 cr

  • SQLServer行列互转实现思路(聚合函数)

    有时候会碰到行转列的需求(也就是将列的值作为列名称),通常我都是用 CASE END + 聚合函数来实现的. 如下: declare @t table (StudentName nvarchar(20), Subject nvarchar(20), Score int) Insert into @t (StudentName,Subject,Score) values ( '学生A', '中文', 80 ); Insert into @t (StudentName,Subject,Score)

  • Pandas直接读取sql脚本的方法

    之前有群友反应同事给了他一个几百MB的sql脚本,导入数据库再从数据库读取数据有点慢,想了解下有没有可以直接读取sql脚本到pandas的方法. 解析sql脚本文本文件替换成csv格式并加载 我考虑了一下sql脚本也就只是一个文本文件而已,而且只有几百MB,现代的机器足以把它一次性全部加载到内存中,使用python来处理也不会太慢. 我简单研究了一下sql脚本的导出格式,并根据格式写出了以下sql脚本的读取方法. 注意:该读取方法只针对SQLyog导出的mysql脚本测试,其他数据库可能代码需要

  • flyway实现java 自动升级SQL脚本的问题及解决方法

    为什么要用Flyway 在日常开发中,我们经常会遇到下面的问题: 自己写的SQL忘了在所有环境执行: 别人写的SQL我们不能确定是否都在所有环境执行过了: 有人修改了已经执行过的SQL,期望再次执行: 需要新增环境做数据迁移: 每次发版需要手动控制先发DB版本,再发布应用版本; 其它场景... 由于项目需求的变化,或者前期设计缺陷,导致在后期需要修改数据库,这应该是一个比较常见的事情,如果项目还没上线,你可能把表删除了重新创建,但是如果项目已经上线了,就不能这样简单粗暴了,每次运维部署项目,还得

  • 利用SQL脚本导入数据到不同数据库避免重复的3种方法

    前言 相信大家都有所体会,无论何种语言,一旦看见代码中有重复性的代码则想到封装来复用,在SQL同样如此,若我们没有界面来维护而且需要经常进行的操作,我们会写脚本避免下次又得重新写一遍,但是这其中就涉及到一个问题,这个问题我开始也没太在意,直到某时某刻,老大看到我写的脚本后笑着问了一句,你的脚本可否重复执行,我懵逼了,很显然不能,如果不能避免这种情况发生,比如进行插入操作,当下次其他同事来执行其脚本时可能会插入重复数据,如果是在线上那就傻逼了,所以老大又给我上了一课,从此之后每次写脚本都加逻辑判断

随机推荐