[ASP.NET C#] 015 | SQL 筆記－門外漢的筆記

新增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