mysql - 来自子查询（mysql）的两列

Question

有一个如下表：

TB1

ID1 NAME
--- ----
1   Mark
2   Elis

TB2

ID2 ID1 DATE       VALUE
--- --- ---------- ------
10  1   2018-01-01 100,00
20  1   2019-01-01 101,00
30  2   2018-02-01 200,00
40  2   2019-02-01 201,00

我想要两个表的结果，它们具有唯一的、降序的和它的值ID。这是我尝试过的，但它不起作用：NAMEDATE

SELECT TB1.NAME,
       (
        SELECT 
          TB2.DATE,
          TB2.VALUE 
        FROM 
          TB2 
        WHERE 
          TB1.ID1=TB2.ID1 
        ORDER BY 
          TB2.DATE DESC 
        LIMIT 1
       ) 
       AS M_DATE,M_VALUE
       FROM 
          TB1
      WHERE 
          TB1.ID1>0

我的预期结果是：

EXPECTED RESULT
===============
NAME M_DATE     M_VALUE
---- ---------- -------
Mark 2019-01-01 101,00
Elis 2019-02-01 201,00

标签： mysqlselectsubquery

Answer 1

您可以使用以下内容：

SELECT TB1.NAME, TB2.`DATE`, TB2.VALUE
FROM TB1 INNER JOIN (
  SELECT ID1, MAX(`DATE`) AS M_DATE 
  FROM TB2 GROUP BY ID1
) TB2_MAX ON TB1.ID1 = TB2_MAX.ID1
  INNER JOIN TB2 ON TB2_MAX.ID1 = TB2.ID1 AND TB2_MAX.M_DATE = TB2.`DATE`
WHERE TB1.ID1 > 0

dbfiddle.uk 上的演示