Итого в SQL Server

У меня есть 3 таблицы -

Книги -

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

Пожалуйста, направьте меня, что мне делать дальше. Спасибо.

Совет по схеме: BooksIssued почти наверняка следует использовать BookNo вместо BookName. Кроме того, кажется, что вам не хватает стола BookType.

Joel Coehoorn 21.11.2022 16:55

Также: литералы даты неверны. Наконец, возможен ли выпуск новых книг, у которых еще нет места на полке?

Joel Coehoorn 21.11.2022 16:58

Схема @JoelCoehoorn уже кем-то создана, и я не могу ее изменить. Мне нужно только написать запрос.

Anonymous 21.11.2022 16:59

@JoelCoehoorn, нет, у них нет книжного стола. и нет, нельзя ли выпускать новые книги, у которых еще нет записи на полке

Anonymous 21.11.2022 17:01

@JoelCoehoorn Что касается даты, вот как она отображается в их базе данных.

Anonymous 21.11.2022 17:02

Нет, это удобство, которое предоставляет инструмент. Сама база данных хранит данные в двоичном, неудобочитаемом формате. Правильный способ обработки этих литералов — 20221101 и 20221107. Кроме того, вероятно, лучше не использовать для этого between, а вместо этого иметь два отдельных утверждения, где нижняя граница является инклюзивной, а верхняя — _эксклюзивной (< вместо <=) на следующую дату (20221108).

Joel Coehoorn 21.11.2022 17:03
Как настроить Tailwind CSS с React.js и Next.js?
Как настроить Tailwind CSS с React.js и Next.js?
Tailwind CSS - единственный фреймворк, который, как я убедился, масштабируется в больших командах. Он легко настраивается, адаптируется к любому...
LeetCode запись решения 2536. Увеличение подматриц на единицу
LeetCode запись решения 2536. Увеличение подматриц на единицу
Увеличение подматриц на единицу - LeetCode
Переключение светлых/темных тем
Переключение светлых/темных тем
В Microsoft Training - Guided Project - Build a simple website with web pages, CSS files and JavaScript files, мы объясняем, как CSS можно...
Отношения &quot;многие ко многим&quot; в Laravel с методами присоединения и отсоединения
Отношения &quot;многие ко многим&quot; в Laravel с методами присоединения и отсоединения
Отношения "многие ко многим" в Laravel могут быть немного сложными, но с помощью Eloquent ORM и его моделей мы можем сделать это с легкостью. В этой...
В PHP
В PHP
В большой кодовой базе с множеством различных компонентов классы, функции и константы могут иметь одинаковые имена. Это может привести к путанице и...
Карта дорог Беладжар PHP Laravel
Карта дорог Беладжар PHP Laravel
Laravel - это PHP-фреймворк, разработанный для облегчения разработки веб-приложений. Laravel предоставляет различные функции, упрощающие разработку...
1
6
50
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Это должно сделать это:

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 vs children. Вы можете исправить это с помощью выражения CASE или с помощью конструктора табличных значений . Я предпочитаю последнее здесь, потому что вы можете добавить гораздо больше типов с течением времени эффективным способом, и потому что это настраивает вас в конечном итоге на использование реальной таблицы, которая у вас должна была быть в первую очередь:

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;  

Попробуй это:

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

Что с nolock? Тот, кто сказал вам, что это был простой трюк «быстрее», был неправ. Это может иметь значение, но это гораздо меньшее влияние, чем вы думали, и везде, где это действительно помогает, потому что весьма вероятно, что задействованы устаревшие данные.

Joel Coehoorn 21.11.2022 17:14

@JoelCoehoorn Извините, по привычке. . . администраторы базы данных в моем офисе строго следят за этим. . . я удалил

codeMonkey 21.11.2022 17:21

Другие вопросы по теме