select a.StockName,a.Modal,a.stockunit,a.instockid,b.MinPrice,
(select VenName from ZDB_Provider Where VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=b.MinPrice))) as 'MinProvider',
c.MaxPrice,
(select VenName from ZDB_Provider Where VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=c.MaxPrice))) as 'MaxProvider'
from zdb_instock a
left Join (select StockID,Min(Price) as MinPrice from STK_OrderDtl group by StockID) b on a.StockID=b.StockID
left Join (select StockID,Max(Price) as MaxPrice from STK_OrderDtl group by StockID) c on a.StockID=c.StockID
使用这个sql语句已经得到了一个结果集,现在想在这个结果集中继续得到指定供应商的信息,也就是MinProvider和MaxProvider是指定的供应商
(select VenName from ZDB_Provider Where VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=b.MinPrice))) as 'MinProvider',
c.MaxPrice,
(select VenName from ZDB_Provider Where VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=c.MaxPrice))) as 'MaxProvider'
from zdb_instock a
left Join (select StockID,Min(Price) as MinPrice from STK_OrderDtl group by StockID) b on a.StockID=b.StockID
left Join (select StockID,Max(Price) as MaxPrice from STK_OrderDtl group by StockID) c on a.StockID=c.StockID
使用这个sql语句已经得到了一个结果集,现在想在这个结果集中继续得到指定供应商的信息,也就是MinProvider和MaxProvider是指定的供应商
这样?
select a.StockName,a.Modal,a.stockunit,a.instockid,b.MinPrice,
(select VenName from ZDB_Provider Where
VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=b.MinPrice))
and VenName='指定供应商的值') as 'MinProvider',
c.MaxPrice,
(select VenName from ZDB_Provider Where VenCode=(SELECT VenCode FROM STK_Order WHERE OrderID=(SELECT Top 1 OrderID from STK_OrderDtl where StockID=a.StockID and Price=c.MaxPrice))
and VenName='指定供应商的值') as 'MaxProvider'
from zdb_instock a
left Join (select StockID,Min(Price) as MinPrice from STK_OrderDtl group by StockID) b on a.StockID=b.StockID
left Join (select StockID,Max(Price) as MaxPrice from STK_OrderDtl group by StockID) c on a.StockID=c.StockID