門外漢的筆記

 新增Table 

CREATE TABLE [MEMBERS]
(
ID nvarchar(10)
,NAME  nvarchar(10)
);

 新增多筆資料 

INSERT INTO [MEMBERS]
VALUES
('A01','Ben'),
('A02',Tom');

 計算相同名稱的個別筆數 

SELECT CUSTOMER as 客戶 ,COUNT(CUSTOMER) as 數量 FROM tbORDER GROUP BY CUSTOMER

SELECT ORDER_MONTH AS 訂單時間 ,CUSTOMER as 客戶, COUNT(CUSTOMER) as 數量
FROM tbORDER
GROUP BY ORDER_MONTH, CUSTOMER
ORDER BY ORDER_MONTH

 資料複製 

只匯入資料結構至新資料表
1.select * into newTable from oldTable where 1 = 0
匯入完整資料表至新資料表
2.select * into newTable from oldTable
匯入指定欄位至新資料表
3.select col1,col2 into newTable table from oldTable

 將指定欄位的資料匯入新資料表中的指定欄位 
4.insert into newTable(col1,col2)  select (col1,col2) from oldTable

 日期轉換 

上午2019/01/01 上午11:00轉換成2019/01/01 AM 11:00 

SET tableDATE=convert(varchar(19),convert(datetime, replace(replace(tbTemp.DATE,N'上午 ',''),N'下午 ','')+case when charindex(N'上午',tbTemp.DATE)>0 then 'AM' when charindex(N'下午',tbTemp.DATE)>0 then 'PM' end),120)

 先COUNT再SUM 透過兩個GROUP BY 

SELECT A.DPPM_MONTH,A.CUSTOMER,A.MODEL,A.DPPM,COUNT(B.MODEL) FAIL_COUNT
FROM tbDPPM A
LEFT JOIN tbRMA B
ON A.CUSTOMER =B.CUSTOMER
GROUP BY A.DPPM_MONTH,A.CUSTOMER,A.MODEL,A.DPPM,B.MODEL
ORDER BY A.DPPM_MONTH,A.CUSTOMER,A.MODEL

 SUM計算加總 

SELECT DPPM_MONTH,CUSTOMER, SUM(INPUT) INPUT 藍色為任意新的名稱
FROM tbDPPM  
GROUP BY DPPM_MONTH,CUSTOMER