行列互转

 

行列互转

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

 

-------------------华丽分割线-------------------

作者： 听风吹雨

出处： http://gaizai.cnblogs.com/

版权：本文版权归作者和博客园共有

转载：欢迎转载，不过记得留下买路钱

Blog： http://gaizai.i170.com/

格言：不喜欢是因为你不会 && 因为会所以喜欢

 

转载于:https://www.cnblogs.com/czjone/archive/2010/04/10/1708889.html