主页 / dba / 问题 / 312116
Accepted
Daniel Halachev
Asked: 2022-05-14 23:44:17 +0800 CST

在不知道会有多少列的情况下，在另一个表的列中显示一个表的行数

  • 772

假设我有一个Library数据库和以下表格：

  • Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN)- 包含作为抽象项目、智力工作的书籍示例
ID 标题 作者 发布者 ID 语言代码 类型 发布日期 国际标准书号
1 尼罗河上的死亡 阿加莎克里斯蒂 2 CN 小说 1937-11-01 4215574186436
  • Branches(ID, Name, City, Address)- 包含不同城市的图书馆分馆
ID 姓名 城市 地址
1 名称1 伦敦 地址1
2 名称2 伯明翰 地址2
3 名称3 曼彻斯特 地址3
  • BookCopies(ID, BookID, BranchID, Condition)- 包含作为物理项目的书籍以及它们所在的分支
ID 书号 分行 ID 健康）状况
3aa99df2-7a88-4ca8-a965-046f478ac9f3 1 1 较差的
34beeffa-14c9-4796-a61c-0477be59af0f 1 1 出色的
1dc0e7cd-0f9e-42b2-829a-04de9a77ae47 1 2 平均
88f0045c-3910-4fd6-9a29-078c2d48bfb8 1 2 好的
ea3aafe9-0ada-4396-9ed3-0867912b7958 1 2 较差的
6d003fd5-83e7-4df5-9aa8-08dd61d53eb2 1 3 出色的
... ... ... ...

我想写一个查询，它为每一本书Books显示图书馆的每个分支中有多少本书的物理副本。到目前为止，我在数据库中的数据有三个带有ID1和. 我编写了以下查询，它正确显示了我想要的内容（添加了一些额外的内容，例如作者 Agatha Christie 的过滤）：23

SELECT
    Books.Title,
    Books.PublisherID,
    Books.DatePublished,
    Books.ISBN,
    r1.Branch1,
    r1.Branch2,
    r1.Branch3
FROM
    Books
    JOIN
    (SELECT
        copies.BookID,
        br1.Branch1,
        br2.Branch2,
        br3.Branch3
    FROM
        BookCopies copies
        JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch1
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=1) br1
        ON copies.BookID=br1.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch2
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=2) br2
        ON copies.BookID=br2.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch3
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=3) br3
        ON copies.BookID=br3.BookID
    GROUP BY copies.BookID, br1.Branch1, br2.Branch2, br3.Branch3) r1
    ON Books.ID=r1.BookID
WHERE Books.Author='Agatha Christie'
标题 出版商 发布日期 国际标准书号 分支1 分支2 分支3
尼罗河上的死亡 2 1937-11-01 4215574186436 2 3 1
... ... ... ... ... ... ...

但是这样的查询有两个基本问题：

  1. 我敢肯定它真的很慢，效率低下并且充满了不良做法
  2. 它针对三个分支进行了硬编码。如果图书馆开设第四，第五等分店，它就行不通了。

    如何改进我的查询，使其适用于所有分支（无论它们的数量），并且最好有效地使用资源？
sql-server select

1 个回答

  1. Best Answer

    基本上，你没有。格式化是表示层的一项工作，在数据库层中，您专注于获得正确的结果：

    SELECT BookID, BranchID, COUNT(*)
FROM BookCopies
GROUP BY BookID, BranchID

    为您提供每本书/分支组合的计数。加入书籍和分支机构：

    SELECT b.Title
    ,  b.PublisherID
    ,  b.DatePublished
    ,  b.ISBN
    ,  r.Branch
    ,  x.CNT
FROM Books b
JOIN (
    SELECT BookID, BranchID, COUNT(*) AS CNT
    FROM BookCopies
    GROUP BY BookID, BranchID
) AS x
    USING (BookID)
JOIN Branches r
    USING (BranchID)
ORDER BY b.BookID, r.BranchID

    在您的应用程序中，您可以遍历该结果集。当 bookid 更改时，您会在“报告”中创建一个新行。对于每个新的分支，您都会创建一个新列

    编辑：SQL 服务器不支持 USING 子句：

    小提琴

    SELECT b.ID as BookID
    ,  b.Title
    ,  b.PublisherID
    ,  b.DatePublished
    ,  b.ISBN
    ,  r.ID as BranchID
    ,  r.Name as Branch
    ,  x.CNT
FROM Books AS b
JOIN (
    SELECT BookID, BranchID, COUNT(*) AS CNT
    FROM BookCopies
    GROUP BY BookID, BranchID
) AS x
    ON b.ID = x.BookID
JOIN Branches AS r
    ON r.ID = x.BranchID
ORDER BY b.ID, r.ID;

    根据评论，这是一个寻找想法的课程项目。一些可能值得研究的事情是：

    STRING_AGG

    窗口函数

    按汇总、多维数据集、分组集分组

    CTE，公用表表达式

    临时表

    • 2

相关问题