使用SQL查询选择数据库数据时出错？

DomainFlag

我有以下3张桌子：

adhocbills 桌子：

CREATE TABLE IF NOT EXISTS `adhocbills` 
(
  `sequence` bigint(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(200) NOT NULL,
  `type` varchar(200) NOT NULL,
  `invoice_number` varchar(200) NOT NULL,
  PRIMARY KEY (`sequence`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `adhocbills` (`sequence`, `status`, `type`, `invoice_number`) 
VALUES (1, 'Completed', 'Invoice', '1234');

adhocbills_lineitems 桌子：

CREATE TABLE IF NOT EXISTS `adhocbills_lineitems` 
(
  `sequence` bigint(11) NOT NULL AUTO_INCREMENT,
  `bill_seq` varchar(20) NOT NULL,
  `service` varchar(200) NOT NULL,
  PRIMARY KEY (`sequence`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `adhocbills_lineitems` (`sequence`, `bill_seq`, `service`) 
VALUES (1, '1', 'Service 1'), (2, '1', 'Service 2'),
       (3, '1', 'Service 2');

billing_invoices 桌子：

CREATE TABLE IF NOT EXISTS `billing_invoices` 
(
  `sequence` bigint(20) NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(200) NOT NULL,
  `sub_total` float NOT NULL,
  `vat_amount` float NOT NULL,
  `grand_total` float NOT NULL,
  `datetime` date NOT NULL,
  `invoice_type` varchar(100) NOT NULL,
  `status` varchar(200) NOT NULL DEFAULT 'Unpaid',
  `total_charges` float NOT NULL,
  `proforma` varchar(1) NOT NULL,
  PRIMARY KEY (`sequence`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `billing_invoices` 
(`sequence`, `invoice_number`, `sub_total`, `vat_amount`, `grand_total`, `datetime`, `invoice_type`, `status`, `total_charges`, `proforma`) 
VALUES
(1, '1234', 100, 20, 120, '2016-09-01', 'Invoice', 'Unpaid', 100, '');

我有这个选择查询：

 SELECT 
     COUNT(i.sequence) as counter, 
     l.service as service, 
     SUM(i.sub_total) as sub_total, 
     SUM(i.total_charges) as total_charges, 
     SUM(i.vat_amount) as vat_amount, 
     SUM(i.grand_total) as grand_total 
 FROM 
     billing_invoices i 
 LEFT JOIN 
     adhocbills a ON a.invoice_number = i.invoice_number 
 LEFT JOIN 
     adhocbills_lineitems l ON a.sequence = l.bill_seq 
 WHERE 
     i.proforma <> 'Y' 
     AND i.invoice_type = 'Invoice' 
     AND a.status = 'Completed' 
     AND DATE(i.datetime) >= '2016-09-01' 
     AND DATE(i.datetime) <= '2016-09-30' 
 GROUP BY 
     l.service 
ORDER BY 
     grand_total DESC

我想做的事：

我想从billing_invoices当前月份中选择所有行，并将此表链接到以下内容：

adhocbills.invoice_number = billing_invoices.invoice_number
adhocbills.sequence = adhocbills_lineitems.bill_seq

并使用现有的WHERE子句。

然后我分组 adhocbills_lineitems.service

样本数据显示了这一点：

总计，1035.75但是正确的数量是多少，1017.26所以它增加了一个额外的数量18.50

额外的18.50来自adhocbills_lineitems中的行：

bill_seq = 1068
unitprice = 8.50(new column, not important)
quantity = 1(new column, not important)

bill_seq = 1068
unitprice = 10(new column, not important)
quantity = 1(new column, not important)

所以这两行都链接到 adhocbills.sequence = '1068'

似乎当多于一行的行adhocbills_lineitems与另一行在adhocbills_lineitems.service重复时

迷你工作示例：

是我得到这个结果：

counter service sub_total total_charges vat_amount grand_total 
2     Service 2   200         200           40         240 
1     Service 1   100         100           20         120

服务2的哪个正在使dunno加倍，为什么？

巴尔玛

而不是与表联接，而是与子查询联接，该子查询仅返回您关心的唯一列，而不是所有重复项。

SELECT 
     COUNT(i.sequence) as counter, 
     l.service as service, 
     SUM(i.sub_total) as sub_total, 
     SUM(i.total_charges) as total_charges, 
     SUM(i.vat_amount) as vat_amount, 
     SUM(i.grand_total) as grand_total 
     FROM billing_invoices i 
LEFT JOIN adhocbills a ON a.invoice_number = i.invoice_number 
LEFT JOIN (
    SELECT DISTINCT service, bill_seq
    FROM adhocbills_lineitems
) l ON a.sequence = l.bill_seq 
WHERE i.proforma <> 'Y' AND i.invoice_type = 'Invoice' AND a.status = 'Completed' 
    AND DATE(i.datetime) >= '2016-09-01' AND DATE(i.datetime) <= '2016-09-30' 
GROUP BY l.service 
ORDER BY grand_total DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。