Search
#純靠北工程師3xp
----------
原來這樣簡單的SQL
在SQL Server 2005 是不支護。
INSERT INTO Table (Name, Location) VALUES
('Name1', 'Location1'),
('Name2', 'Location2'),
('Name3', 'Location3'), etc...
----------
🗳️ [群眾審核] https://kaobei.engineer/cards/review
👉 [GitHub Repo] https://github.com/init-engineer/init.engineer
📢 [匿名發文] https://kaobei.engineer/cards/create
🥙 [全平台留言] https://kaobei.engineer/cards/show/5101
有在使用 MS SQL server 的朋友，如果有需要找到 identity 跳號的資料，可以參考一下新的寫法，查詢成本省很大。
#長知識
【SQL Tips】之【了解為何identity會發生不連續號碼與快速找出那些號碼是跳號】20171224
**identity保證唯一，不保證連續**
**使用SARG規則與SET BASED方式找出不連續號碼**
**最後那三種找出不連續的技巧，成本比如下
50%(SELF JOIN搭配NOT IN) : 32%(SELF JOIN搭配 OFFSET 函數) : 18%(LAG 函數) **
許多SQL Server開發人員經常會使用identity自動產生連續編號，然而該identity卻有一個特質是發生交易退回(rollback)則會產生跳號的狀況，以下的狀況就是identity碰到交易退回後所產生的跳號情況。
***
use tempdb
go
if object_id('tblNum') is not null
drop table tblNum
go
create table tblNum
(c1 int identity ,
c2 nvarchar(30) unique --防止重複
)
go
insert into tblNum(c2) values ('lewis1')
insert into tblNum(c2) values ('lewis2')
insert into tblNum(c2) values ('lewis3')
insert into tblNum(c2) values ('lewis3') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis5')
insert into tblNum(c2) values ('lewis5') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis5') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis8')
insert into tblNum(c2) values ('lewis9')
insert into tblNum(c2) values ('lewis10')
go
select * from tblNum
GO
--結果
c1 c2
1 lewis1
2 lewis2
3 lewis3
5 lewis5 <--發生跳號
8 lewis8 <--發生跳號
9 lewis9
10 lewis10
***
基本上單一執行INSERT UPDATE DELETE 就是一種隱性交易，上述的範例就是因為條件約束(constraint)的unique緣故，當輸入的文字發生重複的時候，就會自動退回交易，緊接著identity也隨之發生跳號的狀況。
當瞭解identity會有發生跳號的特質之後，接下來分享三種從SQL Server 2005開始使用的抓取跳號的範例，以及從SQL Server 2012開始支援的Windows Offset函數抓取跳號的技巧。這三種技巧都是使用set-based的方式，不使用WHILE迴圈去比對資料的連續性，值得一試。
**從SQL Server 2005支援的抓取跳號的技巧
--使用自我查詢產生搭配CTE
--使用SET BASED技巧取代迴圈處理
WITH DS AS (
SELECT DS1.* FROM tblNum as DS1 JOIN
tblNum as DS2
on DS1.c1 = DS2.c1+1 )
SELECT * FROM tblNum
WHERE c1 NOT IN (SELECT TOP(1) c1 FROM tblNum ORDER BY c1)
EXCEPT
SELECT * FROM DS
GO
**從SQL Server 2012支援的抓取跳號的技巧
--使用自我查詢產生搭配OFFSET
--使用SET BASED技巧取代迴圈處理
WITH DS AS (
SELECT DS1.* FROM tblNum as DS1 JOIN
tblNum as DS2
on DS1.c1 = DS2.c1+1 ),
DS2 AS (SELECT * FROM tblNum ORDER BY 1 OFFSET 1 ROW )
SELECT * FROM DS2
EXCEPT
SELECT * FROM DS
GO
**從SQL Server 2012支援的抓取跳號的技巧
--使用自我查詢產生搭配LAG的Windows Offset技巧
--使用SET BASED技巧取代迴圈處理
WITH DIFF AS (
select *,LAG(c1, 1,0) OVER ( ORDER BY c1) as previous
,c1 - LAG(c1, 1,0) OVER ( ORDER BY c1) as diff
from tblNum
)
SELECT c1,c2 FROM DIFF WHERE diff>1
GO
--結果就是 5/8 之前就發生跳號
c1 c2
5 lewis5
8 lewis8
最後那三種找出不連續的技巧，成本比如下
50%(SELF JOIN搭配NOT IN) : 32%(SELF JOIN搭配 OFFSET) : 18%(LAG)
NOT IN/NOT EXISTS/EXCEPT 使用注意事項
#SQL
【SQL Tips】之【 NULL處理技巧，使用NOT IN /NOT EXISTS/EXCEPT】
許多時候，兩個資料表要找出差異值的時候，許多人第一直覺就是使用【NOT IN】，當然大部分時候都不會有狀況，但是碰到NOT IN的子查詢資料值，如果有NULL，就全盤皆輸，意思就是找不出任何差異。這樣在小量資料可以藉由眼力觀察的狀況下，還可以找出這樣寫法NOT IN的危險地方，但是碰到背景程式，或是資料量多的時候，幾乎無法觀察到這樣危險。所以，告訴自己不要再用【NOT IN】去找出兩邊資料差異。
反倒是要使用【NOT EXISTS】寫法，雖然是複雜一點，但是跨越SQL Server與Oracle兩種資料庫，都是可以正常找出兩邊資料差異值，不擔心NOT EXISTS的基礎資料表有NULL值狀況。另外值得一提就是，需多人會直接使用SQL Server的【EXCEPT】與Oracle的【MINUS】方式，要留意再留意，這樣的方式雖然可以找出差異值，但是針對回傳值，會自動進行重複資料列移除。
【SQL Server Code】
if object_id('x') is not null
drop table x
go
--建立比對基礎資料
create table x(a int)
go
insert into x values(1)
insert into x values(1) --注意重複
insert into x values(NULL) --注意NULL
go
if object_id('y') is not null
drop table y
go
--建立簡單比對來源資料
create table y(b int not null)
go
insert into y values(1)
insert into y values(1)
insert into y values(2)
insert into y values(2)
insert into y values(2) --注意三個2
go
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--【預期要回傳三個2】
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x)
GO
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b)
GO
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
EXCEPT
SELECT a FROM x
GO
【Oracle Code】
drop table x purge;
--建立比對基礎資料
create table x(a int);
insert into x values(1);
insert into x values(1); --注意重複
insert into x values(NULL); --注意NULL
drop table y purge;
--建立簡單比對來源資料
create table y(b int not null);
insert into y values(1);
insert into y values(1);
insert into y values(2);
insert into y values(2);
insert into y values(2); --注意三個2
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x);
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b);
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
MINUS
SELECT a FROM x;
ดาวน์โหลด PostgreSQL script ไฟล์ที่ใช้ในคลิปได้ที่ ► https://bit.ly/3oo4iSf
เชิญสมัครเป็นสมาชิกของช่องนี้ได้ที่ ► https://www.youtube.com/subscription_center?add_user=prasertcbs
สอน PostgreSQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGi_NqmIu43B-PsxA0wtnyH
สอน MySQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GFmJDsZipFCrY6L-0RrBYLT
สอน Microsoft SQL Server 2012, 2014, 2016, 2017 ► https://www.youtube.com/playlist?list=PLoTScYm9O0GH8gYuxpp-jqu5Blc7KbQVn
สอน SQLite ► https://www.youtube.com/playlist?list=PLoTScYm9O0GHjYJA4pfG38M5BcrWKf5s2
สอน SQL สำหรับ Data Science ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGq8M6HO8xrpkaRhvEBsQhw
การเชื่อมต่อกับฐานข้อมูล (SQL Server, MySQL, SQLite) ด้วย Python ► https://www.youtube.com/playlist?list=PLoTScYm9O0GEdZtHwU3t9k3dBAlxYoq59
การใช้ Excel ในการทำงานร่วมกับกับฐานข้อมูล (SQL Server, MySQL, Access) ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGA2sSqNRSXlw0OYuCfDwYk
#prasertcbs_SQL #prasertcbs #prasertcbs_PostgreSQL
เชิญสมัครเป็นสมาชิกของช่องนี้ได้ที่ ► https://www.youtube.com/subscription_center?add_user=prasertcbs
สอน MySQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GFmJDsZipFCrY6L-0RrBYLT
สอน PostgreSQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGi_NqmIu43B-PsxA0wtnyH
สอน Microsoft SQL Server 2012, 2014, 2016, 2017 ► https://www.youtube.com/playlist?list=PLoTScYm9O0GH8gYuxpp-jqu5Blc7KbQVn
สอน SQLite ► https://www.youtube.com/playlist?list=PLoTScYm9O0GHjYJA4pfG38M5BcrWKf5s2
สอน SQL สำหรับ Data Science ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGq8M6HO8xrpkaRhvEBsQhw
การเชื่อมต่อกับฐานข้อมูล (SQL Server, MySQL, SQLite) ด้วย Python ► https://www.youtube.com/playlist?list=PLoTScYm9O0GEdZtHwU3t9k3dBAlxYoq59
การใช้ Excel ในการทำงานร่วมกับกับฐานข้อมูล (SQL Server, MySQL, Access) ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGA2sSqNRSXlw0OYuCfDwYk
#prasertcbs_SQL #prasertcbs #prasertcbs_MySQL
-- command
create user alpha with encrypted password '1234';
\c demo
grant select on table public.benjerry to alpha;
grant select, insert, update, delete on table public.menu to alpha;
grant select, insert, update, delete on all tables in schema public to alpha;
revoke all privileges on all tables in schema public from alpha;
grant all privileges on all tables in schema public to alpha;
ดาวน์โหลด saturn database (saturn.tar) ได้ที่ ► http://bit.ly/2E2uY7a
เชิญสมัครเป็นสมาชิกของช่องนี้ได้ที่ ► https://www.youtube.com/subscription_center?add_user=prasertcbs
สอน PostgreSQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGi_NqmIu43B-PsxA0wtnyH
สอน MySQL ► https://www.youtube.com/playlist?list=PLoTScYm9O0GFmJDsZipFCrY6L-0RrBYLT
สอน Microsoft SQL Server 2012, 2014, 2016, 2017 ► https://www.youtube.com/playlist?list=PLoTScYm9O0GH8gYuxpp-jqu5Blc7KbQVn
สอน SQLite ► https://www.youtube.com/playlist?list=PLoTScYm9O0GHjYJA4pfG38M5BcrWKf5s2
สอน SQL สำหรับ Data Science ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGq8M6HO8xrpkaRhvEBsQhw
การเชื่อมต่อกับฐานข้อมูล (SQL Server, MySQL, SQLite) ด้วย Python ► https://www.youtube.com/playlist?list=PLoTScYm9O0GEdZtHwU3t9k3dBAlxYoq59
การใช้ Excel ในการทำงานร่วมกับกับฐานข้อมูล (SQL Server, MySQL, Access) ► https://www.youtube.com/playlist?list=PLoTScYm9O0GGA2sSqNRSXlw0OYuCfDwYk
#prasertcbs_SQL #prasertcbs #prasertcbs_PostgreSQL
使用SQL Server Management Studio 管理介面操作執行T-SQL 指令（使用DDL 提供的 ... Security：開始建立前先檢查是否有CREATE TABLE 及ALTER 的權限 ... ... <看更多>
create table sql server 在 Create table (structure) from existing table - Stack Overflow 的推薦與評價
... <看更多>
相關內容