导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

一道关于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是指定的供应商
...全文
43 点赞 收藏 1
写回复
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

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告