创建对聚合函数sum进行排序的条件

Question

SELECT 
  [ProductErpIdentifier], 
  [PriceValue], 
  [ManufacturerProductIdentifier], 
  SUM([QuantityOrdered]) as  [QuantityOrdered]
FROM 
  [eCommerceDev].[dbo].[OrderItem] 
GROUP BY 
  [ProductErpIdentifier],[PriceValue],[ManufacturerProductIdentifier]
ORDER BY  
  [QuantityOrdered] desc

我应该如何写一个CreateCriteria生成SQL像上面的例子?

Answer 1

这应该能奏效。由于您没有发布orderitem实体的映射，所以我使用了字段名作为属性名。您需要将OrderItemEntity更改为您自己的映射类，并且需要更改以oi开头的名称。通过类中属性的名称(注意:这些字符串是区分大小写的!)

var yourResult = Session.CreateCriteria<OrderItemEntity>("oi")
                   .SetProjection(Projections.ProjectionList()
                      .Add(Projections.Alias(Projections.GroupProperty("oi.ProductErpIdentifier"), "ProductErpIdentifier"))
                      .Add(Projections.Alias(Projections.GroupProperty("oi.PriceValue"), "PriceValue"))
                      .Add(Projections.Alias(Projections.GroupProperty("oi.ManufacturerProductIdentifier"), "ManufacturerProductIdentifier"))
                      .Add(Projections.Alias(Projections.Sum("oi.QuantityOrdered"), "QuantityOrdered")))
                   .AddOrder(Order.Desc("QuantityOrdered"))
                   .List();