SQL Server :子查询中的最大值

Question

请帮我检索最大值。我创建了几个简单的表。第一个是用户，第二个是书籍。因此，我需要使用子查询来检索图书的名称，其中用户获取的日期是最新的。

以下是几张桌子：

CREATE TABLE book_base 
(
    book_id int,
    user_id int,
    title VARCHAR(20),
    date DATE,
); 

CREATE TABLE users 
(
    userid int,
    name VARCHAR(20),
); 

INSERT INTO book_base (book_id, user_id, title, date)
VALUES ('221', 1, 'Just book', '2021-2-2'),
       ('222', 1, 'The book', '2020-4-8'),
       ('223', 1, 'My book', '2019-8-8'),
       ('228', 2, 'Nice book', '2021-1-2'),
       ('225', 2, 'Coole book', '2020-4-8'),
       ('226', 2, 'Super book', '2019-9-8');

INSERT INTO users (userid, name) 
VALUES ('1', 'John Smith'),
       ('2', 'Mary Adams');

我也试过这样做

SELECT
    userid AS [UID], 
    name AS [UserName], 
    bb.title, bb.date 
FROM
    users u
JOIN 
    (SELECT user_id title, MAX(date) 
     FROM book_base) bb ON u.userid = bb.user_id

结果应该是行，日期是最大日期。

​

​

Answer 1

试试这个，它是基于您所获得的数据：

SELECT *
FROM users u
JOIN (
  select user_id,MAX(date) as DATE 
  from book_base GROUP BY user_id
) bb ON u.userid = bb.user_id
JOIN book_base b ON u.userid = b.user_id
AND bb.date = b.date

Answer 2

您可以将Cross Apply与使用Top 1 With Ties的子查询一起使用。

Select u.userid As [UID], u.name As [UserName], bb.title, bb.date 
From users u Cross Apply 
    (Select Top 1 With Ties user_id, title, date 
     From book_base
     Where user_id=u.userid 
     Order by date Desc) As bb

Answer 3

使用关联子查询按最大日期进行筛选

select b1.*, u.name
from book_base b1
join users u on u.userid = b1.user_id
where date = (select max(b2.date) 
             from book_base b2 
             where b2.user_id = b1.user_id)