大家好!我决定用我创建的这个数据库项目来解决一些问题。数据库是可以工作的但是我们在课上没有学到case when。使用count函数的语句，但我正在尝试自学。
这是问题，我试图显示每本书的销售量，但由于某种原因，在我创建的视图中，这里的计数没有正确显示。
下面是创建视图的代码:- 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 image
*编辑下面添加了对示例数据的请求:
INSERT INTO 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")
;
CREATE TABLE
-- -- -- -- -- -- -- -- -- -- -- -- 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
);
不需要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表中由于没有匹配而具有空值的行不被计算。