这个“已付款或未付款的发票" MySQL查询是否可能? [英] Is this 'paid or unpaid invoices' MySQL query possible?
问题描述
我正在尝试生成一个查询，该查询将显示已付款"发票.我的查询无法正常工作，这意味着其中可能有错误，或者我试图实现的目标无法通过这种方式实现.
I'm trying to produce a query that will display 'paid' invoices. My query isn't working which means there's either an error in it or what I'm trying to achieve is impossible in this way.
// paid
$statement = "accounts_invoice i
INNER JOIN customer_type ct on i.invoice_customer_type = ct.customer_type_id
LEFT JOIN accounts_invoice_payment ip on i.invoice_id = ip.invoice_payment_invoice_id
WHERE invoice_posted='1'
AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.TotalPayments"
. $where
. " ORDER BY invoice_id DESC";
$invoice_details_query = mysqli_query($con,
"SELECT i.*, ct.customer_type_name,
ip.invoice_payment_amount AS TotalPayments
FROM {$statement}
LIMIT {$startpoint} , {$per_page}")
or die(mysql_error());
$where字符串在此实例中为空，您可以根据需要将其删除.您也可以删除"LIMIT"，因为它仅用于分页.
The $where string is empty in this instance, you can delete it if you wish. You can also remove the 'LIMIT' as this is purely being used for pagination.
我只是想制作一个页面来显示已付款或未付款的发票，并且从理论上讲听起来很简单，这让我发疯了！
I'm simply trying to make a page to display paid or unpaid invoices and it's driving me mad considering it sounds so simple in theory!
数据库结构；
-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 08, 2015 at 05:17 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `propsyst_atlas`
--
-- --------------------------------------------------------
--
-- Table structure for table `accounts_invoice`
--
CREATE TABLE IF NOT EXISTS `accounts_invoice` (
`invoice_id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_customer_type` tinyint(4) DEFAULT NULL,
`invoice_customer` int(11) DEFAULT NULL,
`invoice_date` date DEFAULT NULL,
`invoice_due_date` date DEFAULT NULL,
`invoice_property_id` int(11) DEFAULT NULL,
`invoice_tenancy_id` int(11) DEFAULT NULL,
`invoice_branch` int(11) DEFAULT NULL,
`invoice_payment_terms` tinyint(4) DEFAULT NULL,
`invoice_notes` text COLLATE utf8_bin,
`invoice_total_amount_exc_vat` decimal(10,2) DEFAULT NULL,
`invoice_total_vat_amount` decimal(10,2) DEFAULT NULL,
`invoice_posted` tinyint(4) DEFAULT '0',
`invoice_date_created` datetime DEFAULT NULL,
`invoice_date_updated` datetime DEFAULT NULL,
`invoice_date_posted` datetime DEFAULT NULL,
`invoice_created_by` int(11) DEFAULT NULL,
`invoice_updated_by` int(11) DEFAULT NULL,
`invoice_posted_by` int(11) DEFAULT NULL,
PRIMARY KEY (`invoice_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=87 ;
-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 08, 2015 at 05:18 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `propsyst_atlas`
--
-- --------------------------------------------------------
--
-- Table structure for table `accounts_invoice_payment`
--
CREATE TABLE IF NOT EXISTS `accounts_invoice_payment` (
`invoice_payment_id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_payment_date` date DEFAULT NULL,
`invoice_payment_amount` decimal(10,2) DEFAULT NULL,
`invoice_payment_method` tinyint(4) DEFAULT NULL,
`invoice_payment_invoice_id` int(11) DEFAULT NULL,
`invoice_payment_notes` text COLLATE utf8_bin,
`invoice_payment_date_created` datetime DEFAULT NULL,
`invoice_payment_date_updated` datetime DEFAULT NULL,
`invoice_payment_created_by` int(11) DEFAULT NULL,
`invoice_payment_updated_by` int(11) DEFAULT NULL,
PRIMARY KEY (`invoice_payment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=71 ;
-- phpMyAdmin SQL Dump
-- version 4.0.10.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 08, 2015 at 05:58 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `propsyst_atlas`
--
-- --------------------------------------------------------
--
-- Table structure for table `customer_type`
--
CREATE TABLE IF NOT EXISTS `customer_type` (
`customer_type_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_type_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`customer_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
推荐答案
您的查询实际上是
SELECT
i.*,
ip.invoice_payment_amount AS TotalPayments
FROM accounts_invoice i
LEFT JOIN accounts_invoice_payment ip
ON i.invoice_id = ip.invoice_payment_invoice_id
WHERE invoice_posted='1'
AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.TotalPayments
ORDER BY invoice_id DESC
，该查询失败，因为where语句中的ip.TotalPayments是未知列.您需要使用原始名称ip.invoice_payment_amount.别名仅在返回的集合中使用，而不在查询本身中使用.
and that query fails because the ip.TotalPayments in the where statement is an unknown column. You need to use the original name ip.invoice_payment_amount. The alias is only used in the returned set, not in the query itself.
此外，您的数据库设计也可以得到改善.首先，我建议使用InnoDB而不是MyISAM.允许您使用外键，因此表之间实际上存在关系.
Also, your database design could be improved. First I would advise to use InnoDB instead of MyISAM. That allows you to use foreign keys, so there's actually a relation between the tables.
同样，我会将WHERE子句的AND部分移到ON子句，就像这样:
Also I would move the AND part of the WHERE clause to the ON clause, like so:
SELECT
i.*,
ip.invoice_payment_amount AS TotalPayments
FROM accounts_invoice i
LEFT JOIN accounts_invoice_payment ip
ON i.invoice_id = ip.invoice_payment_invoice_id
AND (i.invoice_total_amount_exc_vat + i.invoice_total_vat_amount) = ip.invoice_payment_amount
WHERE invoice_posted='1'
ORDER BY invoice_id DESC
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id的结果包含A的每一行和B的匹配行.如果B没有与A中某行匹配的行，则B中各列的值为NULL.如果您不希望这样做，而只希望在B中具有匹配行的A行，则应使用INNER JOIN.
The result of SELECT * FROM A LEFT JOIN B ON A.id = B.a_id contains every row of A and the matching rows of B. If B does not have a matching row for a row in A, the values for the columns in B are NULL. If you do not want that, but instead only want the rows of A that have a matching row in B, you should use INNER JOIN.
这篇关于这个“已付款或未付款的发票" MySQL查询是否可能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
