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