如何在两个表之间获得最高的收入？

Question

我有三个表，如果版税价值取决于首选版税高于或低于标准版税，我如何获得终生收益价值？

这是使用SELECT IF的情况吗？

另外，根据他们第一次发表作品的日期到今天，我如何获得他们的平均年收入？

假设:作者总是收到他们喜欢的版税中最高的，或者出版商的标准版税。

我目前的查询是错误的，因为它没有考虑到它会在首选或标准之间选择哪个版税来源。我不确定该使用哪条Select语句。

当前的SQL文件系统https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7afcd23342ffb024a2a7f2d2a270d0c1

**author**
id*
first_name
last_name
date_of_birth
preferred_royalties

**book**
id*
name
date_published
msrp
copies_sold
author_id
publisher_id

**publisher**
id*
name
standard_royalties

这就是我想出来的，但我知道这是错误的。

SELECT
  author.first_name,
  author.last_name,
  (SELECT
    SUM (
      author.preferred_royalties * book.copies_sold
    ) AS lifetime earnings
  FROM
    author
    RIGHT JOIN book
      ON author.id = book.author_id)
  (SELECT
    SUM (
      (
        author.preferred_royalties * book.copies_sold
      ) / diffdate (yy, book.date_published, getdate ()) AS average earnings per YEAR FROM author
      RIGHT JOIN book
        ON author.id = book.author_id
    )
  FROM
    author
    RIGHT JOIN book
      ON author.id = book.author_id
  GROUP BY book.author_id

Answer 1

如果我们至少有示例数据，理解起来会容易得多，但我想这个想法是一个开始：

SELECT first_name, last_name, 
       SUM(EarningByBook) AS 'Lifetime Earning', 
       SUM(copies_sold) AS 'Copies Sold',
       SUM(years) as 'Total Years',
       SUM(EarningByBook)/SUM(Years) AS 'AVG earning per year'
FROM
(SELECT first_name, last_name,
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       copies_sold ,
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id) V
GROUP BY 
 first_name, last_name;

我从你的问题中可以理解的是，首先，你想要根据preferred_royalties和standard_royalties中较大的值来计算收益。因此，我执行的基本查询如下：

SELECT first_name, last_name,
       /*here*/
       CASE WHEN preferred_royalties > standard_royalties
                THEN preferred_royalties*copies_sold
                ELSE standard_royalties*copies_sold END 
       AS 'EarningByBook', 
       /**/
       copies_sold ,
       YEAR(CURDATE())-YEAR(date_published) Years
FROM author A 
JOIN book B ON A.id=B.author_id 
JOIN publisher P ON B.publisher_id=P.id

因此，CASE表达式选择较大的值并将其与copies_sold相乘。我认为这是非常直接的。然后每本书我都用YEAR(CURDATE())-YEAR(date_published)的直接减法来计算年份差。完成后，我将其作为子查询，然后进行计算。

Here is the updated fiddle。