工作中有許多比較常用的SQL腳本，今天開(kāi)始分幾章分享給大家相味。
1如迟、行轉(zhuǎn)列的用法PIVOT
CREATE table test
(id int,name nvarchar(20),quarter int,number int)
insert into test values(1,N'蘋(píng)果',1,1000)
insert into test values(1,N'蘋(píng)果',2,2000)
insert into test values(1,N'蘋(píng)果',3,4000)
insert into test values(1,N'蘋(píng)果',4,5000)
insert into test values(2,N'梨子',1,3000)
insert into test values(2,N'梨子',2,3500)
insert into test values(2,N'梨子',3,4200)
insert into test values(2,N'梨子',4,5500)
select * from test
結(jié)果：
select ID,NAME,
[1] as '一季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
from
test
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt
結(jié)果：
2、列轉(zhuǎn)行的用法UNPIOVT
create table test2
(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,'蘋(píng)果',1000,2000,4000,5000)
insert into test2 values(2,'梨子',3000,3500,4200,5500)
select * from test2
(提示：可以左右滑動(dòng)代碼)
結(jié)果：
--列轉(zhuǎn)行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
結(jié)果：
3攻走、字符串替換SUBSTRING/REPLACE
SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
結(jié)果：
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
結(jié)果：
SELECT REPLACE('12345678@qq.com','1234567','******')
結(jié)果：
4殷勘、查詢一個(gè)表內(nèi)相同紀(jì)錄 HAVING
如果一個(gè)ID可以區(qū)分的話，可以這么寫(xiě)
SELECT * FROM HR.Employees
結(jié)果：
select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)
結(jié)果：
對(duì)比一下發(fā)現(xiàn)昔搂，ID為1,2的被過(guò)濾掉了玲销，因?yàn)樗麄冎挥幸粭l記錄
如果幾個(gè)ID才能區(qū)分的話，可以這么寫(xiě)
select * from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
group by title,titleofcourtesy
having count(1)>1)
結(jié)果：
title在和titleofcourtesy進(jìn)行拼接后符合條件的就只有ID為6,7,8,9的了
5摘符、把多行SQL數(shù)據(jù)變成一條多列數(shù)據(jù)贤斜，即新增列
SELECT
id,
name,
SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度',
SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度',
SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'
FROM test
GROUP BY id,name
結(jié)果：
我們將原來(lái)的4列增加到了6列策吠。細(xì)心的朋友可能發(fā)現(xiàn)了這個(gè)結(jié)果和上面的行轉(zhuǎn)列怎么一模一樣?其實(shí)上面的行轉(zhuǎn)列是省略寫(xiě)法，這種是比較通用的寫(xiě)法瘩绒。
6猴抹、表復(fù)制
語(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ù)制恬总。如果是使用【編寫(xiě)查詢以指定要傳輸?shù)臄?shù)據(jù)】前普，那么在大數(shù)據(jù)表的復(fù)制就會(huì)有問(wèn)題?因?yàn)閺?fù)制到一定程度就不再動(dòng)了，內(nèi)存爆了?它也沒(méi)有寫(xiě)入到表中壹堰。而使用上面3種語(yǔ)法直接執(zhí)行是會(huì)馬上刷新到數(shù)據(jù)庫(kù)表中的拭卿，你刷新一下mdf文件就知道了。
7贱纠、利用帶關(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 ='001'
8峻厚、連接遠(yuǎn)程服務(wù)器
--方法1：
select * from openrowset(
'SQLOLEDB',
'server=192.168.0.1;uid=sa;pwd=password',
'SELECT * FROM dbo.test')
--方法2：
select * from openrowset(
'SQLOLEDB',
'192.168.0.1';
'sa';
'password',
'SELECT * FROM dbo.test')
當(dāng)然也可以參考以前的示例，建立DBLINK進(jìn)行遠(yuǎn)程連接
9并巍、Date 和 Time 樣式 CONVERT
CONVERT() 函數(shù)是把日期轉(zhuǎn)換為新數(shù)據(jù)類型的通用函數(shù)。
CONVERT() 函數(shù)可以用不同的格式顯示日期/時(shí)間數(shù)據(jù)换途。
語(yǔ)法
CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 規(guī)定目標(biāo)數(shù)據(jù)類型(帶有可選的長(zhǎng)度)懊渡。data_to_be_converted 含有需要轉(zhuǎn)換的值。style 規(guī)定日期/時(shí)間的輸出格式军拟。
可以使用的 style 值：
SELECT CONVERT(varchar(100), GETDATE(), 0)
--結(jié)果：
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 1)
--結(jié)果：
12/07/20
SELECT CONVERT(varchar(100), GETDATE(), 2)
--結(jié)果：
20.12.07
SELECT CONVERT(varchar(100), GETDATE(), 3)
--結(jié)果：
07/12/20
SELECT CONVERT(varchar(100), GETDATE(), 4)
--結(jié)果：
07.12.20
SELECT CONVERT(varchar(100), GETDATE(), 5)
--結(jié)果：
07-12-20
SELECT CONVERT(varchar(100), GETDATE(), 6)
--結(jié)果：
07 12 20
SELECT CONVERT(varchar(100), GETDATE(), 7)
--結(jié)果：
12 07, 20
SELECT CONVERT(varchar(100), GETDATE(), 8)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 9)
--結(jié)果：
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 10)
--結(jié)果：
12-07-20
SELECT CONVERT(varchar(100), GETDATE(), 11)
--結(jié)果：
20/12/07
SELECT CONVERT(varchar(100), GETDATE(), 12)
--結(jié)果：
201207
SELECT CONVERT(varchar(100), GETDATE(), 13)
--結(jié)果：
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 14)
--結(jié)果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 20)
--結(jié)果：
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 21)
--結(jié)果：
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 22)
--結(jié)果：
12/07/20 9:33:18 PM
SELECT CONVERT(varchar(100), GETDATE(), 23)
--結(jié)果：
2020-12-07
SELECT CONVERT(varchar(100), GETDATE(), 24)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 25)
--結(jié)果：
2020-12-07 21:33:18.780
SELECT CONVERT(varchar(100), GETDATE(), 100)
--結(jié)果：
12 7 2020 9:33PM
SELECT CONVERT(varchar(100), GETDATE(), 101)
--結(jié)果：
12/07/2020
SELECT CONVERT(varchar(100), GETDATE(), 102)
--結(jié)果：
2020.12.07
SELECT CONVERT(varchar(100), GETDATE(), 103)
--結(jié)果：
07/12/2020
SELECT CONVERT(varchar(100), GETDATE(), 104)
--結(jié)果：
07.12.2020
SELECT CONVERT(varchar(100), GETDATE(), 105)
--結(jié)果：
07-12-2020
SELECT CONVERT(varchar(100), GETDATE(), 106)
--結(jié)果：
07 12 2020
SELECT CONVERT(varchar(100), GETDATE(), 107)
--結(jié)果：
12 07, 2020
SELECT CONVERT(varchar(100), GETDATE(), 108)
--結(jié)果：
21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 109)
--結(jié)果：
12 7 2020 9:33:18:780PM
SELECT CONVERT(varchar(100), GETDATE(), 110)
--結(jié)果：
12-07-2020
SELECT CONVERT(varchar(100), GETDATE(), 111)
--結(jié)果：
2020/12/07
SELECT CONVERT(varchar(100), GETDATE(), 112)
--結(jié)果：
20201207
SELECT CONVERT(varchar(100), GETDATE(), 113)
--結(jié)果：
07 12 2020 21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 114)
--結(jié)果：
21:33:18:780
SELECT CONVERT(varchar(100), GETDATE(), 120)
--結(jié)果：
2020-12-07 21:33:18
SELECT CONVERT(varchar(100), GETDATE(), 121)
--結(jié)果：
2020-12-07 21:33:18.780
以上內(nèi)容剃执，在工作中比較常用，能記住最好懈息。不能記住就收藏起來(lái)肾档，在需要的時(shí)候查詢即可。
