交易日期最后4个供应商的产品 [英] Product with last 4 vendors on transaction date
问题描述
我有这个sql,必须转换为NHibernate QueryOver
Hi I have this sql and have to translate into NHibernate QueryOver
SELECT S.Number, S.Description, S.BrandDescription, subquery.vendornumber, subquery.vendorname
FROM Stocks.Stock S left join
(select vendorNumber, VendorName, POLID, LastTransactionDate from
(
SELECT top 4 v.Number vendorNumber, v.Name VendorName, PLL.Id POLID, max(por.TransactionDate) as LastTransactionDate,
ROW_NUMBER() OVER(PARTITION BY v.Number ORDER BY max(por.TransactionDate) DESC) AS rk
FROM Purchasing.PurchaseOrderLineItem PLL
inner join Purchasing.PurchaseOrder po on PLL.PurchaseOrderId = po.Id
inner join Purchasing.PurchaseOrderVendor POV on po.Id = POV.PurchaseOrderId
inner join Purchasing.Vendor V on pov.VendorId = v.Id
left outer join Purchasing.PurchaseOrderReceipt POR on PLL.Id = por.PurchaseOrderLineItemId
group by v.Number, v.Name,PLL.Id
order by LastTransactionDate desc
) subquery
where subquery.rk = 1) B on PL.Id = b.POLID
或者只是为了解释它而已,请看其简化版本
Or just to explain it simply see its simplified version
Select * from master m
outer apply (select top 4 * From Details d where m.Id = d.Id order by someColumns desc)o
我认为我们不能在nhibernate中将子查询用作派生表.如果您有建议,请分享.
I think we cannot use subquery as derived table in nhibernate. If you have suggestions, please share.
谢谢
推荐答案
我一直在研究这个问题,发现如果想完全使用QueryOver可能会非常困难.我想展示一下我是如何做到的.
I was keep working on this and found that it could be very difficult if want to do totally with QueryOver. I want to show how I did this.
首先,我将所有具有StockID的供应商加入到StockQuery中.
First I took all the vendors with StockID to join with StockQuery later.
var stockVendors =
Session.QueryOver<Vendor>(() => V)
.Left.JoinQueryOver(p => V.Stock, () => sstk)
.Where(sstk.Number !=null)
.OrderBy(Projections.Max(() => V.TransactionDate)).Desc()
.ThenBy(() => sstk.Number).Asc()
.ThenBy(() => sv.Number).Asc()
.SelectList(
lst =>
lst.SelectGroup(() => V.Name).WithAlias(() => svhModal.VendorName)
.SelectGroup(() => V.Number).WithAlias(() => svhModal.VendorNumber)
.SelectGroup(() => sstk.Number).WithAlias(() => svhModal.StockNumber)
.Select(Projections.Max(() => V.TransactionDate)).WithAlias(() => svhModal.LastTransactionDate)
) .TransformUsing(Transformers.AliasToBean()).List();
) .TransformUsing(Transformers.AliasToBean()).List();
然后选择仅库存"
var stockDetail = Session.QueryOver<Stock>(() => stk)
.Where(soneCriteria)
.SelectList(list => list
.Select(() => stk.Id).WithAlias(() => sdrModal.Id)
.Select(() => stk.Number).WithAlias(() => sdrModal.Number)
.TransformUsing(Transformers.AliasToBean<StockDetailReportModal>())
.List<StockDetailReportModal>();
IList<StockVendor> vlst2 = null;
IList<StockDetail> newStock = new List<StockDetail>();
此处开始两个循环,以用供应商列表中的每种股票及其前5名供应商填充List对象.来自查询的Stockdetail结果循环和来自供应商结果的内部循环过滤到外部循环stockid,仅循环获得前5个供应商,完成后仅将结果返回给报表.工作正常.
Here starts two loops to fill List object with each stock and its 5 top vendors from vendors list. Loop from Stockdetail result from query and inside loop from vendor result filtered to outer loop stockid, get first 5 vendors only in loop, when done just return the result to report. Its working fine.
foreach (StockDetail ostk in stockDetail)
{
stkid = ostk.Number;
vlst2 = (from v in stockVendors where v.StockNumber == stkid orderby v.LastTransactionDate descending select v).ToList<StockVendor>();
vndrcnt = 0;
stok = new StockDetail
{
Id = ostk.Id,
Number = ostk.Number,
//// other fields too here
};
if (vlst2.Count() == 0)
{
newStock.Add(stok);
}
foreach (StockVendor vn in vlst2)
{
if (vndrcnt == 0)
{
stok.VendorName = vn.VendorName;
stok.VendorNumber = vn.VendorNumber;
// other fields here...
newStock.Add(stok);
}
else
{
newStock.Add(new StockDetail
{
Id = ostk.Id,
Number = ostk.Number,
VendorName = vn.VendorName,
VendorNumber = vn.VendorNumber,
// adding vendor information in stock record.
});
}
vndrcnt++;
if (vndrcnt >= 4)
break;
}
这解决了我的问题,并在调查了许多天后实现了这一目标.您可能会找到更好的方法,所以请分享.
This solved my problem and achieved this after investigating many days. You may find better approach, so please share.
这篇关于交易日期最后4个供应商的产品的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!