时间戳

首页 > 解决方案 > 如何从左连接三个表中选择本月的总和

mysql - 如何从左连接三个表中选择本月的总和

问题描述

目前是 2021 年 3 月，我正在尝试从三个表中选择总和，如果不是这个月，那么今年的输出为 0。

我进行手动计算时的正确输出是我想要的输出：

Alkohol 70% 1000 ML   0 
Amoxicillin 500 mg    0 
Amoxicillin Syrup     0  
Asam Askorbat         0  
Combiven              0  
Masker                100

我试过这个，但输出不是我想要的：

SELECT table_one.name, CASE WHEN (MONTH(table_two.date) = MONTH(NOW())) AND (YEAR(table_two.date) = YEAR(NOW()))
       THEN SUM(income)
       ELSE 0
END AS total_income
FROM table_one
LEFT JOIN table_three ON table_one.id_one = table_three.one_id
LEFT JOIN table_two ON table_two.id_two = table_three.one_id
WHERE
(id_one NOT IN (SELECT one_id FROM table_three)
OR id_one IN (SELECT one_id FROM table_three))
GROUP BY one_id

这是我的数据和结构：

CREATE TABLE table_one (
  id_one INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(255) NOT NULL,
  unit VARCHAR(255) NOT NULL,
  stock INT(11) NOT NULL,
  PRIMARY KEY (id_one)
);

INSERT  INTO table_one VALUES 
(2,'Alkohol 70% 1000 ML','Botol',50),
(3,'Amoxicillin 500 mg','Tablet',0),
(4,'Amoxicillin Syrup','Botol',75),
(5,'Asam Askorbat','Tablet',0),
(6,'Combiven','Sup',25),
(16,'Masker','Tube',200);

CREATE TABLE table_three (
  id_three INT(11) NOT NULL AUTO_INCREMENT,
  batch CHAR(1) NOT NULL,
  income INT(11) NOT NULL,
  exp_date DATE NOT NULL,
  descr TEXT DEFAULT NULL,
  one_id INT(11) NOT NULL,
  two_id INT(11) NOT NULL,
  PRIMARY KEY (id_three),
  KEY one_id (one_id),
  KEY two_id (two_id),
  CONSTRAINT table_three_ibfk_1 FOREIGN KEY (one_id) REFERENCES table_one (id_one),
  CONSTRAINT table_three_ibfk_2 FOREIGN KEY (two_id) REFERENCES table_two (id_two)
);


INSERT  INTO table_three VALUES 
(1,'A',1000,'2022-02-22','ket.',6,1),
(2,'1',500,'2021-02-22','',2,4),
(3,'4',50,'1970-01-01','',5,5),
(4,'7',75,'1970-01-01','',4,5),
(5,'1',100,'1970-01-01','',6,4),
(6,'',100,'1970-01-01','',16,6),
(7,'',100,'1970-01-01','',16,5),
(8,'',50,'1970-01-01','',16,7),
(11,'',50,'0000-00-00',NULL,16,7);

/*Table structure for table table_two */

CREATE TABLE table_two (
  id_two INT(11) NOT NULL AUTO_INCREMENT,
  DATE DATE NOT NULL,
  source VARCHAR(64) NOT NULL,
  file_source TEXT NOT NULL,
  descr TEXT DEFAULT NULL,
  PRIMARY KEY (id_two)
);

INSERT  INTO table_two VALUES 
(1,'2021-02-18','Lain-lain','bukti barang masuk_3.jpg','test'),
(4,'2021-02-18','Pembelian dengan dana JKN','bukti barang masuk_2.jpg','keterangan'),
(5,'2021-02-21','Dinas kesehatan','bukti barang masuk_1.jpg','set'),
(6,'2021-02-26','Pembelian dengan dana JKN','bukti barang masuk.jpg',''),
(7,'2021-03-02','Pembelian dengan dana JKN','4_faktur pembelian.jpg','');

我应该怎么办？

标签： mysql

解决方案

这是您想要的 sql 请求：

SELECT 
    `table_one`.`NAME`, 
    SUM(CASE WHEN (MONTH(`table_two`.`DATE`) = MONTH(NOW())) AND (YEAR(`table_two`.`DATE`) = YEAR(NOW()))
       THEN `table_three`.`income`
       ELSE 0
       END
    ) AS total_income
FROM `table_one`
LEFT JOIN `table_three` ON `table_three`.`one_id` = `table_one`.`id_one`
LEFT JOIN `table_two` ON `table_two`.`id_two` = `table_three`.`two_id`
GROUP BY `table_one`.`id_one`

你很亲近

主要部分是在 CASE 的作用下进行 SUM。

推荐阅读