mysql - 如何根据同一月份值的另一个 SELECT 的值进行选择

Question

所以，我有两个数据，它们没有任何关系

第一个表

tglAmbil    Satuan  Harga
11-08-2017  1       10000
11-08-2017  2       10000
15-08-2017  2       10000
01-09-2017  2       10000

第二个表

tglAmbil    Satuan  Harga
21-08-2017  1       10000

我尝试让我的 SELECT 结果看起来像这样:

Month(tglAmbil)    date_format(tglAmbil,"$m")  Harga
8                  Agustus                     60000
9                  September                   20000

使用此查询:

SELECT 

MONTH(`tglAmbil`),

DATE_FORMAT(tglAmbil,"%M"),

SUM(detaillpjunbudged.satuan * detaillpjunbudged.harga) + 
IFNULL(prokers.total,0) 

FROM `unbudged` LEFT JOIN lpjunbudged ON unbudged.kdUnbudgeding = 
lpjunbudged.kdUnbudgeding 

LEFT JOIN detaillpjunbudged ON lpjunbudged.kdLpjUnbudged = 
detaillpjunbudged.kdLpjUnbudged,

(SELECT MONTH(`tglAmbil`) AS 
tgl,DATE_FORMAT(tglAmbil,"%M"),SUM(detaillpjproker.satuan * 
detaillpjproker.harga) AS total,`kdDetailProker` FROM `realisasiproker` LEFT 
JOIN lpjproker ON realisasiproker.kdRealisasiProker = 
lpjproker.kdRealisasiProker LEFT JOIN detaillpjproker ON lpjproker.kdLPJ = 
detaillpjproker.kdLPJ GROUP BY MONTH(tglAmbil)) AS prokers 

WHERE MONTH(`tglAmbil`) = prokers.tgl GROUP BY MONTH(`tglAmbil`)

但是我得到的结果是:

Month(tglAmbil)    date_format(tglAmbil,"$m")  Harga
8                  Agustus                     60000

那么，真正的原因是什么？我对这个语法问题感到困惑。谢谢

Answer 1

考虑以下因素:

DROP TABLE IF EXISTS table1;

CREATE TABLE table1
(purchase_date DATE NOT NULL
,quantity INT NOT NULL
,price INT NOT NULL
);

INSERT INTO table1 VALUES
('2017-08-11',1,10000),
('2017-08-11',2,10000),
('2017-08-15',2,10000),
('2017-09-01',2,10000);

DROP TABLE IF EXISTS table2;

CREATE TABLE table2
(purchase_date DATE NOT NULL
,quantity INT NOT NULL
,price INT NOT NULL
);

INSERT INTO table2 VALUES
('2017-08-21',1,10000);


SELECT DATE_FORMAT(purchase_date,'%Y-%m') yearmonth
     , SUM(quantity*price) total 
  FROM
     (
     SELECT * FROM table1
      UNION
     SELECT * FROM table2
     ) x
 GROUP 
    BY yearmonth;
+-----------+-------+
| yearmonth | total |
+-----------+-------+
| 2017-08   | 60000 |
| 2017-09   | 20000 |
+-----------+-------+