create table bidRecords
(
id int identity(1,1) primary key not null,
auctionId int not null,
userId int,
codeName varchar(20),
offer money
)
1 10001 10000 name1 200
2 10001 10001 name2 300
3 10002 10000 name3 500
4 10003 10000 name4 800
5 10002 10001 name5 900
查询条件 max(offer)as offer group by auctionId count(auctionId)as cs
结果：
2 10001 10001 name2 300 2
4 10003 10000 name4 800 1
5 10002 10001 name5 900 2
------解决方案--------------------
SELECT S.AuctionId, T.userid, T.codename, S.cs
FROM (SELECT AuctionId, COUNT(*) AS cs
FROM BidRecords GROUP BY AuctionId ) S
CROSS APPLY (SELECT TOP 1 userid, codename FROM BidRecords WHERE AuctionId = S.AuctionId) T
------解决方案--------------------
SELECT S.AuctionId, T.userid, T.codename, T.offer, S.cs
FROM (SELECT AuctionId, COUNT(*) AS cs
FROM BidRecords GROUP BY AuctionId ) S
CROSS APPLY (SELECT TOP 1 userid, codename, offer FROM BidRecords WHERE AuctionId = S.AuctionId ORDER BY offer DESC) T
------解决方案--------------------
这样呢：
SELECT T.id,
S.AuctionId, T.userid, T.codename, T.offer, S.cs
FROM (SELECT AuctionId, COUNT(*) AS cs
FROM BidRecords GROUP BY AuctionId ) S
CROSS APPLY (SELECT TOP 1 id,userid, codename, offer FROM BidRecords WHERE AuctionId = S.AuctionId ORDER BY offer DESC) T