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

问题描述：

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

哪一行与se为什么第2服务器翻倍？

答

不是加入表格，而是加入一个子查询，它只返回您关心的唯一列，而不是所有重复项。

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

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

相关推荐