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
同样少了主键ID 这样呢: 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
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
同样少了主键ID 这样呢:
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