GROUPPARTITION （Entity SQL）

傳回自變數值的集合，這些自變數值是從匯總相關的目前群組分割區投影而來。 匯總 GroupPartition 是以群組為基礎的匯總，而且沒有以集合為基礎的窗體。

語法

GROUPPARTITION( [ALL|DISTINCT] expression )

論點

expression 任何 Entity SQL 運算式。

備註

下列查詢會產生產品清單，以及每個產品的訂單明細數量集合：

SELECT p, GroupPartition(ol.Quantity) FROM LOB.OrderLines AS ol
  GROUP BY ol.Product AS p

下列兩個查詢在語意上相等：

SELECT p, Sum(GroupPartition(ol.Quantity)) FROM LOB.OrderLines AS ol
  GROUP BY ol.Product AS p
SELECT p, Sum(ol.Quantity) FROM LOB.OrderLines AS ol
  group by ol.Product as p

GROUPPARTITION運算子可以與使用者定義的聚合函數搭配使用。

GROUPPARTITION 是特殊的匯總運算符，可保存群組輸入集的參考。 此參考可用於 GROUP BY 在範圍內之查詢中的任何位置。 例如：

SELECT p, GroupPartition(ol.Quantity) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p

使用一般 GROUP BY時，群組的結果會隱藏。 您只能在聚合函數中使用結果。 若要查看群組的結果，您必須使用子查詢，將群組的結果和輸入集的結果相互關聯。 下列兩個查詢的用法相同：

SELECT p, (SELECT q FROM GroupPartition(ol.Quantity) AS q) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p
SELECT p, (SELECT ol.Quantity AS q FROM LOB.OrderLines AS ol2 WHERE ol2.Product = p) FROM LOB.OrderLines AS ol GROUP BY ol.Product AS p

如範例所示，GROUPPARTITION 匯總運算符可讓您更輕鬆地在群組之後取得輸入集的參考。

當您使用 expression 參數時，GROUPPARTITION 運算子可以在運算元輸入中指定任何 Entity SQL 運算式。

例如，群組分割區的所有下列輸入表達式都是有效的：

SELECT groupkey, GroupPartition(b) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(1) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(a + b) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition({a + b}) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition({42}) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey
SELECT groupkey, GroupPartition(b > a) FROM {1,2,3} AS a INNER JOIN {4,5,6} AS b ON true GROUP BY a AS groupkey

範例

下列範例示範如何搭配 GROUP BY 子句使用 GROUPPARTITION 子句。 GROUP BY 子句會依其 Contact群組SalesOrderHeader實體。 GROUPPARTITION 子句接著會為每個群組投影 TotalDue 屬性，進而產生十進位數的集合。

USING Microsoft.Samples.Entity
Function MyAvg(dues Collection(Decimal)) AS
(
    Avg(SELECT value due FROM dues AS due WHERE due > @price)
)
SELECT TOP(10) contactID, MyAvg(GroupPartition(order.TotalDue)) 
FROM AdventureWorksEntities.SalesOrderHeaders AS order 
GROUP BY order.Contact.ContactID AS contactID;