mysql INNER JOIN 没有像我想象的那样工作

Question

有两个表:

CREATE  TABLE IF NOT EXISTS `issue_details` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    amt_offer_dlr INT NOT NULL DEFAULT 9999 COMMENT '564,510,000',
   maturity  DATE NOT NULL DEFAULT '1111-11-11' COMMENT '06/28/2012' ,
    fk_cusip6 VARCHAR(6) NOT NULL DEFAULT '' COMMENT '' ,
  PRIMARY KEY (id),
  CONSTRAINT con_issue_details__issuers FOREIGN KEY (fk_cusip6) REFERENCES issuers    (fk_cusip6) ON DELETE NO ACTION ON UPDATE CASCADE
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `identifiers` (
  id     INT NOT NULL AUTO_INCREMENT ,
  fk_cusip6 VARCHAR(6)  NOT NULL DEFAULT 'NA' ,
  PRIMARY KEY (id) ,
  CONSTRAINT con_ident__cusip6  FOREIGN KEY (fk_cusip6) REFERENCES cusip_6 (cusip6) ON DELETE NO ACTION ON UPDATE CASCADE ,
)
COMMENT ''
 ENGINE = InnoDB;

issue_details 表:注意:每个id都不一样 可以有多个相同的 fk_cusip6 数字

+-----+--------------+------------+----------------+
| id  | maturity     | fk_cusip6  |  amt_offer_dlr |
+-----+--------------+------------+----------------+
|  1  | 2013-06-28   | 567090     |  1000          |
|  2  | 2014-06-05   | 567090     |  1000          |
|  3  | 2013-06-05   | 567100     |  2500          |
|  3  | 2014-06-05   | 567100     |  2500          |
+-----+--------------+------------+----------------+

标识符表:注意:每个 fk_cusip6 号码都是不同的

+--------+-----------+
| id     | fk_cusip6 |
+--------+-----------+
| 131472 | 567090    |
| 131473 | 567100    |
+--------+-----------+

我需要生成一个查询来匹配标识符表中的每个 fk_cusip6 实例，然后对 issue_details 表中具有相同 fk_cusip6 的所有实例求和 amt_offer_dlr。最终结果应该是:

+--------------+---------+------------------------+
| ide.id | isd.fk_cusip6 |  SUM(isd.Amt_Offer_Dlr |
+--------------+---------+------------------------+
| 131472 | 567090        | 2000                   |
| 131473 | 567100        | 5000                   |
+--------------+---------+------------------------+

我试过:

SELECT CURRENT_DATE, FORMAT(SUM(isd.Amt_Offer_Dlr),0) 'Current Out Standing Debt:'
from muni.issue_details isd
INNER JOIN identifiers ide ON ide.fk_cusip6 = isd.fk_cusip6 AND  isd.fk_cusip6 = '567541'
where isd.maturity > CURRENT_DATE
;

这是所有列的总和。

我想我需要一个子查询，但我被细节困住了。

谢谢

Answer 1

试试这个:

SELECT
    i.id,
    i.fk_cusip6,
    x.amt_offer_dlr_sum

FROM identifiers i

INNER JOIN (
    SELECT
        id.fk_cusip6,
        SUM(id.amt_offer_dlr) AS amt_offer_dlr_sum
    FROM issue_details id
    GROUP BY id.fk_cusip6
) x
ON x.fk_cusip6 = i.fk_cusip6;