行列互轉
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')



 

不帶世紀數位 (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)

 
查看資料庫緩存的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)
 
-------------------华丽分割线-------------------
作者:聽風吹雨
出處:HTTP://gaizai.cnblogs.com/
Date 和 Time 樣式
 
arrow
arrow
    全站熱搜

    戮克 發表在 痞客邦 留言(0) 人氣()