【SQL】用SQL傳回1-100

修改自暗黑執行緒大大的CODE //查詢SELECT DISTINCT NUMBER FROM master.dbo.spt_values WHERE name IS NULL可傳回一段0到2047的數字//

SELECT  CONVERT(VARCHAR(4), Num)+'m' as title, CONVERT(VARCHAR(4), Num) as value

FROM 

(

    SELECT DISTINCT NUMBER AS Num

    FROM master.dbo.spt_values

    WHERE name IS NULL

) NumberPool

WHERE Num BETWEEN 1 AND 100

ORDER BY Num 

執行結果

【SQL】SQL抓月初月尾

select  year(  GETDATE()) --目前年
select convert(varchar(10),getdate(),111)--月底
select dateadd(month,datediff(month,'1900-01-01',getdate()),'1900-01-01') --當月初
select  dateadd(month,datediff(month,0,getdate()),0)  --當月初
select dateadd(month,datediff(month,-1,getdate()),-1)--當月底
SELECT DATEADD(MM, DATEDIFF(MM,0,(SELECT DATEADD(MM,-1, GETDATE()))),0)    --上個月初 

【SQL】依當年度週別取得日期

--MSSQL
with ach as
(
 select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date
 from master..spt_values
 where [type] = 'p' and number between 0 and 400 
),cte as
(
 select *
 from ach
 where year(date) = year(getdate())
), abc as
(
select datepart(wk,date) as wk,min(date)  mindate,max(date)  maxdate
from cte
group by datepart(wk,date)
)


select CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), -1),112 ) as int ) as 'SUNDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 0),112 ) as int ) as 'MONDAY' 

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 1),112 ) as int ) as 'TUESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 2),112 ) as int ) as 'WENDNESDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 3),112 ) as int ) as 'THURSDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 4),112 ) as int ) as 'FRIDAY'

,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, abc.mindate), 5),112 ) as int ) as 'SATURDAY'
from abc
where wk='1' -- 自行代入週別 

【SQL】WHERE 1=1, 1=0

1.WHERE 1=1
小技巧，通常在程式中可以動態的處理SQL語句的方法，
SELECT * FROM DT WHERE 1=1 和 SELECT * FROM DT 其實是一樣的東西

範例

    String a="1";
    String b="2";
   String SQL_STR="SELECT * FROM DT WHERE 1=1";
   if(String.IsNullorEmpty(a))//條件1
   {
      SQL_STR+="AND a='a'";
   }
   if(String.IsNullorEmpty(b))//條件2
   {
      SQL_STR+="AND b='b'";
   }
   //如此就算條件1, 條件2不成立, SQL_STR丟回資料庫處理時也不會產生錯誤

2.WHERE 1=0
SELECT * FROM DT WHERE 1=0 意指只傳回SELECT結果的表格結構
範例

   Create table newT AS SELECT *FROM DT1 WHERE 1=0

將欄位資料結合的寫法

"M.HOLD_GROUP_ID " + DBParameterChar.PlusChar + "' '" + DBParameterChar.PlusChar + " M.HOLD_GROUP_NAME AS HOLD_GROUP, \n");

編譯結果
M.HOLD_GROUP_ID || ' ' || M.HOLD_GROUP_NAME AS HOLD_GROUP

假設M.HOLD_GROUP_ID=001
M.HOLD_GROUP_NAME=JIMMY

SQL SELECT出來的結果就是
001 JIMMY, 欄位名稱為 HOLD_GROUP

SQL SELECT DISTINCT 用法

SELECT 指令讓我們能夠讀取表格中一個或數個欄位的所有資料。 這將把所有的資料都抓出，無論資料值有無重複。在資料處理中，我們會經常碰到需要找出表格內的不同 資料值的情況。換句話說，我們需要知道這個表格/欄位內有哪些不同的值，而每個值出現的次數並不重要。 這要如何達成呢？在 SQL 中， 這是很容易做到的。我們只要在 SELECT 後加上一個 DISTINCT 就可以了。DISTINCT 的 語法如下：
SELECT DISTINCT "欄位名"
FROM "表格名"
舉例來說，若要在以下的表格，Store_Information，找出所有不同的店名時，
Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT DISTINCT store_name FROM Store_Information
結果:

store_name

Los Angeles

San Diego

Boston

用SQL合併資料表

合併資料表
這邊是教你用SQL去寫, 當然如果你有Access的話也可以用設計檢視去設計合併資料表. 用這個方法合併資料表不會永久合併, 而是在SQL執行的時候建立一份新的資料表來進行兩份資料表的合併工作. 因此不用擔心啦! Access裡面的查詢 (Query) 就可以做到這個功能.
INNER JOIN 方法

Sql = "SELECT * FROM 資料表1 INNER JOIN 資料表2 ON 條件"

SELECT * 的 * 代表所有欄位, 也就是從資料表選取所有欄位.
INNER JOIN的意思是將資料表1與資料表2合併, 而 ON 之後的條件要成立.
這條件怎麼寫呢?
假如說資料表1有一個class欄位而資料表2也有一個class欄位, 那ON你可以寫資料表1.class = 資料表2.class
xxx.yyy , xxx代表的是資料表, yyy代表的是欄位名稱.

現在如果將這整段寫出來的話:

Sql = "SELECT * FROM 資料表1 INNER JOIN 資料表2 ON 資料表1.class = 資料表2.class"

意思也就是如果資料表1的class欄位與資料表2的class欄位數值相等就將他們合併, 如果資料表1的class欄位含有資料表2的class欄位沒有的數值, 就將其剔除, 反推亦同. INNER JOIN的意思就是當兩個資料表的某欄位含有相同的資料再合併, 如果沒有就剔除.

執行上面這段SQL以後就會產生一個同時含有資料表1與資料表2的資料的新資料表, 中間的連結欄位是用兩個資料表的class欄位.

這個技巧可以怎麼使用呢? 就拿文章系統來講, 如果你有一個文章列表, 和一個文章分類.
文章列表中紀錄每一個文章的資料和分類, 而文章分類紀錄著分類資訊, 你可以用這個方法來將兩個資料表暫時合併 (在SQL被執行時合併) 來同時存取兩個資料表的資料.
RIGHT JOIN 方法
剛剛的 INNER JOIN是很棒沒有錯, 但是如果今天有一個文章分類裡面沒有任何的文章, 那由於文章列表中沒有對應的分類資料, 該分類就會被剔除...這...不大對吧?! 因此還有RIGHT JOIN的方法.

Sql = "SELECT * FROM 資料表1 RIGHT JOIN 資料表2 ON 資料表1.class = 資料表2.class"

其實大致上和剛剛是一樣的, 只是RIGHT JOIN會以資料表2, 也就是運算子的右邊的資料表為重, RIGHT JOIN會將所有右邊資料表 (也就是資料表2) 的資料都加進來, 而只將左邊資料表 (資料表1) 符合的資料加進來.

這樣在做文章分類清單的時候, 就算一個文章列表裡面沒有任何文章, 那該分類仍然會顯示出來.
LEFT JOIN 方法
有RIGHT JOIN, 當然就有 LEFT JOIN...

Sql = "SELECT * FROM 資料表1 LEFT JOIN 資料表2 ON 資料表1.class = 資料表2.class"

其實就是一樣的意思啦, 之前的RIGHT JOIN是包含所有右邊資料表的內容, 那LEFT JOIN當然就是包含所有左邊資料表的內容囉!
只要記得:

INNER JOIN - 兩邊的資料表擁有相同的優先權, 要兩邊都有的資料才會被包含在新的資料表.

RIGHT JOIN - 右邊的資料表擁有優先權, 右邊所有的資料都會被包含, 而左邊只有符合的資料才會被包含.

LEFT JOIN - 左邊的資料表擁有優先權, 左邊所有的資料都會被包含, 而右邊只有符合的資料才會被包含.