mysql - 如何在两张桌子之间获得最高收入？

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

标签： mysqlsql

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表达式是在较大的那个之间进行选择，并将其与copys_sold 相乘。我认为这很简单。然后每本书我都使用直接减法来计算年份差异 year YEAR(CURDATE())-YEAR(date_published)。完成后，我将其作为子查询然后进行计算。

这是更新的小提琴。