代码之家 › 专栏 › 技术社区 › Jon Smock

对同一表使用多个子查询进行选择

subquery tsql sql-server sql

Jon Smock · 技术社区 · 14 年前

我一次又一次地使用相同的SQL模式,我知道必须有更好的方法,但是我很难把它拼凑在一起。这是模式的一个简单版本,我将收回学生的信息和他们签出的最后一本书(如果有的话):

SELECT TStudents.*,
       BookName = (SELECT TOP 1 BookName 
                     FROM TBookCheckouts 
                    WHERE StudentID = TStudents.ID 
                 ORDER BY DateCheckedOut DESC),
       BookAuthor = (SELECT TOP 1 BookAuthor 
                       FROM TBookCheckouts 
                      WHERE StudentID = TStudents.ID 
                   ORDER BY DateCheckedOut DESC),
       BookCheckout = (SELECT TOP 1 DateCheckedOut 
                         FROM TBookCheckouts 
                         WHERE StudentID = TStudents.ID 
                     ORDER BY DateCheckedOut DESC)
   FROM TStudents

(为了这个例子,请忽略这样一个事实:tbookcheckout可能被分为tcheckout和tbooks)

我想说明的是:对于同一个表中的列,我倾向于有很多子查询。我还需要按日期对这些子查询表进行排序,以获得最新的记录,所以这并不像执行左联接那样简单(至少对我来说)。但是,请注意,除了返回哪个字段之外,我基本上做了3次相同的子查询。SQL Server可能足够聪明,可以对其进行优化,但我不这么认为(我绝对需要更好地阅读执行计划…)。

虽然以这种方式构建它可能会有一些好处(有时,如果我有大量的子查询和子表,这最终会变得更具可读性),但它看起来并不是特别有效。

我已经研究过从派生表执行左联接,可能合并了一个row_number()和partition by,但我似乎无法将它们全部拼凑在一起。

8 回复 | 直到 6 年前

Thomas 14 年前

如果您使用的是SQL Server 2005及更高版本,则可以使用排名函数,如下所示:

With LastCheckout As
    (
    Select StudentId, BookName, BookAuthor, DateCheckedOut 
        , Row_Number() Over ( Partition By StudentId Order By DateCheckedOut Desc) As CheckoutRank
    From TBookCheckouts
    )
Select ..., LastCheckout.BookName, LastCheckout.BookAuthor, LastCheckout.DateCheckedOut
From TStudents
    Left Join LastCheckout 
        On LastCheckout.StudentId = TStudents.StudentId
                And LastCheckout.CheckoutRank = 1

A-K 14 年前

在2005年及更高版本上,外部应用程序是您的朋友:

SELECT TStudents.*,
       t.BookName ,
       t.BookAuthor ,
       t.BookCheckout
   FROM TStudents
  OUTER APPLY(SELECT TOP 1 s.* 
                     FROM TBookCheckouts AS s
                    WHERE s.StudentID = TStudents.ID 
                 ORDER BY s.DateCheckedOut DESC) AS t

OMG Ponies 14 年前

用途:

   SELECT s.*,
          x.bookname,
          x.bookauthor,
          x.datecheckedout
     FROM TSTUDENTS s
LEFT JOIN (SELECT bc.studentid,
                  bc.bookname,
                  bc.bookauthor,
                  bc.datecheckedout,
                  ROW_NUMBER() OVER(PARTITION BY bc.studentid
                                        ORDER BY bc.datecheckedout DESC) AS rank
             FROM TSBOOKCHECKOUTS bc) x ON x.studentid = s.id
                                       AND x.rank = 1

如果学生没有结帐,那么 bookname , bookauthor 和 datecheckedout 将为空。

p.campbell 14 年前

如果您想使用一个公共表表达式,可以使用下面的查询。在这种情况下,它不会为您带来任何好处,但对于未来:

;with LatestBookOut as 
(
    SELECT  C.StudentID, BookID, Title, Author, DateCheckedOut AS BookCheckout 
    FROM    CheckedOut AS C
    INNER JOIN ( SELECT StudentID, 
                        MAX(DateCheckedOut) AS DD 
                FROM Checkedout 
                GROUP BY StudentID) StuMAX                 
    ON StuMAX.StudentID = C.StudentID 
    AND StuMAX.DD = C.DateCheckedOut  
)

SELECT    B.BookCheckout,
        BookId, 
        Title,    
        Author, 
        S.*

FROM    LatestBookOut AS B
INNER JOIN Student  AS S ON S.ID = B.StudentID

nang 14 年前

create table BookCheckout(StudentID int, CheckoutDate date, BookName varchar(10))

insert into BookCheckout values (1, '1.1.2010', 'a');
insert into BookCheckout values (1, '2.1.2010', 'b');
insert into BookCheckout values (1, '3.1.2010', 'c');
insert into BookCheckout values (2, '1.1.2010', 'd');
insert into BookCheckout values (2, '2.1.2010', 'e');

select *
from BookCheckout bc1
where CheckoutDate = (
    Select MAX(CheckoutDate) 
    from BookCheckout bc2
    where bc2.StudentID= bc1.StudentID)

StudentID    CheckoutDate    BookName
2    2010-01-02    e
1    2010-01-03    c

只需将join添加到tstudent,即可完成。还有一个问题:如果一个学生有两个或两个以上的退房日期相同,则每个学生将获得多个退房。

  select s.*, LastBookCheckout.*
  from TStudent s, 
    (select *
    from BookCheckout bc1
    where CheckoutDate = (
        Select MAX(CheckoutDate) 
        from BookCheckout bc2
        where bc2.StudentID= bc1.StudentID)) LastBookCheckout
  where s.ID = LastBookCheckout.StudentID

为避免重复:

select * 
from (
  select *, RANK() over (partition by StudentID order by CheckoutDate desc,BookName) rnk
    from BookCheckout bc1) x
where rnk=1

我用“书名”作为第二个订购标准。=>请改用主键使其成为真正的唯一条件。

littlegreen 14 年前

omgponies的答案是一个很好的答案。为了可读性,我将使用通用表表达式编写它:

WITH CheckoutsPerStudentRankedByDate AS (
    SELECT bookname, bookauthor, datecheckedout, studentid,
        ROW_NUMBER(PARTITION BY studentid ORDER BY datecheckedout DESC) AS rank
    FROM TSBOOKCHECKOUTS
)
SELECT 
    s.*, c.bookname, c.bookauthor, c.datecheckedout
FROM TSTUDENTS AS s
LEFT JOIN CheckoutsPerStudentRankedByDate AS c
    ON s.studentid = c.studentid
    AND c.rank = 1

这个 c.rank = 1 可替换为 c.rank IN(1, 2) 对于最后2次退房, BETWEEN 1 AND 3 最后3个,等等…

Zephyr was a Friend of Mine 14 年前

尝试

    ;WITH LatestCheckouts
    AS
    (
        SELECT  DISTINCT
                A.StudentID
            ,   A.BookName   
            ,   A.BookAuthor
            ,   A.DateCheckedOut
        FROM    TBookCheckouts A
            INNER JOIN
        (   
            SELECT  StudentID
            ,   DateCheckedOut =  MAX(DateCheckedOut)
             FROM TBookCheckouts
            GROUP  BY
                StudentID
        ) B

        ON A.StudentID = B.StudentID
        AND A.DateCheckedOut =  B.DateCheckedOut
    )       
    SELECT students.*
        ,  BookName     = checkouts.BookName
        ,  BookAuthor   = checkouts.BookAuthor
        ,  BookCheckout = checkouts.DateCheckedOut

    FROM    TStudents students
        LEFT JOIN
         LatestCheckouts checkouts
    ON  students.ID = checkouts.StudentID

Dennis Xavier 6 年前

希望这就是你要找的,一个简单的方法,我知道这些案件。

SELECT (SELECT TOP 1 BookName 
                 FROM TBookCheckouts 
                WHERE StudentID = TStudents.ID 
             ORDER BY DateCheckedOut DESC)[BOOK_NAME],
   (SELECT TOP 1 BookAuthor 
                   FROM TBookCheckouts 
                  WHERE StudentID = TStudents.ID 
               ORDER BY DateCheckedOut DESC)[BOOK_AUTHOR],
   (SELECT TOP 1 DateCheckedOut 
                     FROM TBookCheckouts 
                     WHERE StudentID = TStudents.ID 
                 ORDER BY DateCheckedOut DESC)[DATE_CHECKEDOUT]

当我遇到这样的问题时,我就是这样解决的,我想这就是你的解决方案。