Question

1421. 净现值查询

English Version

题目描述

表: NPV

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| id      | int   |
| year      | int   |
| npv       | int   |
+---------------+---------+
(id, year) 是该表主键(具有唯一值的列的组合).
该表有每一笔存货的年份, id 和对应净现值的信息.

 

表: Queries

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| id      | int   |
| year      | int   |
+---------------+---------+
(id, year) 是该表主键(具有唯一值的列的组合).
该表有每一次查询所对应存货的 id 和年份的信息.

 

编写解决方案，找到 Queries 表中每一次查询的净现值。

结果表 没有顺序要求 。

查询结果的格式如下所示:

 

示例 1：

输入：
NPV 表:
+------+--------+--------+
| id   | year   | npv  |
+------+--------+--------+
| 1  | 2018   | 100  |
| 7  | 2020   | 30   |
| 13   | 2019   | 40   |
| 1  | 2019   | 113  |
| 2  | 2008   | 121  |
| 3  | 2009   | 12   |
| 11   | 2020   | 99   |
| 7  | 2019   | 0    |
+------+--------+--------+

Queries 表:
+------+--------+
| id   | year   |
+------+--------+
| 1  | 2019   |
| 2  | 2008   |
| 3  | 2009   |
| 7  | 2018   |
| 7  | 2019   |
| 7  | 2020   |
| 13   | 2019   |
+------+--------+
输出：
+------+--------+--------+
| id   | year   | npv  |
+------+--------+--------+
| 1  | 2019   | 113  |
| 2  | 2008   | 121  |
| 3  | 2009   | 12   |
| 7  | 2018   | 0    |
| 7  | 2019   | 0    |
| 7  | 2020   | 30   |
| 13   | 2019   | 40   |
+------+--------+--------+
解释：
(7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0.
所有其它查询的净现值都能在 NPV 表中找到.

解法

方法一

# Write your MySQL query statement below
SELECT q.*, IFNULL(npv, 0) AS npv
FROM
  Queries AS q
  LEFT JOIN NPV AS n USING (id, year);