C# 建立複雜靈活 SQL 查詢實戰（三）

彙總查詢與 GROUP BY / HAVING

var q = new SelectQueryBuilder();
q.SelectColumns("count(*) AS Count", "ShipCity");
q.SelectFromTable("Orders");
q.GroupBy("ShipCity");
q.AddHaving("ShipCity", Comparison.NotEquals, "Amsterdam");
q.AddOrderBy("count(*)", Sorting.Descending);

產出：

SELECT count(*) AS Count, ShipCity
FROM Orders
GROUP BY ShipCity
HAVING (ShipCity <> 'Amsterdam')
ORDER BY count(*) DESC

多層 WHERE 邏輯（Criteria / Or... 欄）

AddWhere(..., level) 的 level 參數對應查詢設計器的「Criteria / Or...」欄位：

• Level 1：AND 串接
• Level 2+：OR 串接

// Level 1
q.AddWhere("CustomerID", Comparison.Equals, "VINET", 1);
q.AddWhere("OrderDate", Comparison.LessThan, new DateTime(2005,1,1), 1);
// Level 2
q.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 2);
q.AddWhere("OrderDate", Comparison.LessThan, new DateTime(2004,6,30), 2);
// Level 3
q.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 3);
q.AddWhere("OrderDate", Comparison.GreaterThan, new DateTime(2006,1,1), 3);

所有層級最終以 OR 合併，完全對應 SQL Server 查詢設計器行為。