34,590
社区成员
发帖
与我相关
我的任务
分享
select
拍卖物.Id as ItemId,
拍卖物.Item_desc as 物品名称及描述,
竞投者.Price as 最高出价,
竞投者.Name as 最高出价者,
拍卖物.Base_price as 底价,
持有者.Name as 货主
from
Add_Item as 拍卖物
inner join
[User] as 持有者
on 拍卖物.[User_ID]=持有者.ID
left join
(
select
a.Name,
b.ItemID,
b.Price,
b.UserID
from
[User] as a
inner join
Item_price as b
on a.ID=b.UserID
where b.Price=(select max(Price) from Item_price where ItemID=b.ItemID)
) as 竞投者
on 拍卖物.ID=竞投者.ItemID
select
拍卖物.Id as ItemId,
拍卖物.Item_desc as 物品名称及描述,
--竞投者.Price as 最高出价,
--竞投者.Name as 最高出价者,
拍卖物.Base_price as 底价,
持有者.Name as 货主
from
Add_Item as 拍卖物
inner join
[User] as 持有者
on 拍卖物.[User_ID]=持有者.ID
select
a.Name,
b.ItemID,
b.Price,
b.UserID
from
[User] as a
inner join
Item_price as b
on a.ID=b.UserID
where b.Price=(select max(Price) from Item_price where ItemID=b.ItemID) --> a.ItemID->b.ItemID
select
a.Name,
b.ItemID,
b.Price,
b.UserID
from
[User] as a
inner join
Item_price as b
on a.ID=b.UserID
where b.Price=(select max(Price) from Item_price where ItemID=a.ItemID)
where b.Price=(select max(Price) from Item_price where ItemID=a.ItemID)
-->
where b.Price=(select max(Price) from Item_price where ItemID=b.ItemID)
select
拍卖物.Id as ItemId,
拍卖物.Item_desc as 物品名称及描述,
竞投者.Price as 最高出价,
竞投者.Name as 最高出价者,
拍卖物.Base_price as 底价,
持有者.Name as 货主
from
Add_Item as 拍卖物
inner join
[User] as 持有者
on 拍卖物.[User_ID]=持有者.ID
left join
(
select
a.Name,
b.ItemID,
b.Price,
b.UserID
from
[User] as a
inner join
Item_price as b
on a.ID=b.UserID
where b.Price=(select max(Price) from Item_price where ItemID=a.ItemID)
--这里不考虑最高价重复问题,应该在业务逻辑控制竞价只能高于上一轮竞价
) as 竞投者
on 拍卖物.ID=竞投者.ItemID
where
拍卖物.[User_ID]<>竞投者.UserID -->自己叫价不算,这也应该在业务逻辑控制。