Nhibernate 3.0 IQueryOver分组并获取每个组中满足条件的完整记录

Question

我的要求是获取SiteItemOnHand记录，按Businessunitid分组，inventoryitemid，并在每个具有max（lastmodifiedtimestamp）的组中获取整个SiteItemOnHand记录。

我正在尝试为下面的sql查询编写nhibernate等效查询

Select x.* from 

(

Select  businessunitid,inventoryitemid,max(lastmodifiedtimestamp) as maxvalue from InventoryItemBUOnHand  

group by businessunitid,inventoryitemid HAVING inventoryitemid in (939) and businessunitid=829

) as x

inner join

(

    Select  businessunitid,inventoryitemid,lastmodifiedtimestamp,inventoryitemonhandid from InventoryItemBUOnHand 

    where inventoryitemid in (939) and businessunitid=829

) as y

on x.businessunitid=y.businessunitid and  x.inventoryitemid =y.inventoryitemid and x.maxvalue=y.lastmodifiedtimestamp

我有很多限制。我只允许使用Nhibernate 3.0版本，并且严格禁止编写sql查询（标准）。我只能写下面查询的一半。任何帮助将不胜感激。

var query= _session.QueryOver<SiteItemOnHand>()

      .Where(x => x.Businessunitid == siteID)

      .Where(x => x.End == endDate)

      .Where(x => x.CountID != filterCount.ID)

      .WhereRestrictionOn(x => x.ItemID).IsIn(itemIdList.ToArray())

      .SelectList(list => list

      .SelectMax(x => x.LastModifiedTimestamp)

      .SelectGroup(x => x.Businessunitid)

      .SelectGroup(x => x.ItemId));

上面的查询正在生成下面的SQL查询，它只返回三列，但我需要整个记录，因为在检索记录后我需要更新。我需要获取满足上述查询的所有SiteItemOnHand记录。

SELECT max(this_.lastmodifiedtimestamp) as y0_, this_.businessunitid as y1_, this_.inventoryitemid as y2_ 

FROM InventoryItemBUOnHand this_ 

WHERE this_.businessunitid = 567 and this_.enddate = '1/31/2019 1:18:17 AM' and not (this_.itemcountid = 958) 

and this_.inventoryitemid in (744,755)

GROUP BY this_.businessunitid, this_.inventoryitemid

任何帮助将不胜感激。如果您需要更多信息，请告诉我。

Answer 1

如果我正确地理解你，你有这样的数据，你想要标记的记录。我不知道是否是桌子，所以我不依赖它。iventoryitemonhandididTable InventoryItemBUOnHand (mapped as SiteItemOnHand)| businessunitid|inventoryitemid|lastmodifiedtimestamp|inventoryitemonhandid||&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 939|&nbsp; 2019-01-01 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100| <--|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 940|&nbsp; 2019-01-02 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101|&nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 940|&nbsp; 2019-01-03 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 102| <--&nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 950|&nbsp; 2019-01-04 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 103|&nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 950|&nbsp; 2019-01-10 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 104| <--|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 829|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 950|&nbsp; 2019-01-06 00:00:00|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 105|如果是，那么我会使用子查询，如以下问题：SO：NHibernate - 从聚合中选择完整记录根据您的要求进行修改（未测试），如下所示：int siteID = 829;List<int> itemIdList = new List<int>() { 939, 940, 950 };SiteItemOnHand siAlias = null;var subQuery = QueryOver.Of<SiteItemOnHand>()&nbsp; &nbsp; .Where(x => x.Businessunitid == siAlias.Businessunitid)&nbsp; &nbsp; .And(x => x.ItemID == siAlias.ItemID)&nbsp; &nbsp; .Select(Projections.Max<SiteItemOnHand>(y => y.lastmodifiedtimestamp));var siteItems = Session.QueryOver<SiteItemOnHand>(() => siAlias)&nbsp; &nbsp; .Where(x => x.Businessunitid == siteID)&nbsp; &nbsp; .AndRestrictionOn(x => x.ItemID).IsIn(itemIdList.ToArray())&nbsp; &nbsp; .WithSubquery.Where(x => siAlias.lastmodifiedtimestamp == subQuery.As<DateTime>())&nbsp; &nbsp; .List();此处的目标是使用子查询筛选每个组的正确最大日期，然后使用它来筛选实际记录。生成的 SQL 将如下所示：SELECT <field_list> FROM InventoryItemBUOnHand&nbsp;WHERE Businessunitid = 829AND inventoryitemid in (939, 940, 950)AND this_.lastmodifiedtimestamp =&nbsp;&nbsp; &nbsp;(SELECT max(this_0_.lastmodifiedtimestamp) as y0_&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FROM InventoryItemBUOnHand this_0_&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;WHERE this_0_.Businessunitid = this_.Businessunitid and this_0_.ItemID = this_.ItemID)&nbsp;谨慎：当两条记录具有相同的值时，比较可能会导致不需要的结果。如果发生这种情况，您可以添加并仅选择第一条记录。如果是唯一索引，则变得更加简单。lastmodifiedtimestampbusinessunitidinventoryitemidOrderByinventoryitemonhandid