按月联接3个表和组

c3frrgcw 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(278)

我试着每月加入3个表组，计算2个表的和。我可以把所有的表连在一起，但总数不对。我的table：
表月份
表格发票
餐桌采购
我的问题：

$query = $this->db->query("SELECT month.month_name as month, 
    SUM(table_purchase.subtotal) AS total_pur,
    SUM(table_invoice.subtotal) AS total_inv  
    FROM month 
    LEFT JOIN table_purchase ON (month.month_num = MONTH(table_purchase.date_pur) AND YEAR(table_purchase.date)= '2018')
    LEFT JOIN table_invoice ON (month.month_num = MONTH(table_invoice.date_inv) AND YEAR(table_invoice.date)= '2018')
    GROUP BY month.month_name ORDER BY month.month_num");

结果应该是这样的：

+---------------------------+
|Month| Total Inv| Total Pur|
+---------------------------+
| Jan |    30    |  80      |
+---------------------------+
| Feb |    90    |  48      |
+---------------------------+
| Mar |   110    |  42      |
+---------------------------+
| Apr |    60    |  96      |
+---------------------------+
| May |  null    |  72      |
+---------------------------+
| Jun |    30    |  56      | 
+---------------------------+
| Jul |    32    |  10      |
+---------------------------+
| Agt |    48    |  36      |
+---------------------------+
| Sep |  null    | null     |
+---------------------------+
| Okt |  null    | null     |
+---------------------------+
| Nov |  null    | null     |
+---------------------------+
| Des |  null    | null     |
+---------------------------+

但几个月来我都算错了。我认为它会重演。我试过类似的内部连接，和连接。但我还是算错了。如何纠正？谢谢
编辑，我的表中的数据：
月份：

INSERT INTO `month` (`month_num`, `month_name`) VALUES
(1, 'Jan'),
(2, 'Feb'),
(3, 'Mar'),
(4, 'Apr'),
(5, 'May'),
(6, 'Jun'),
(7, 'Jul'),
(8, 'Agt'),
(9, 'Sep'),
(10, 'Okt'),
(11, 'Nov'),
(12, 'Des');

表1发票

INSERT INTO `table_invoice` (`id`, `ref`, `med`, `unit_price`, `qty`, `subtotal`, `customer`, `date`, `grandtotal`) VALUES
(80, 'JMBSErmmo0', 'Adona', 15000, 1, 15000, 'Melinda', '2018-08-09', 29000),
(81, 'JMBSErmmo0', 'Asam Mefenamat', 4000, 1, 4000, 'Melinda', '2018-08-09', 29000),
(82, 'JMBSErmmo0', 'Salbutamol', 10000, 1, 10000, 'Melinda', '2018-08-09', 29000),
(87, 'xl23Q6whsP', 'Salbutamol', 10000, 3, 30000, 'Melinda', '2018-01-10', 30000),
(90, '9ab7RcZHma', 'Adrome', 15000, 6, 90000, 'Doni', '2018-02-14', 90000),
(91, 'fAMEjr9rA0', 'Ambroxol', 22000, 5, 110000, 'Amirullah', '2018-03-14', 110000),
(92, 'so5518T1vI', 'Salbutamol', 10000, 6, 60000, 'Doni', '2018-04-18', 60000),
(95, 'M5mhiUJs5y', 'Adona', 15000, 1, 15000, 'Melinda', '2018-08-22', 19000),
(96, 'M5mhiUJs5y', 'Asam Mefenamat', 4000, 1, 4000, 'Melinda', '2018-08-22', 19000),
(97, 'VS27jjD5Ze', 'Ambroxol', 22000, 1, 22000, 'Pitaloka', '2018-07-18', 32000),
(98, 'VS27jjD5Ze', 'Stimuno', 10000, 1, 10000, 'Pitaloka', '2018-07-18', 32000),
(99, 'nyptFm9i43', 'Stimuno', 10000, 3, 30000, 'Andi', '2018-06-13', 30000),
(100, '3ilz1ZvPj5', 'Stimuno', 10000, 11, 110000, 'Andi', '2017-05-17', 110000);

表1采购

INSERT INTO `table_purchase` (`id`, `ref`, `med`, `unit_price`, `qty`, `subtotal`, `supplier`, `date`, `grandtotal`) VALUES
(3, 'L8BvtCyFDZ', 'Salbutamol', 6000, 3, 18000, 'Bina Jaya Apotek', '2018-08-11', 24000),
(4, 'L8BvtCyFDZ', 'Stimuno', 6000, 1, 6000, 'Bina Jaya Apotek', '2018-08-11', 24000),
(8, 'pwoaFcmGVs', 'Asam Mefenamat', 4000, 12, 48000, 'Bina Jaya Apotek', '2018-02-22', 48000),
(9, '96kHYMepS9', 'Salbutamol', 6000, 7, 42000, 'Bina Jaya Apotek', '2018-03-21', 42000),
(10, 'Tbb6pLVCJr', 'Ambroxol', 20000, 4, 80000, 'Bina Jaya Apotek', '2018-01-24', 80000),
(11, 'fdMGrBa4nS', 'Stimuno', 6000, 16, 96000, 'Bina Jaya Apotek', '2018-04-18', 96000),
(12, 'aDxtUA0rsc', 'Adrome', 12000, 6, 72000, 'Bina Jaya Apotek', '2018-05-09', 72000),
(13, '5UCwWCC7Yb', 'Asam Mefenamat', 4000, 3, 12000, 'Bina Jaya Apotek', '2018-08-16', 12000),
(14, 'KCZxZ1MqID', 'Ambroxol', 20000, 2, 40000, 'Bina Jaya Apotek', '2018-07-18', 40000),
(15, 'Ar9235n1ny', 'Adrome', 12000, 3, 36000, 'Kenanga Apotek', '2018-06-06', 56000),
(16, 'Ar9235n1ny', 'Ambroxol', 20000, 1, 20000, 'Kenanga Apotek', '2018-06-06', 56000);

mysql Join left-join

来源：https://stackoverflow.com/questions/52268783/join-3-tables-sum-group-by-month

2条答案

按热度按时间

wsewodh21#

你的问题是发生的，因为连接正在创建一个倍增的效果；例如，当同一个月有3张发票和2次采购时，您将得到6行数据（3*2），导致所有值都太大。您需要对每年每个月的所有采购和发票分别求和，然后将结果加入到月份表中：

SELECT m.month_name as month, 
    p.total_pur,
    i.total_inv  
    FROM month m
    LEFT JOIN (SELECT YEAR(date) AS y, MONTH(date) AS m, SUM(subtotal) AS total_pur FROM table_purchase GROUP BY y, m) p
        ON m.month_num = p.m AND p.y = 2018
    LEFT JOIN (SELECT YEAR(date) AS y, MONTH(date) AS m, SUM(subtotal) AS total_inv FROM table_invoice GROUP BY y, m) i
        ON m.month_num = i.m AND i.y = 2018
    GROUP BY m.month_name ORDER BY month

赞(0）回复(0）举报 2021-06-21

4urapxun2#

计算不同子条款中发票和采购的金额，然后与月份表左联接

SELECT m.month_name as month, 
   i.total_inv, 
   p.total_pur
FROM month m
LEFT JOIN (SELECT MONTH(date_inv) as month, 
            SUM(subtotal) as total_inv  
            FROM table_invoice
            WHERE YEAR(date_inv)= '2018'
            GROUP BY month) i  ON (m.month_num = i.month)    
LEFT JOIN (SELECT MONTH(date_pur) as month, 
            SUM(subtotal) as total_pur
            FROM  table_purchase 
            WHERE YEAR(date_pur)= '2018'
            GROUP BY month) p ON (m.month_num = p.month )
ORDER BY m.month_num

赞(0）回复(0）举报 2021-06-21

我来回答

按月联接3个表和组

2条答案

相关问题

热门标签

最新问答