嘿所有第二學期的comp sci學生在這里。我決定對我創建的這個資料庫專案有點過頭了。資料庫可以作業，但我們在課堂上沒有學到任何關于使用 count 函式的“case when”陳述句的知識，但我正在嘗試自學。
這是我試圖顯示每本書售出多少的問題，但由于某種原因，在我創建的視圖中，這里的計數沒有正確顯示。
這是創建視圖的代碼：
--12。撰寫 2 個 SQL 視圖 視圖陳述句集應包括 SELECT 陳述句以查看視圖
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT( CASE bookNumber_FK WHEN (bookNumber = bookNumber_FK) THEN 1 ELSE 0 END) as Number_Sold
FROM Book
JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
SELECT *
FROM booksSold;
這段代碼將所有內容放在我想要的一列中，但它只是沒有正確計算所有內容，當我知道某些行應該超過 2 時，它只會上升到 2。
我還附上了 MariaDB MySQL 服務器螢屏的螢屏截圖，這樣您就可以看到當我輸入查詢時會發生什么。
正如我所說，沒有教過這個，只是試圖教自己如何去做不同的列（不是我想要的）或者它引導我展示如何計算單個專案。也許我搜索得不夠正確？
MYSQL MARIADB 鏡像
*下面添加了對樣本資料的編輯請求：
插入到 vvvv
INSERT INTO Book(bookNumber, bookTitle, bookAuthor, bookPrice, bookGenre, orderNumber_FK, publisherName_FK)
VALUES
("1", "STer WuRs", "Minestrone", 23.5, "Sci-Fi", "1", "Penguin Publishing"),
("2", "Waldo is Where?", "Branston", 15.25, "Children's", "2", "Capital Publishing"),
("3", "Parry Hotter", "Worling", 19.99, "Fantasy", "3", "Decent Publishing "),
("4", "Comic Sans the Comic", "Sansom", 12.99, "Graphic Novels and Comics", "4", "Pretty Darn Great Publishing"),
("5", "What is Email?", "Thompson", 99.99, "Textbooks/Informational", "5", "What is Email Publishing")
;
INSERT INTO BookSale(saleNum, employeeNumber_FK, customerNumber_FK, saleDate, saleAmount)
VALUES
("1", "4", "7", "2022-03-29", 38.75),
("2", "5", "6", "2022-04-03", 19.99),
("3", "1", "5", "2022-04-05", 56.48),
("4", "2", "4", "2022-04-06", 99.99),
("5", "3", "3", "2022-04-15", 99.99),
("6", "1", "2", "2022-04-19", 28.24),
("7", "2", "1", "2022-04-24", 132.97)
;
INSERT INTO isSoldIn(soldInNum, bookNumber_FK, saleNum_FK)
VALUES
("1", "1", "1"),
("2", "2", "1"),
("3", "3", "2"),
("4", "3", "3"),
("5", "4", "3"),
("6", "1", "3"),
("7", "5", "4"),
("8", "5", "5"),
("9", "2", "6"),
("10", "4", "6"),
("11", "3", "7"),
("12", "4", "7"),
("13", "5", "7")
;
創建表
-- -- -- -- -- -- -- -- -- -- -- -- Book -- -- -- -- -- -- -- -- -- -- -- --
-- Creating the Book Table
-- Primary Key: bookNumber
-- Foreign Keys: orderNumber_FK, publisherName_FK
CREATE TABLE Book
(
bookNumber VARCHAR(5) NOT NULL,
bookTitle VARCHAR(50),
bookAuthor VARCHAR(50),
bookPrice DOUBLE,
bookGenre VARCHAR(50),
orderNumber_FK VARCHAR(5),
publisherName_FK VARCHAR(30),
CONSTRAINT Book_PK PRIMARY KEY (bookNumber),
CONSTRAINT Book_FK1 FOREIGN KEY (orderNumber_FK)
REFERENCES PublisherOrders (orderNumber)
ON DELETE CASCADE,
CONSTRAINT Book_FK2 FOREIGN KEY (publisherName_FK)
REFERENCES PublisherCompany (publisherName)
ON DELETE CASCADE
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- BookSale-- -- -- -- -- -- -- -- -- -- --
-- Creating the BookSale Table
-- Primary Key: saleNum
-- Foreign Keys: employeeNumber_FK, customerNumber_FK
CREATE TABLE BookSale
(
saleNum VARCHAR(5) NOT NULL,
employeeNumber_FK VARCHAR(5),
customerNumber_FK VARCHAR(5),
saleDate DATE,
saleAmount DOUBLE,
CONSTRAINT BookSale_PK PRIMARY KEY (saleNum),
CONSTRAINT BookSale_FK1 FOREIGN KEY (employeeNumber_FK)
REFERENCES Employee (employeeNumber)
ON DELETE CASCADE,
CONSTRAINT BookSale_FK2 FOREIGN KEY (customerNumber_FK)
REFERENCES Customer (customerNumber)
ON DELETE CASCADE
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- IsSoldIn -- -- -- -- -- -- -- -- -- -- --
-- Creating the IsSoldIn Table
-- Primary Keys: soldInNum, bookNumber_FK, saleNum_FK
-- Foreign Keys: bookNumber_FK, saleNum_FK
CREATE TABLE IsSoldIn
(
soldInNum VARCHAR(5) NOT NULL,
bookNumber_FK VARCHAR(5),
saleNum_FK VARCHAR(5),
CONSTRAINT IsSoldIn_PK PRIMARY KEY (soldInNum),
CONSTRAINT IsSoldIn_FK1 FOREIGN KEY (bookNumber_FK)
REFERENCES Book (bookNumber)
ON DELETE CASCADE,
CONSTRAINT IsSoldIn_FK2 FOREIGN KEY (saleNum_FK)
REFERENCES BookSale (saleNum)
ON DELETE CASCADE
);
uj5u.com熱心網友回復：
不需要CASE表達。只需計算連接表中的行數。
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT(*) as Number_Sold
FROM Book
JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
如果您希望未售出的書籍計數為零，則需要使用LEFT JOIN.
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT(isSoldIn.bookNumber_FK) as Number_Sold
FROM Book
LEFT JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
COUNT(*)必須更改為，COUNT(isSoldIn.bookNumber_FK)以便isSoldIn表中具有空值的行，因為沒有匹配項不被計算在內。
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/467567.html
下一篇：如何撰寫平均SQL查詢？