※GROUP BY 範例(1)
from a in SM_ISET_PARAM_Ds
where a.SYS_ID=="TM"
group a by new {a.SYS_ID, a.PARAM_ID} into p
select p
※GROUP BY 範例(2)
: 使用Linq Extension
///取得目前Reservations裡面所有有訂房紀錄的房號
//Group by RoomId
var _rooms
= db.Reservations.GroupBy(x => x.RoomId).
Select(
group => new
{
RoomId = group.Key
}).OrderBy(x => x.RoomId);
※GROUP BY 範例(3) : 使用Linq Extension
///取得某房號某月份的訂房次數
///Select
RoomId, COUNT(1) as ResCount
///from
db.Reservations
///WHERE
SUBSTRING(CheckInOn,1,6)=months[i]
///Group by
RoomId
var
_roomInfo =
db.Reservations.
Where(x => x.RoomId == _room.RoomId).
Where(y => (
y.CheckInOn.Year == _year &&
y.CheckInOn.Month==_mon)).
GroupBy( x => x.RoomId).
Select(
group => new
{
RoomId=group.Key,
ResCount = group.Count()
});
PS. 使用關鍵字Key來取得Group By的欄位值
PS. 使用關鍵字Count()來做計數
※GROUP BY 範例(4) : 使用Linq Extension
///查詢每個月需要/不需要用膳的數目
///Group by CheckInOn月份, IsDinner
int _year =
DateTime.Now.Year;
var
_dinnerInfo =
db.Reservations.
Where(x => x.CheckInOn.Year == _year).
GroupBy(x => new { mon =
x.CheckInOn.Month, IsDinner = x.IsDinner }).
Select(
group => new
{
Month =
group.Key.mon,
IsDinner =
group.Key.IsDinner,
Count =
group.Count()
|
}).OrderBy(x => x.Month);
PS. 此範例Group by兩個欄位
|
PS.
如果Group by的欄位超過一個，請使用 Key.{欄位名稱} 來取得值
