postgrepsql查询2个列相除

行列互转

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 ' )  

 

Date 和 Time 样式

 

不带世纪数位 (yy) (1)	带世纪数位 (yyyy)	标准	输入/输出 (3)
-	0 或 100 (1,2)	默 认	mon dd yyyy hh:miAM（或 PM）
1	101	美 国	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英 国/法国	dd/mm/yyyy
4	104	德 国	dd.mm.yy
5	105	意 大利	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	mon dd, yy
8	108	-	hh:mi:ss
-	9 或 109 (1,2)	默 认设置 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美 国	mm-dd-yy
11	111	日 本	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 或 113 (1,2)	欧 洲默认设置 + 毫秒	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 或 120 (2)	ODBC 规范	yyyy-mm-dd hh:mi:ss(24h)
-	21 或 121 (2)	ODBC 规范（带毫秒）	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy- mm-ddThh:mi:ss.mmm（无空格）
-	127(6, 7)	带时区 Z 的 ISO8601。	yyyy-mm-ddThh:mi:ss.mmmZ （无 空格）
-	130 (1,2)	回历 (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	回历 (5)	dd/mm/yy hh:mi:ss:mmmAM

 

-- 语句及查询结果：
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  0 ):  05  16  2006  10 :57AM
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  1 ):  05 / 16 / 06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  2 ):  06.05 . 16
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  3 ):  16 / 05 / 06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  4 ):  16.05 . 06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  5 ):  16 - 05 - 06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  6 ):  16  05  06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  7 ):  05  16 ,  06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  8 ):  10 : 57 : 46
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  9 ):  05  16  2006  10 : 57 : 46 :827AM
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  10 ):  05 - 16 - 06
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  11 ):  06 / 05 / 16
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  12 ):  060516
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  13 ):  16  05  2006  10 : 57 : 46 : 937
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  14 ):  10 : 57 : 46 : 967
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  20 ):  2006 - 05 - 16  10 : 57 : 47
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  21 ):  2006 - 05 - 16  10 : 57 : 47.157
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  22 ):  05 / 16 / 06  10 : 57 : 47  AM
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  23 ):  2006 - 05 - 16
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  24 ):  10 : 57 : 47
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  25 ):  2006 - 05 - 16  10 : 57 : 47.250
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  100 ):  05  16  2006  10 :57AM
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  101 ):  05 / 16 / 2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  102 ):  2006.05 . 16
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  103 ):  16 / 05 / 2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  104 ):  16.05 . 2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  105 ):  16 - 05 - 2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  106 ):  16  05  2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  107 ):  05  16 ,  2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  108 ):  10 : 57 : 49
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  109 ):  05  16  2006  10 : 57 : 49 :437AM
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  110 ):  05 - 16 - 2006
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  111 ):  2006 / 05 / 16
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  112 ):  20060516
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  113 ):  16  05  2006  10 : 57 : 49 : 513
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  114 ):  10 : 57 : 49 : 547
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  120 ):  2006 - 05 - 16  10 : 57 : 49
SELECT  CONVERT ( varchar ( 100 ),  GETDATE (),  121 ):  2006

对上面进行动态生成字符串：

declare  @sql1  nvarchar ( 200 ), @sql2  nvarchar ( 200 )
declare  @count  nvarchar ( 100 );
set  @sql1  =  ' SELECT CONVERT(varchar(100), GETDATE(), 0) '
set  @sql2  =  ' SELECT @count = CONVERT(varchar(100), GETDATE(), 0) '
exec  sp_executesql   @sql2 ,N ' @count nvarchar(50) out ' , @count  out
print  @sql1  + ' ： ' +  @count

-- SQL Server 仅保证往返转换（即从原始数据类型进行转换后又返回原始数据类型的转换）在各版本间产生相 同值。
DECLARE  @myval  decimal  ( 5 ,  2 )
SET  @myval  =  193.57
SELECT  CAST ( CAST ( @myval  AS  varbinary ( 20 ))  AS  decimal ( 10 , 5 ))
--  Or, using CONVERT
SELECT  CONVERT ( decimal ( 10 , 5 ),  CONVERT ( varbinary ( 20 ),  @myval ))
-- 输出193.57000
-- 输 出193.57000

-- bigint数据类型的字段截取（其它类型也一样）
select  substring ( CONVERT ( varchar ( 15 ),字段 名), 11 , 9 )  from  表名
select  substring ( cast (字段名  as  varchar ( 50 ), 6 , 9 ))  from  表名

 

 SQL中的相除

-- SQL中的相除
SELECT  CASE  WHEN  ISNULL (A + B, 0 ) <> 0  THEN  
LTRIM ( CONVERT ( DEC ( 18 , 2 ),A * 100.0 / (A + B))) + ' % '    ELSE  ''  END  AS  ' 百分数 '  
FROM  TB 

 

-- 百分比的不同格式
select  
LTRIM ( CONVERT ( DEC ( 18 , 2 ), 42 * 100.0 / 96 )) + ' % '  AS  ' 百分数string '  , -- DEC=decimal
CONVERT ( decimal ( 10 , 2 ), 42 * 100.0 / 96 )  AS  ' 百分数dec ' ,  -- 100 与 100.0是不一样的
CONVERT ( decimal ( 10 , 2 ), 42 * 100 / 96 )  AS  ' 没有保留到小数点 '
-- from Tb
order  by  百分数dec  desc
-- 43.75%    43.75    43.00
 
-- 方法二：
Select  ( Convert ( varchar ( 50 ), Round ( 42 * 100.0 / 96 , 3 )) + ' % ' )  as  百分比  -- from A
-- 43.750000%

四舍五入

/* ROUND ( numeric_expression , length [ ,function ] )
function 必 须为 tinyint、smallint  或 int。
如果省略 function 或其值为 0（默认值），则将舍 入 numeric_expression。
如果指定了0以外的值，则将截断 numeric_expression。 */
SELECT  ROUND ( 150.45648 ,  2 ); -- 保留小数点后两位，需要四舍五入
SELECT  ROUND ( 150.45648 ,  2 ,  0 ); -- 保留小数点后两位，0为默认值，表示进行四舍五入
SELECT  ROUND ( 150.45648 ,  2 ,  1 ); -- 保留小数点后两位，不需要四舍五入， 这里除0以外都是有同样的效果
SELECT  ROUND ( 150.45648 ,  2 ,  2 ); -- 保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果
-- 150.46000
-- 150.45000
-- 150.45000
-- 150.45000

 

对字段出现NULL值的处理

-- 判断某些字段是否为空
-- case
select  case  when  ' 字段名 '  is  null  then  ' \N '  else  convert ( varchar ( 20 ), ' 字段名 ' )  end  as  ' NewName '
select  case  when  null  is  null  then  ' \N '  else  convert ( varchar ( 20 ), null )  end  as  ' NewName '

-- SQL Server 2005：coalesce
select  coalesce ( ' 字符串类型字段 ' , ' \N ' )  as  ' NewName '
select  coalesce ( convert ( varchar ( 20 ), ' 非字符串类型字段 ' ), ' \N ' )  as  ' NewName '
select  coalesce ( convert ( varchar ( 20 ), null ), ' \N ' )  as  ' NewName '

-- coalesce,返回其 参数中的第一个非空表达式
select  Coalesce ( null , null , 1 , 2 , null ) union
select  Coalesce ( null , 11 , 12 , 13 , null ) union
select  Coalesce ( 111 , 112 , 113 , 114 , null )

 

count 的几种情况

-- 第一种
select  count ( * )  from  tablename

-- 第二种
select  count (ID)  from  tablename

-- 第三种,1换成其它值也是可以的
select  count ( 1 )  from  tablename

/*
--第四种,这个不存在性能问题
idint 表ID(如果 indid = 0 或255)。否则为索引所属表 的ID
Indid smallint 索引ID：
0＝表
1＝聚簇索引
>1＝非聚簇索引
255＝具有 text或image数据的表条目。
rows int 基于indid=0 和 indid=1地数据级行数，该值对于indid>1重 复。如果indid=255，rows设置为0。
当表没有聚簇索引时，Indid = 0 否则为 1。 
*/
select  rows,indid  from  sysindexes  where  id  =  object_id ( ' tablename ' )  and  indid  in  ( 0 , 1 ) 

Union all

 

-- 把两个相同结构的表union后插入到一个新表中
select  *  
into  table_now
from  table_1
union  all  select  *  from  table_2
-- Truncate table table_now

 

查 看数据库缓存的SQL

-- 适用MSSQL2000、MSSQL2005
use  master
declare  @dbid  int
Select  @dbid  =  dbid  from  sysdatabases  where  name  =  ' Test ' -- 修改成数据库的名称
select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name (dbid)  as  DatabaseName,SQL
from  syscacheobjects
where  dbid = @dbid
order  by  dbid,useCounts  desc ,objtype

删除计划缓存

-- 删除整个数据库的计划缓存
DBCC  FREEPROCCACHE

-- 删除某个数据库的计划缓存
USE  master
DECLARE  @dbid  INT
SELECT  @dbid = dbid  FROM  sysdatabases  WHERE  NAME  =  ' 表名 '
DBCC  FLUSHPROCINDB ( @dbid )

导出时加入特殊字符

情况一：全部字段都需要加字符，在这里设置【文本限定符】就可以了。

情况二：

-- 某些特殊的字段需要加单引号（或者其它符号） 
SELECT
       [ ID ]
      , '''' + convert ( varchar ( 25 ), [ ts ] , 121 ) + ''''  as  [ ts ]
      , '''' + convert ( varchar ( 25 ), [ otherParty ] , 121 ) + ''''  as  [ otherParty ]
      , '''' + convert ( varchar ( 25 ), [ StartTime ] , 121 ) + ''''  as  [ StartTime ]
      , [ CcCause ]
      , [ RrCause ]
   FROM  [ 表 ]

效果：74983006,'2010-03-09 23:59:10.000'

 

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 不能用于参与了索引视图的表。 

 

其它

-- 查看内存状态
dbcc  memorystatus

-- 查看哪个引起的阻塞，blk 
EXEC  sp_who active 

-- 查看锁住了那个资源id，objid
EXEC  sp_lock 

-- 当前查询 分析器SPID
-- 查询分析器的状态栏中可以看到
-- 比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控

 

参考文献

数据库表行转列，列转行终极方案

行列互转（动态脚本）

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

非常有用的sql脚本 

SQL中OpenDataSource与OpenRowSet的应用

SQL中CONVERT转化函数的用法

CAST 和 CONVERT (Transact-SQL) 

精度、小数位数和长度 (Transact-SQL) 

ROUND (Transact-SQL)