MySQL LEFT JOIN 在某个日期之前与 SUM 以及在该日期之前和该日期的 SUM

Question

我有三个表(pagu、realisasi 和 perencanaan)。我正在使用左连接。我想在特定日期之前对一个字段求​​和，而在该日期之前和该日期对另一个字段求​​和。

我的 pagu 表:

idpagu  kode    komponen    pagu    target_kinerja  tahun   keterangan
1   2217.057.056    Pengembangan Sistem Manajemen Mutu UPTP     52740000    1   2017    tes123
2   2220.051.051    Pelatihan Peningkatan Produktivitas     4732755000  120     2017    tes123
3   2220.052.051    Calon Wirausaha Baru yang Dilatih (RM)  12464938000     240     2017    TES123

我的现实表:

idrealisasi     idpagu  kode    komponen    realisasi   target_kinerja  tgl     keterangan
5   1   2217.057.056    Pengembangan Sistem Manajemen Mutu UPTP     52740000    1   1/14/2017   tes
6   2   2220.051.051    Pelatihan Peningkatan Produktivitas     10000000    10  1/14/2017   tes
7   2   2220.051.051    Pelatihan Peningkatan Produktivitas     20000000    20  2/14/2017   tes2
8   2   2220.051.051    Pelatihan Peningkatan Produktivitas     10000000    10  3/14/2017   tes3
9   2   2220.051.051    Pelatihan Peningkatan Produktivitas     10000000    10  6/14/2017   tes4
10  2   2220.051.051    Pelatihan Peningkatan Produktivitas     10000000    10  1/15/2017   tes

我的perencanaan表(无记录):

idperencanaan   idpagu  kode    komponen    penarikan   target_kinerja  tgl     keterangan

我的查询:

select p.idpagu,p.kode,p.komponen,p.pagu,p.target_kinerja,
if(pr.penarikan is null,0,sum(pr.penarikan)) as perenuang,
if(pr.target_kinerja is null,0,sum(pr.target_kinerja)) as perenfis,
if(r.realisasi is null,0,sum(r.realisasi)) as realuang,
if(r.target_kinerja is null,0,sum(r.target_kinerja)) as realfis,
p.pagu-if(r.realisasi is null,0,sum(r.realisasi)) as sisauang,
p.target_kinerja-if(r.target_kinerja is null,0,sum(r.target_kinerja)) as sisafis,
if(sum(rr.realisasi) is null,0,sum(rr.realisasi)) as tes
from pagu p 
left join realisasi r on p.idpagu=r.idpagu and r.tgl BETWEEN "2017-01-01" and "2017-01-15"
left join perencanaan pr on p.idpagu=pr.idpagu and r.tgl BETWEEN "2017-01-01" and "2017-01-15"
left join realisasi rr on p.idpagu=rr.idpagu and rr.tgl < "2017-01-15"
group by p.idpagu,r.idpagu,rr.idpagu

我的结果:

idpagu  kode    komponen    pagu    target_kinerja  perenuang   perenfis    realuang    realfis     sisauang    sisafis     tes
1   2217.057.056    Pengembangan Sistem Manajemen Mutu UPTP     52740000    1   0   0   52740000    1   0   0   52740000
2   2220.051.051    Pelatihan Peningkatan Produktivitas     4732755000  120     0   0   20000000    20  4712755000  100     20000000
3   2220.052.051    Calon Wirausaha Baru yang Dilatih (RM)  12464938000     240     0   0   0   0   12464938000     240     0

我的问题:

tes 列是当前日期“2017-01-15”之前的 pagu - realuang，第二行应该是 10.000.000，但我的查询给出了 20.000.000。如何编写查询，以便在第二条记录中，tes 列为 10.000.000？

我的模式:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `pagu`;
CREATE TABLE `pagu` (
  `idpagu` int(11) NOT NULL AUTO_INCREMENT,
  `kode` varchar(45) NOT NULL,
  `komponen` varchar(250) NOT NULL,
  `pagu` bigint(20) NOT NULL,
  `target_kinerja` int(11) NOT NULL,
  `tahun` varchar(4) NOT NULL,
  `keterangan` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`idpagu`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `pagu` VALUES ('1', '2217.057.056', 'Pengembangan Sistem Manajemen Mutu UPTP', '52740000', '1', '2017', 'tes123');
INSERT INTO `pagu` VALUES ('2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '4732755000', '120', '2017', 'tes123');
INSERT INTO `pagu` VALUES ('3', '2220.052.051', 'Calon Wirausaha Baru yang Dilatih (RM)', '12464938000', '240', '2017', 'TES123');

DROP TABLE IF EXISTS `perencanaan`;
CREATE TABLE `perencanaan` (
  `idperencanaan` int(11) NOT NULL AUTO_INCREMENT,
  `idpagu` int(11) NOT NULL,
  `kode` varchar(45) NOT NULL,
  `komponen` varchar(250) NOT NULL,
  `penarikan` bigint(20) NOT NULL,
  `target_kinerja` int(11) NOT NULL,
  `tgl` date NOT NULL,
  `keterangan` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`idperencanaan`),
  KEY `fk_perencanaan_pagu1_idx` (`idpagu`),
  CONSTRAINT `fk_perencanaan_pagu1` FOREIGN KEY (`idpagu`) REFERENCES `pagu` (`idpagu`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `realisasi`;
CREATE TABLE `realisasi` (
  `idrealisasi` int(11) NOT NULL AUTO_INCREMENT,
  `idpagu` int(11) NOT NULL,
  `kode` varchar(45) NOT NULL,
  `komponen` varchar(250) NOT NULL,
  `realisasi` bigint(20) NOT NULL,
  `target_kinerja` int(11) NOT NULL,
  `tgl` date NOT NULL,
  `keterangan` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`idrealisasi`),
  KEY `fk_perencanaan_pagu1_idx` (`idpagu`),
  CONSTRAINT `fk_perencanaan_pagu10` FOREIGN KEY (`idpagu`) REFERENCES `pagu` (`idpagu`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `realisasi` VALUES ('5', '1', '2217.057.056', 'Pengembangan Sistem Manajemen Mutu UPTP', '52740000', '1', '2017-01-14', 'tes');
INSERT INTO `realisasi` VALUES ('6', '2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '10000000', '10', '2017-01-14', 'tes');
INSERT INTO `realisasi` VALUES ('7', '2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '20000000', '20', '2017-02-14', 'tes2');
INSERT INTO `realisasi` VALUES ('8', '2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '10000000', '10', '2017-03-14', 'tes3');
INSERT INTO `realisasi` VALUES ('9', '2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '10000000', '10', '2017-06-14', 'tes4');
INSERT INTO `realisasi` VALUES ('10', '2', '2220.051.051', 'Pelatihan Peningkatan Produktivitas', '10000000', '10', '2017-01-15', 'tes');

Answer 1

根据您的查询和表名和列名进行猜测(pagu = ceiling，realisasi = actual，perencanaan = planned)，对于每个上限，您将在特定范围内的几天内计算其实际值、这些天的计划值、它的其中一些日期的实际值，以及一些相关的目标值，将 NULL 值视为 0。

我假设如果上限在某个日期具有实际值，那么它在该日期具有计划值。否则，您的查询将涉及 FULL JOIN，而 LEFT JOIN 有时会错误地形成具有不同日期的行等。所以我怀疑您缺少 FK(外键)。

您的第一个 LEFT JOIN 获取某些日期每个上限的实际值。那么您想要一个上限和日期的任何计划值。所以您缺少 和 r.tgl = pr.tgl。然后，您需要其中一些日期的上限和日期 的任何实际值。所以您的第三个 LEFT JOIN 缺少 和 r.tgl = rr.tgl。

您的分组和汇总不正确。如果 SELECT 在聚合调用之外使用一个列，那么它对于 GROUPed 列的每个子行应该是单值的。 (GROUP BY 对 GROUPed 列的每个子行返回一行。)(SET sql_mode = 'ONLY_FULL_GROUP_BY' 不允许 MySQL 的非标准行为。)

select p.idpagu, count(*), p.pagu, p.target_kinerja,
    sum(coalesce(pr.penarikan,0)) as pr_penarikan,
    sum(coalesce(pr.target_kinerja,0)) as pr_target_kinerja,
    sum(coalesce(r.realisasi,0)) as r_realisasi,
    sum(coalesce(r.target_kinerja,0)) as r_target_kinerja,
    p.pagu-sum(coalesce(r.realisasi,0)) as d_realisasi,
    p.target_kinerja-sum(coalesce(r.target_kinerja,0)) as d_target_kinerja,
    sum(coalesce(rr.realisasi,0)) as rr_realisasi
from pagu p
left join realisasi r on p.idpagu=r.idpagu and r.tgl BETWEEN "2017-01-01" and "2017-01-15"
left join perencanaan pr on p.idpagu=pr.idpagu and r.tgl = pr.tgl and r.tgl BETWEEN "2017-01-01" and "2017-01-15"
left join realisasi rr on p.idpagu=rr.idpagu and r.tgl = rr.tgl and r.tgl < "2017-01-15"
group by p.idpagu, p.pagu, p.target_kinerja;

idpagu  pagu                    pr_penarikan    r_realisasi     d_realisasi             rr_target_kinerja 
    count(*)        target_kinerja  pr_target_kinerja       r_target_kinerja    d_target_kinerja    
1   1   52740000    1           0   0           52740000    1   0               0       52740000
2   2   4732755000  120         0   0           20000000    20  4712755000      100     10000000
3   1   12464938000 240         0   0           0           0   12464938000     240     0