mysql - sqlquery 不工作给出错误

Question

我要为图书数据库编写一个查询。问题是按降序列出每个作者在 1995-2000 年的总收入。

SELECT DISTINCT b.author,b.earnings as TOTAL_EARNING
FROM BookAuthor as b lEFT JOIN
     BookPublish
     on b.book=BookPublish.book
WHERE (b.earnings=COUNT(b.earnings)) AND (year BETWEEN 1995 AND 2000)
ORDER BY b.earnings desc ;

错误或:群组功能使用无效。

create table BookPublish( 
      book varchar(255) ,
      year int,
      publisher varchar(255),
      price int,
      num int,
      PRIMARY KEY (book)
    );

    //INSERT INTO BookPublish VALUES('Math-1',1999,'Mcgraw',120,5000);
    //INSERT INTO BookPublish VALUES('Physics',2001,'Matt Woods',85,2500);
    //INSERT INTO BookPublish VALUES('Chemistry',1996,'McGraw',99,2300);
    //INSERT INTO BookPublish VALUES('Biology',1999,'Krishna',75,2000);
    //INSERT INTO BookPublish VALUES('Economics',2000,'Maze',125,3000);
    //INSERT INTO BookPublish VALUES('Engineering',1994,'ABC',180,8000);
    //INSERT INTO BookPublish VALUES('Medicine',1997,'ABC',180,9000);


    create table BookAuthor( 
      book varchar(255) ,
      author varchar(255) ,
      earnings int,
      PRIMARY KEY (book,author)
    );


    //INSERT INTO BookAuthor VALUES('Math-1','Chris,Norman',12000);
    //INSERT INTO BookAuthor VALUES('Physics','Matt Woods',4000);
    //INSERT INTO BookAuthor VALUES('Chemistry','John Bayer',8200);
    //INSERT INTO BookAuthor VALUES('Biology','Craig S',3000);
    //INSERT INTO BookAuthor VALUES('Economics','Manohar',17000);
    //INSERT INTO BookAuthor VALUES('Engineering','Sterling',13050);
    //INSERT INTO BookAuthor VALUES('Medicine','Craig S',10000);

    create table BookReview( 
      book varchar(255) ,
      reviewer varchar(255) ,
      score int,
      PRIMARY KEY (book,reviewer)  ,
      FOREIGN KEY (book) REFERENCES BookPublish(book)
    );


    //INSERT INTO BookReview VALUES('Math-1','Paul Gray',7);
    //INSERT INTO BookReview VALUES('Math-1','Daphne Merkin',8);
    //INSERT INTO BookReview VALUES('Physics','Daphne Merkin',6);
    //INSERT INTO BookReview VALUES('Chemistry','John',9);
    //INSERT INTO BookReview VALUES('Biology','Jack',10);
    //INSERT INTO BookReview VALUES('Economics','Paul Gray',7.5);
    //INSERT INTO BookReview VALUES('Engineering','Paul Gray',8);
    //INSERT INTO BookReview VALUES('Engineering','Daphne Merkin',7);


    create table BookReference( 
      book varchar(255) ,
      bookreferenced varchar(255) ,
      PRIMARY KEY (book, bookreferenced)  ,
      FOREIGN KEY (book) REFERENCES BookPublish(book)
    );

    //INSERT INTO BookReference VALUES('Math-1','Introduction to Math');
    //INSERT INTO BookReference VALUES('Physics','Foundation of Physics');
    //INSERT INTO BookReference VALUES('Chemistry','Introduction to Chemistry');
    //INSERT INTO BookReference VALUES('Biology','Molecular Biology');
    //INSERT INTO BookReference VALUES('Economics','Micro-Economics');
    //INSERT INTO BookReference VALUES('Engineering','Engineering-1');

Answer 1

你在 where 子句中有这个:

 b.earnings = COUNT(b.earnings)

您不能混合聚合值和非聚合值。

你想要的是group by:

SELECT ba.author, sum(ba.earnings) as TOTAL_EARNING
FROM BookAuthor as ba lEFT JOIN
     BookPublish bp
     on ba.book = bp.book
WHERE year BETWEEN 1995 AND 2000
GROUP BY ba.author
ORDER BY sum(ba.earnings) desc ;