TSQL利用CASE WHEN處理null值

 
因為有些欄位有null值，select出的資料整筆變成NULL

 

使用CASE WHEN去處理

 

 

 

gist
https://gist.github.com/imrexhuang/3715d6087e26f77577a1b5df937bb38b#file-tsql-case-when-null

 

sqlfiddle
http://sqlfiddle.com/#!18/5eba54/10

UNION和UNION ALL的差別，UNION會移除重複資料

UNION會移除重複資料
UNION ALL會保留重複資料

執行計畫:
UNION會先Concatenation 再Distinct Sort再輸出結果
UNION ALL只有Concatenation就輸出結果

GROUP BY會去掉重複的資料

T-SQL Querying: TOP and OFFSET-FETCH

https://www.microsoftpressstore.com/articles/article.aspx?p=2314819

OFFSET 25 ROWS

從第26筆資料開始(跳過前25筆)

 use Northwind
 GO

SELECT * FROM Orders
ORDER BY OrderID
OFFSET 25 ROWS

OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY

 use Northwind
 GO

SELECT * FROM Orders
ORDER BY OrderID
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY


效果同
SELECT TOP(25) * FROM Orders
ORDER BY OrderID

差別:
TOP不是ANSI SQL，但是TOP還可以搭配PERCENT和WITH TIES很方便
OFFSET是 ANSI SQL

OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

找出51筆到75筆的資料
通常用在分頁
要加ORDER BY

 use Northwind
 GO

SELECT * FROM Orders
ORDER BY OrderID
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY



分頁效果(每頁25筆，顯示第2頁的資料)
DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 2;
SELECT *
FROM Orders
ORDER BY OrderID DESC
OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

(重要)使用WITH TIES語法找出成績排名前8名的學生，因為平分，得到9筆資料

SELECT TOP 8 WITH TIES * FROM students ORDER BY score DESC

因為需求是排名前8名
如果用沒加WITH TIES只會有8筆，導致資料不正確

REF:
https://blog.csdn.net/whaxrl/article/details/51218230

order by (select null)

https://stackoverflow.com/questions/10066819/what-does-order-by-select-null-mean


T-SQL Querying: TOP and OFFSET-FETCH
https://www.microsoftpressstore.com/articles/article.aspx?p=2314819

ORDER BY要搭配indexes使用

資料量大，有index時SQL Server就不用真的去Sort資料
效能會比較好

SELECT TOP (1) PERCENT

SELECT TOP (1) PERCENT *
FROM Orders
ORDER BY orderdate DESC;

取出前1%的資料

TSQL個別取得年月日

SELECT YEAR(GETDATE()) , MONTH(GETDATE()) ,DAY(GETDATE())

CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#g-using-cast-and-convert-with-datetime-data

找出開頭不是數字的資料

開頭不是數字的資料
SELECT * FROM Table_1 WHERE T1 LIKE '[^0-9]%' 

開頭數字的資料
SELECT * FROM Table_1 WHERE T1 LIKE '[0-9]%'

找出開頭第二個字是D的資料 LIKE '_D%'

SELECT * FROM Table_1 WHERE T1 LIKE '_D%'

找出開頭第二個字是D的資料

TRY_CAST

SELECT * FROM  Table_1 WHERE  TRY_CAST(T1 AS INT) > 10
=>可以得到結果

SELECT * FROM  Table_1 WHERE  CAST(T1 AS INT) > 10
=>將 varchar 值 'A         ' 轉換成資料類型 int 時，轉換失敗。

GROUP BY無法使用alias column name

原因：
https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by

SQL is implemented as if a query was executed in the following order:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

問題描述：
SELECT country AS c, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
FROM HR.Employees
WHERE hiredate >= '20140101'
GROUP BY country, YEAR(hiredate)
-- GROUP BY country, yearhired -- 無效的資料行名稱 'yearhired'。
HAVING COUNT(*) > 1
ORDER BY country, yearhired DESC;
--ORDER BY c, yearhired DESC; --可以執行

TSQL的outer apply範例

http://sqlfiddle.com/#!18/d47fe/2


ref:
https://docs.microsoft.com/zh-tw/sql/t-sql/queries/from-transact-sql?view=sql-server-2017

合併查詢結果
http://vito-note.blogspot.com/2013/05/blog-post_134.html

[SQL] APPLY
http://jengting.blogspot.com/2014/03/apply.html

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

SQL Server 2005 新功能-APPLY 運算子
https://blog.xuite.net/sunnysoap/r/15570805-SQL+Server+2005+%E6%96%B0%E5%8A%9F%E8%83%BD-APPLY+%E9%81%8B%E7%AE%97%E5%AD%90 

Email the Auto-growth events that have occurred in the last 24 hours to the DBA

https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

不同資料庫SQL Script語法轉換器

線上版
http://www.sqlines.com/online

軟體版(免安裝)
http://www.sqlines.com/download
SQLines SQL Converter is an open source tool (Apache License 2.0) that helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

原始碼
https://github.com/dmtolpeko/sqlines