簡單有用的SQL腳本 (行列互轉(zhuǎn),查詢一個表內(nèi)相同紀(jì)錄等)
更新時間：2010年04月12日 00:58:27 作者：
本文推薦的SQL腳本都是很實(shí)用的哦，學(xué)會了，對在工作中得心應(yīng)手，一起來學(xué)習(xí)學(xué)習(xí)吧。
行列互轉(zhuǎn)
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
--行轉(zhuǎn)列
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
--列轉(zhuǎn)行
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查詢一個表內(nèi)相同紀(jì)錄 having
如果一個ID可以區(qū)分的話，可以這么寫
select * from 表 where ID in (
select ID from 表 group by ID having sum(1)>1))
如果幾個ID才能區(qū)分的話，可以這么寫
select * from 表 where ID1+ID2+ID3 in
(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1))
其他回答：數(shù)據(jù)表是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數(shù)據(jù)變成一條多列數(shù)據(jù)，即新增列
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)
表復(fù)制
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（要求目標(biāo)表Table2必須存在，由于目標(biāo)表Table2已經(jīng)存在，所以我們除了插入源表Table1的字段外，還可以插入常量。）
語法3：SELECT vale1, value2 into Table2 from Table1（要求目標(biāo)表Table2不存在，因?yàn)樵诓迦霑r會自動創(chuàng)建表Table2，并將Table1中指定字段數(shù)據(jù)復(fù)制到Table2中。）
語法4：使用導(dǎo)入導(dǎo)出功能進(jìn)行全表復(fù)制。如果是使用【編寫查詢以指定要傳輸?shù)臄?shù)據(jù)】，那么在大數(shù)據(jù)表的復(fù)制就會有問題？因?yàn)閺?fù)制到一定程度就不再動了，內(nèi)存爆了？它也沒有寫入到表中。而使用上面3種語法直接執(zhí)行是會馬上刷新到數(shù)據(jù)庫表中的，你刷新一下mdf文件就知道了。
利用帶關(guān)聯(lián)子查詢Update語句更新數(shù)據(jù)
--方法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
連接遠(yuǎn)程服務(wù)器
--方法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中的一個刪除數(shù)據(jù)表內(nèi)容的語句，用法是：
Truncate table 表名 速度快,而且效率高,因?yàn)?
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。
對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
TRUNCATE TABLE 不能用于參與了索引視圖的表。
參考文獻(xiàn)
數(shù)據(jù)庫表行轉(zhuǎn)列，列轉(zhuǎn)行終極方案
行列互轉(zhuǎn)（動態(tài)腳本）
SELECT INTO 和 INSERT INTO SELECT 兩種表復(fù)制語句
非常有用的sql腳本
作者：聽風(fēng)吹雨
復(fù)制代碼 代碼如下:
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
--行轉(zhuǎn)列
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
--列轉(zhuǎn)行
select id,name,quarter,profile
from
test2
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
sql替換字符串 substring replace
復(fù)制代碼 代碼如下:
--例子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查詢一個表內(nèi)相同紀(jì)錄 having
如果一個ID可以區(qū)分的話，可以這么寫
復(fù)制代碼 代碼如下:
select * from 表 where ID in (
select ID from 表 group by ID having sum(1)>1))
如果幾個ID才能區(qū)分的話，可以這么寫
復(fù)制代碼 代碼如下:
select * from 表 where ID1+ID2+ID3 in
(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)>1))
其他回答：數(shù)據(jù)表是zy_bho,想找出ZYH字段名相同的記錄
復(fù)制代碼 代碼如下:
--方法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數(shù)據(jù)變成一條多列數(shù)據(jù)，即新增列
復(fù)制代碼 代碼如下:
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)
表復(fù)制
復(fù)制代碼 代碼如下:
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（要求目標(biāo)表Table2必須存在，由于目標(biāo)表Table2已經(jīng)存在，所以我們除了插入源表Table1的字段外，還可以插入常量。）
語法3：SELECT vale1, value2 into Table2 from Table1（要求目標(biāo)表Table2不存在，因?yàn)樵诓迦霑r會自動創(chuàng)建表Table2，并將Table1中指定字段數(shù)據(jù)復(fù)制到Table2中。）
語法4：使用導(dǎo)入導(dǎo)出功能進(jìn)行全表復(fù)制。如果是使用【編寫查詢以指定要傳輸?shù)臄?shù)據(jù)】，那么在大數(shù)據(jù)表的復(fù)制就會有問題？因?yàn)閺?fù)制到一定程度就不再動了，內(nèi)存爆了？它也沒有寫入到表中。而使用上面3種語法直接執(zhí)行是會馬上刷新到數(shù)據(jù)庫表中的，你刷新一下mdf文件就知道了。
利用帶關(guān)聯(lián)子查詢Update語句更新數(shù)據(jù)
復(fù)制代碼 代碼如下:
--方法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
連接遠(yuǎn)程服務(wù)器
復(fù)制代碼 代碼如下:
--方法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中的一個刪除數(shù)據(jù)表內(nèi)容的語句，用法是：
Truncate table 表名 速度快,而且效率高,因?yàn)?
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE TABLE 通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。
對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
TRUNCATE TABLE 不能用于參與了索引視圖的表。
參考文獻(xiàn)
數(shù)據(jù)庫表行轉(zhuǎn)列，列轉(zhuǎn)行終極方案
行列互轉(zhuǎn)（動態(tài)腳本）
SELECT INTO 和 INSERT INTO SELECT 兩種表復(fù)制語句
非常有用的sql腳本
作者：聽風(fēng)吹雨
相關(guān)文章
sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享
最近發(fā)現(xiàn)現(xiàn)有框架的通用查詢存儲過程的性能慢,經(jīng)過使用臨時表的確提高了性能,現(xiàn)把方法分享如下2014-01-01
能實(shí)現(xiàn)效果的方法有很多種,這里借用Js的正則表達(dá)式來處理.2009-08-08
SQL Server 2005 鏡像構(gòu)建手冊(sql2005數(shù)據(jù)庫同步鏡像方案)
為了網(wǎng)站數(shù)據(jù)庫安全，我們需要備份數(shù)據(jù)，這里為大家分享下sql2005數(shù)據(jù)庫同步鏡像方案，需要的朋友可以參考下2014-08-08
本文將介紹兩種清除日志的方法:自動清除法與手動清除法，需要了解的朋友可以參考下2012-12-12
SQL Server 2005的cmd_shell組件的開啟方法
這篇文章介紹了SQL Server 2005的cmd_shell組件的開啟方法，有需要的朋友可以參考一下2013-09-09
簡述SQL Server 2005數(shù)據(jù)庫鏡像相關(guān)知識
本文我們主要介紹了SQL Server 2005數(shù)據(jù)庫鏡像的相關(guān)知識，包括數(shù)據(jù)庫鏡像的優(yōu)點(diǎn)、數(shù)據(jù)庫鏡像的工作方式等,需要的朋友可以參考下2015-08-08
SQLServer Job運(yùn)行成功或失敗時發(fā)送電子郵件通知的圖文教程
雖然我們可以通過Job日志來查看SQL Server Job運(yùn)行成功或是失敗，但是人工主動去查沒有及時性，也不智能化。下面簡單介紹一下如何設(shè)置SQL Server的郵件通知，讓Job完成后，自動發(fā)送狀態(tài)郵件。2009-09-09
SQL2005CLR函數(shù)擴(kuò)展-深入環(huán)比計算的詳解
環(huán)比就是本月和上月的差值所占上月值的比例。在復(fù)雜的olap計算中我們經(jīng)常會用到同比環(huán)比等概念，要求的上個維度的某個字段的實(shí)現(xiàn)語句非常簡練，比如ssas的mdx語句類似[維度].CurrentMember.Prevmember就可以了2013-06-06
sql2005附加數(shù)據(jù)庫操作步驟(sql2005還原數(shù)據(jù)庫)
本文介紹了sql2005附加數(shù)據(jù)庫的操作步驟，簡單幾步就可以完成，大家參考使用吧2014-01-01
SQLServer2005與SQLServer2008數(shù)據(jù)庫同步圖文教程
要實(shí)現(xiàn)SQLServer2005與2005的數(shù)據(jù)庫同步的話，直接用鏡像就可以實(shí)現(xiàn)。但是如果同步 SQLServer2008的話，2005的實(shí)例是連接不上08的。低版本的無法連接高版本的。所以我們可以通過復(fù)制的方式，也就是所謂的訂閱發(fā)布的方法來實(shí)現(xiàn)兩個不同版本數(shù)據(jù)庫的數(shù)據(jù)同步。2011-09-09