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 xinner join(    Select  businessunitid,inventoryitemid,lastmodifiedtimestamp,inventoryitemonhandid from InventoryItemBUOnHand     where inventoryitemid in (939) and businessunitid=829) as yon 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

如果我正确地理解你，你有这样的数据，你想要标记的记录。我不知道是否是桌子，所以我不依赖它。iventoryitemonhandidid

Table InventoryItemBUOnHand (mapped as SiteItemOnHand)

| businessunitid|inventoryitemid|lastmodifiedtimestamp|inventoryitemonhandid|

|            829|            939|  2019-01-01 00:00:00|                  100| <--

|            829|            940|  2019-01-02 00:00:00|                  101| 

|            829|            940|  2019-01-03 00:00:00|                  102| <-- 

|            829|            950|  2019-01-04 00:00:00|                  103| 

|            829|            950|  2019-01-10 00:00:00|                  104| <--

|            829|            950|  2019-01-06 00:00:00|                  105|

如果是，那么我会使用子查询，如以下问题：SO：NHibernate - 从聚合中选择完整记录

根据您的要求进行修改（未测试），如下所示：

int siteID = 829;

List<int> itemIdList = new List<int>() { 939, 940, 950 };

SiteItemOnHand siAlias = null;

var subQuery = QueryOver.Of<SiteItemOnHand>()

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

    .And(x => x.ItemID == siAlias.ItemID)

    .Select(Projections.Max<SiteItemOnHand>(y => y.lastmodifiedtimestamp));

var siteItems = Session.QueryOver<SiteItemOnHand>(() => siAlias)

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

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

    .WithSubquery.Where(x => siAlias.lastmodifiedtimestamp == subQuery.As<DateTime>())

    .List();

此处的目标是使用子查询筛选每个组的正确最大日期，然后使用它来筛选实际记录。

生成的 SQL 将如下所示：

SELECT <field_list> FROM InventoryItemBUOnHand 

WHERE Businessunitid = 829

AND inventoryitemid in (939, 940, 950)

AND this_.lastmodifiedtimestamp = 

   (SELECT max(this_0_.lastmodifiedtimestamp) as y0_ 

       FROM InventoryItemBUOnHand this_0_ 

       WHERE this_0_.Businessunitid = this_.Businessunitid and this_0_.ItemID = this_.ItemID) 

谨慎：当两条记录具有相同的值时，比较可能会导致不需要的结果。如果发生这种情况，您可以添加并仅选择第一条记录。如果是唯一索引，则变得更加简单。lastmodifiedtimestampbusinessunitidinventoryitemidOrderByinventoryitemonhandid