一道关于SQL子查询的问题

mylove123456 2008-01-10 11:52:42

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是指定的供应商

...全文

67 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

pt1314917 2008-01-10

打赏
举报



这样？

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