SQL Server中的总计

pexxcrt2 于 2022-11-28 发布在 SQL Server

关注(0)|答案(2)|浏览(173)

我有三张table-
书籍-

BookNo BookName  BookType
123    ABC          1
555    XYZ          0

搁板

Shelf ShelfNo BookNo BookQuantity
  XB   XB01     123    5
  XB   XB02     555    3
  XB   XB03     123    8

已出版图书

ShelfNo BookName IssuedDate   QuantityIssued
  XB01    ABC      11/21/2022     2
  XB02    XYZ      11/20/2022     1
  XB03    ABC      11/21/2022     5

我的目标是找出我们拥有的图书库存总数。输出应该按图书分组。我必须合并所有包含同一本书的shelfNo，并将它们的Shelf.BookQuantity相加，然后将其添加到该特定图书的BooksIssued.QuantityIssued中。Booktype应该显示为0的儿童和1的成人。
例如，
输出量

BookNo BookName BookType Total Stock
123     ABC       adults   20        //(5+8+2+5)
555     XYZ      children   4         //(3+1)

到目前为止，我已经写了这个。我知道我在查询中选择了比输出格式中提到的多的列。这是因为我是一步一步地理解流程的。我想首先按图书对数据分组，然后对数量求和，但它不是按图书编号对数据分组。它也不是对bi.quantityissued求和。

select s.bookno, b.booktype, s.shelfno, b.bookname, s.bookquantity, 
    sum(bi.quantityissued), bi.issueddate
from Shelf s 
left outer join BooksIssued bi on s.shelfno = bi.shelfno 
left outer join Books b on s.bookno=b.bookno
where s.shelf = 'XB'
    and bi.issueddate between '11/01/2022' and  '11/07/2022'
group by  s.bookno, s.shelfno,  b.booktype,  b.bookname, s.bookquantity, bi.issueddate

请指导我下一步该怎么做，谢谢。

sql-server

来源：https://stackoverflow.com/questions/74521565/total-of-totals-in-sql-server

2条答案

按热度按时间

fcg9iug31#

这应该可以做到：

WITH baseData As
(
    SELECT BookNo, BookQty As Qty
    FROM Shelf s
    WHERE s.shelf = 'XB'
   
    UNION ALL 

    SELECT b0.BookNo, QtyIssued As Qty
    FROM BooksIssued bi
    INNER JOIN Books b0 on b0.BookName = bi.BookName
    WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
), 
grouped As
(
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM baseData
    GROUP BY BookNo
)
SELECT b.BookNo, b.BookName, b.BookType, g.[Total Stock]
FROM grouped g
INNER JOIN Books b ON b.BookNo = g.BookNo

这也显示了BooksIssued表应该使用BookNo而不是BookName的原因之一（* 多个 * 中的一个）：这样你就保存找个帮手了
我们也可以用嵌套的SELECT查询来代替公共表表达式（CTE），但我发现CTE更容易推理：

SELECT b.BookNo, b.BookName, b.BookType, g.[Total Stock]
FROM (
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM (
        SELECT BookNo, BookQty As Qty
        FROM Shelf s
        WHERE s.shelf = 'XB'
       
        UNION ALL 
    
        SELECT b0.BookNo, QtyIssued As Qty
        FROM BooksIssued bi
        INNER JOIN Books b0 on b0.BookName = bi.BookName
        WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
    ) baseData
    GROUP BY BookNo
) g
INNER JOIN Books b ON b.BookNo = g.BookNo

这仍然缺少adults与children book类型。您可以使用CASE表达式或使用 Table Value Constructor 来修复此问题。在这里我更喜欢后者，因为随着时间的推移，您可以以一种有效的方式添加更多类型，并且因为它为您最终使用一个真实的的表做好了准备，而这正是您一开始就应该拥有的：

WITH baseData As
(
    SELECT BookNo, BookQty As Qty
    FROM Shelf s
    WHERE s.shelf = 'XB'
   
    UNION ALL 

    SELECT b0.BookNo, QtyIssued As Qty
    FROM BooksIssued bi
    INNER JOIN Books b0 on b0.BookName = bi.BookName
    WHERE bi.IssuedDate >= '20221101' AND bi.IssuedDate < '20221108' 
), 
grouped As
(
    SELECT BookNo, Sum(Qty) As [Total Stock]
    FROM baseData
    GROUP BY BookNo
)
SELECT b.BookNo, b.BookName, bt.Name As BookType, g.[Total Stock]
FROM grouped g
INNER JOIN Books b ON b.BookNo = g.BookNo
INNER JOIN (VALUES (0, 'Childrens'), (1 'Adults') ) AS bt(Type, Name)   
    ON b.BookType = bt.Type;

赞(0）回复(0）举报 2022-11-28

uujelgoq2#

试试这个：

select bookno BookNo,
        bookname BookName,
        case booktype
            when 1 then 'adults'
            when 0 then 'children'
        end BookType
        SUM(TotalStock) TotalStock
from (

    select b.bookno,
           b.bookname,
           b.booktype,
           sum(bookquantity) TotalStock
    from ..books b
    inner join ..shelf s
        ON b.bookno = s.bookno
    group by b.bookno, b.bookname, b.booktype
    
    UNION ALL
    
    select b.bookno,
           b.bookname,
           b.booktype,
           sum(QuantityIssued) TotalStock
    from ..books b
    inner join ..shelf s
        ON b.bookno = s.bookno
    inner join ..booksissued bi
        ON s.shelfno = bi.shelfno
            and b.bookname = bi.bookname
    group by b.bookno, b.bookname, b.booktype
) s
group by bookno, bookname, booktype

赞(0）回复(0）举报 2022-11-28

我来回答

SQL Server中的总计

2条答案

相关问题

热门标签

最新问答