本文以餐廳訂單系統(ItemMaster、OrderMaster、OrderDetail 三表)為例,從建表、插資料開始,循序漸進講解 15 類核心查詢技巧,包含 SELECT 基礎、彙總/標量/日期函數、TOP/DISTINCT、WHERE 進階、GROUP BY/HAVING、子查詢、多表 JOIN、UNION、CTE、VIEW、PIVOT、預存程序、自訂函數。
CREATE TABLE ItemMasters (
Item_Code VARCHAR(20) PRIMARY KEY,
Item_Name VARCHAR(100), Price INT, TAX1 INT, Discount INT,
Description VARCHAR(200), IN_DATE DATETIME, IN_USR_ID VARCHAR(20),
UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO ItemMasters VALUES
('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Item004','Potato Fry',15,0,0,'No Comments',GETDATE(),'SHANU',GETDATE(),'SHANU');
CREATE TABLE OrderMasters (
Order_No VARCHAR(20) PRIMARY KEY,
Table_ID VARCHAR(20), Description VARCHAR(200),
IN_DATE DATETIME, IN_USR_ID VARCHAR(20), UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO OrderMasters VALUES
('Ord_001','T1','',GETDATE(),'SHANU',GETDATE(),'SHANU'),
('Ord_002','T2','',GETDATE(),'MAK',GETDATE(),'MAK'),
('Ord_003','T3','',GETDATE(),'RAJ',GETDATE(),'RAJ');
CREATE TABLE OrderDetails (
Order_Detail_No VARCHAR(20) PRIMARY KEY,
Order_No VARCHAR(20) REFERENCES OrderMasters(Order_No),
Item_Code VARCHAR(20) REFERENCES ItemMasters(Item_Code),
Notes VARCHAR(200), QTY INT,
IN_DATE DATETIME, IN_USR_ID VARCHAR(20), UP_DATE DATETIME, UP_USR_ID VARCHAR(20)
);
INSERT INTO OrderDetails VALUES
('OR_Dt_001','Ord_001','Item001','Need very Cold',3,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_002','Ord_001','Item004','very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_003','Ord_001','Item003','Very Spicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_004','Ord_002','Item002','Need very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_005','Ord_002','Item003','very Hot',2,GETDATE(),'SHANU',GETDATE(),'SHANU'),
('OR_Dt_006','Ord_003','Item003','Very Spicy',4,GETDATE(),'SHANU',GETDATE(),'SHANU');
-- 全欄位 SELECT * FROM ItemMasters; -- 指定欄位與別名 SELECT Item_Code, Item_Name AS Item, Price, Description, IN_DATE FROM ItemMasters; -- 字串串接 SELECT 'My Name' AS Col1, 'Is' AS Col2, 'SYED SHANU' AS Col3;
-- 彙總
SELECT COUNT(*) TotalRows, AVG(Price) AvgPrice, MAX(Price) MaxPrice, MIN(Price) MinPrice, SUM(Price) TotalPrice FROM ItemMasters;
-- 標量
SELECT UPPER(Item_Name) UpperName, LOWER(Item_Name) LowerName,
SUBSTRING(Item_Name,2,3) SubStr, LEN(Item_Name) LenName,
ROUND(Price,0) RoundPrice FROM ItemMasters;
SELECT GETDATE() Now,
FORMAT(GETDATE(),'yyyy-MM-dd') DateOnly,
FORMAT(GETDATE(),'HH:mm:ss') TimeOnly,
CONVERT(VARCHAR(10),GETDATE(),110) Conv110,
DATEPART(yyyy,GETDATE()) Yr, DATEPART(mm,GETDATE()) Mo, DATEPART(dd,GETDATE()) Dy,
DATEADD(day,10,GETDATE()) Add10Day,
DATEDIFF(day,'2020-01-01',GETDATE()) DiffDay;
SELECT TOP 2 * FROM ItemMasters ORDER BY Item_Code DESC; SELECT DISTINCT Item_Name, Price FROM ItemMasters;
-- 精確比對
SELECT * FROM ItemMasters WHERE Item_Name = 'Coke';
-- 模糊比對
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%';
-- 多條件 AND/OR
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%' AND Price >= 40;
-- IN 列表
SELECT * FROM ItemMasters WHERE Item_Name IN ('Coffee','Chiken Burger') ORDER BY Item_Code DESC;
-- BETWEEN 日期區間
SELECT * FROM ItemMasters WHERE IN_DATE BETWEEN '2024-01-01' AND '2024-12-31';
-- 基本分組彙總 SELECT Item_Name, COUNT(*) Cnt, AVG(Price) AvgP FROM ItemMasters GROUP BY Item_Name; -- 多欄位分組 SELECT Order_No, Item_Code, SUM(QTY) TotalQty FROM OrderDetails WHERE QTY >= 2 GROUP BY Order_No, Item_Code ORDER BY Order_No DESC, Item_Code; -- HAVING 過濾分組結果 SELECT Order_No, SUM(QTY) TotalQty FROM OrderDetails GROUP BY Order_No HAVING SUM(QTY) > 4;
-- WHERE 子查詢 SELECT * FROM ItemMasters WHERE Item_Code IN (SELECT Item_Code FROM ItemMasters WHERE Price > 40); -- INSERT 子查詢 INSERT INTO ItemMasters (Item_Code,Item_Name,Price,TAX1,Discount,Description,IN_DATE,IN_USR_ID,UP_DATE,UP_USR_ID) SELECT 'Item006', Item_Name, Price+4, TAX1, Discount, Description, GETDATE(),'SHANU',GETDATE(),'SHANU' FROM ItemMasters WHERE Item_Code='Item002';
-- 內連接三表 SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice FROM OrderMasters M INNER JOIN OrderDetails D ON M.Order_No = D.Order_No INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code; -- LEFT/RIGHT/FULL OUTER JOIN 範例同上,僅替換關鍵字
SELECT Item_Code,Item_Name,Price FROM ItemMasters WHERE Price <= 44 UNION ALL SELECT Item_Code,Item_Name,Price FROM ItemMasters WHERE Price > 44;
DECLARE @sDate DATETIME = DATEADD(day,-5,GETDATE()), @eDate DATETIME = DATEADD(day,16,GETDATE());
WITH cte AS (
SELECT @sDate AS StartDate, 'W'+CAST(DATEPART(wk,@sDate) AS VARCHAR)+'('+CONVERT(VARCHAR(2),@sDate,106)+')' AS SDT
UNION ALL
SELECT DATEADD(day,1,StartDate), 'W'+CAST(DATEPART(wk,DATEADD(day,1,StartDate)) AS VARCHAR)+'('+CONVERT(VARCHAR(2),DATEADD(day,1,StartDate),106)+')'
FROM cte WHERE DATEADD(day,1,StartDate) <= @eDate
)
SELECT * FROM cte OPTION (MAXRECURSION 0);
CREATE VIEW myUnionView AS SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice FROM OrderMasters M INNER JOIN OrderDetails D ON M.Order_No = D.Order_No INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code WHERE I.Price <= 44 UNION ALL SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.QTY, I.Price, I.Price*D.QTY AS TotalPrice FROM OrderMasters M INNER JOIN OrderDetails D ON M.Order_No = D.Order_No INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code WHERE I.Price > 44; -- 使用 View SELECT * FROM myUnionView WHERE Price > 40;
SELECT * FROM ( SELECT Item_Name, Price AS TotAmount FROM ItemMasters ) src PIVOT (SUM(TotAmount) FOR Item_Name IN ([Chiken Burger],[Coffee],[Coke])) pvt;
CREATE PROC USP_SelectPivot AS
BEGIN
DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
SELECT @Cols = STUFF((SELECT ','+QUOTENAME(Item_Name) FROM ItemMasters GROUP BY Item_Name ORDER BY Item_Name FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'');
SET @SQL = N'SELECT '+@Cols+N' FROM (SELECT Item_Name,Price AS TotAmount FROM ItemMasters) x PIVOT (SUM(TotAmount) FOR Item_Name IN ('+@Cols+N')) p';
EXEC sp_executesql @SQL;
END;
EXEC USP_SelectPivot;
-- 取得 ItemMasters 筆數+1
CREATE FUNCTION ufnSelectItemMaster() RETURNS INT AS BEGIN
DECLARE @c INT; SELECT @c = COUNT(*)+1 FROM ItemMasters; RETURN @c; END;
SELECT dbo.ufnSelectItemMaster();
-- 取得指定年月的最後一天
CREATE FUNCTION ufn_LastDayOfMonth(@DATE NVARCHAR(10)) RETURNS NVARCHAR(10) AS BEGIN
RETURN CONVERT(NVARCHAR(10), DATEADD(day,-1, DATEADD(month,1, CAST(SUBSTRING(@DATE,1,7)+'-01' AS DATETIME))), 120);
END;
SELECT dbo.ufn_LastDayOfMonth('2024-09-01'); -- 2024-09-30
掌握這 15 類查詢模式,即可應付絕大多數日常開發與報表需求。建議實際在 SSMS 中跑一次範例,體會各子句的執行順序與結果差異。