我的企业几年前用php编写了一个简单的发票系统，其中包含MySQL数据库。当我写它的时候，我知道足够危险。虽然它确实有效，但很多代码效率非常低，速度慢且构思不佳，所以我正在重写其中的一些代码，希望能够修复它。
有两个表：customers和transactions。当需要创建发票时，有两个查询，第二个不幸的是在 while 循环中。这里有一些简化的伪代码来展示我正在做的事情：
// Get list of customers who owe something
SELECT name, address
FROM customers
WHERE
(SELECT COUNT(*) FROM transactions
WHERE transactions.customer_id = customers.id
AND owed > 0)
> 0
ORDER BY address
//loop through that result and query the transactions table to get a
//list of charges for each customer. Like:
while ($row = customer_array()) {
echo name_and_address;
SELECT * FROM transactions WHERE id = $row['customer_id']
AND owed = TRUE ORDER BY date
while ($row = transactions_array()) {
echo each_transaction_row;
}
所以很明显，嵌套循环、子查询和循环中的查询是坏的、更糟的和缓慢的。我尝试过联接，但似乎无法弄清楚如何使它们在这种情况下正常工作。做这样的事情的正确方法是什么？可以通过单个查询完成吗？
表结构：
CREATE TABLE `customer_data` (
`account_id` int(6) NOT NULL AUTO_INCREMENT,
`service_address` varchar(255) DEFAULT NULL,
`service_zipcode` int(5) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`billing_address1` varchar(255) DEFAULT NULL,
`billing_address2` varchar(255) DEFAULT NULL,
`billing_city` varchar(255) DEFAULT NULL,
`billing_state` varchar(2) DEFAULT NULL,
`billing_zipcode` varchar(20) DEFAULT NULL,
`phone1` varchar(100) DEFAULT NULL,
`phone2` varchar(100) DEFAULT NULL,
`quoted_price` int(6) DEFAULT NULL,
`current_price` int(6) DEFAULT NULL,
`original_interval` varchar(10) DEFAULT NULL,
`current_interval` varchar(7) DEFAULT NULL,
`temp_interval` int(5) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`origin_date` varchar(20) DEFAULT NULL,
`remarks` text,
`crew_remarks` text NOT NULL,
`customer_type` varchar(10) DEFAULT NULL,
`perm_crew_assign` int(5) NOT NULL DEFAULT '0',
`temp_crew_assign` int(5) NOT NULL,
`date_last_service` date DEFAULT NULL,
`next_scheduled_date` date DEFAULT NULL,
`excluded_days` varchar(255) NOT NULL,
`special_instructions` text NOT NULL,
`inactive` tinyint(1) NOT NULL DEFAULT '0',
`location` varchar(255) NOT NULL,
`sent_letter` date NOT NULL,
`date_added` datetime NOT NULL,
`email_notify` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1687 DEFAULT CHARSET=utf8
CREATE TABLE `transactions` (
`transaction_id` int(10) NOT NULL AUTO_INCREMENT,
`account_id` int(6) NOT NULL,
`crew_id` int(3) NOT NULL,
`date_performed` date NOT NULL,
`date_recorded` datetime NOT NULL,
`price` int(6) NOT NULL,
`amount_paid` int(6) NOT NULL,
`description` varchar(255) NOT NULL,
`user` varchar(255) NOT NULL,
`status` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=69233 DEFAULT CHARSET=latin1
您可以使用一个查询和一个循环来执行此操作。您可以联接两个表并对结果进行排序。然后，当名称更改时，您就知道您正在开始一个新客户。
SELECT c.name, c.address, t.*
FROM customers AS c
JOIN transactions AS t ON (t.customer_id = c.customers.id)
WHERE c.owed > 0
ORDER BY `c`.`name`
// Data looks like this
// | customer.name | customer.address | transaction.* |
$prevName = null;
$prevCustTransList = [];
$prevCustTransTotalOwed = 0;
while ($row = result_array()) { // pseudo code
if ( $row['c.name'] != $prevName ) {
if ( $prevName != null && $prevCustTransTotalOwed > 0 ) {
echo $prevName; // pseudo code
foreach($prevCustTransList as $t) {
echo $t; // pseudo code
}
}
$prevName = $row['c.name'];
$prevCustTransTotalOwed = 0;
$prevCustTransList = [];
}
// Keep the list of transactions to print out later.
$prevCustTransList[] = transaction data; // pseudo code
// Keep a running total of the amount owed
// Replace with actual calculation
$prevCustTransTotalOwed += transaction.price;
}
是的，您可以在一个查询中执行此操作，按account_id排序。在您的"交易"表中，account_id应该被索引，因为您将使用它来加入"customer_data"。
$st_date = '2016-01-01';
$end_date = '2016-02-01';
//select sql
SELECT c.name,c.account_id, ts.*
FROM customer_data c
INNER JOIN transactions ts ON (c.account_id = ts.account_id)
WHERE ts.price > 0 and ts.date_performed between $st_date and $end_data
$current_account_id = null;
while ($row = $results) {
if ($current_account_id != $row['account_id']) {
//print customer name, details, etc..
}
//print the transactions detail
echo $results['transaction_id']; //etc...
//keep track of the current customer
$current_account_id = $row['account_id']
}