三个表的结构。

预订

CREATE TABLE `booking` (
`bookingID` int(11) NOT NULL AUTO_INCREMENT,
`receipt_no` int(11) NOT NULL,
`client` varchar(32) NOT NULL,
`operator` varchar(32) NOT NULL,
`discount` int(11) NOT NULL,
`total_amount` int(64) NOT NULL,
`amount_paid` int(32) NOT NULL,
`balance` int(32) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`bookingID`)
ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=latin1

职务

CREATE TABLE `jobtitle` (
`jobtitleID` int(11) NOT NULL AUTO_INCREMENT,
`jobtitle` varchar(255) NOT NULL,
`quantity` int(11) NOT NULL,
`amount` varchar(255) NOT NULL,
`jobtypeID` int(11) NOT NULL,
`bookingID` int(11) NOT NULL,
PRIMARY KEY (`jobtitleID`)
ENGINE=InnoDB AUTO_INCREMENT=463 DEFAULT CHARSET=latin1

First_graphics_debtors

CREATE TABLE `first_graphics_debtors`
`id` int(11) NOT NULL AUTO_INCREMENT,
`receipt_no` int(11) NOT NULL,
`date_paid` date NOT NULL,
`old_balance` int(32) NOT NULL,
`debtor_amount_paid` int(32) NOT NULL,
`new_balance` int(32) NOT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

插入少量数据后

预订

+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
| bookingID | receipt_no | client   | operator | discount | total_amount | amount_paid | balance | date       |
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
|       205 |  156872940 | Osaro    | Obi      |       10 |       156380 |      135000 |    5742 | 2012-05-15 |
|       206 |  227349168 | Amaka    | Stephen  |        4 |        73250 |       70320 |       0 | 2012-05-15 |
|       207 |  155732278 | Aghahowa | Ibori    |        0 |       116836 |       15000 |  101836 | 2012-05-15 |
|       208 |  753263343 | Chaka    | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 |
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+

职务

+------------+---------------------------+----------+--------+-----------+-----------+
| jobtitleID | jobtitle                  | quantity | amount | jobtypeID | bookingID |
+------------+---------------------------+----------+--------+-----------+-----------+
|        454 | A1 Full Colour            |       10 | 4334   |         1 |       205 |
|        455 | Complementry Card         |       20 | 5652   |         2 |       205 |
|        456 | A4 Printout (graphics)B/W |       25 | 2930   |         4 |       206 |
|        457 | Scan                      |        2 | 4334   |         5 |       207 |
|        458 | A4 Full Colour            |      199 | 500    |         3 |       207 |
|        459 | ID Card                   |        2 | 4334   |         2 |       207 |
|        460 | A3 Full Colour            |       10 | 4334   |         3 |       208 |
|        461 | Flex Banner               |       20 | 2930   |         2 |       208 |
|        462 | A2 Single Colour          |      199 | 650    |         1 |       208 |
+------------+---------------------------+----------+--------+-----------+-----------+

First_graphics_debtors

+----+------------+------------+-------------+--------------------+-------------+
| id | receipt_no | date_paid  | old_balance | debtor_amount_paid | new_balance |
+----+------------+------------+-------------+--------------------+-------------+
|  7 |  156872940 | 2012-05-15 |        5742 |               5000 |         742 |
|  8 |  156872940 | 2012-05-15 |        5742 |               5742 |           0 |
|  9 |  753263343 | 2012-05-15 |      152161 |             152161 |           0 |
| 13 |  753263343 | 2012-05-15 |      152161 |              14524 |      137637 |
| 14 |  753263343 | 2012-05-15 |      152161 |               2000 |      150161 |
| 15 |  753263343 | 2012-05-15 |      152161 |               1000 |      151161 |
+----+------------+------------+-------------+--------------------+-------------+

当我运行这个查询时:

SELECT `booking`.`receipt_no`, `client`, `operator`, `discount`, `total_amount`,
       `amount_paid`, `balance`, `date`, `jobtitle`, `quantity`, `amount`,
       `date_paid`, `old_balance`, `debtor_amount_paid`, `new_balance`
  FROM (`booking`)
  JOIN `jobtitle` ON `jobtitle`.`bookingID` = `booking`.`bookingID`
  JOIN `first_graphics_debtors`
    ON `first_graphics_debtors`.`receipt_no` = `booking`.`receipt_no`
 WHERE `booking`.`receipt_no` =  '753263343'
   AND `first_graphics_debtors`.`receipt_no` =  '753263343'
 GROUP BY `jobtitle`.`quantity`

我得到这个输出:

+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| receipt_no | client | operator | discount | total_amount | amount_paid | balance | date       | jobtitle         | quantity | amount | date_paid  | old_balance | debtor_amount_paid | new_balance |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A3 Full Colour   |       10 | 4334   | 2012-05-15 |      152161 |             152161 |           0 |
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | Flex Banner      |       20 | 2930   | 2012-05-15 |      152161 |             152161 |           0 |
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A2 Single Colour |      199 | 650    | 2012-05-15 |      152161 |             152161 |           0 |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+

以下列的数据重复三次，而不是从receipt_no相关的四行中获取数据

date_paid, old_balance, debtor_amount_paid, new_balance

预期结果

+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| receipt_no | client | operator | discount | total_amount | amount_paid | balance | date       | jobtitle         | quantity | amount | date_paid  | old_balance | debtor_amount_paid | new_balance |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A3 Full Colour   |       10 | 4334   | 2012-05-15 |      152161 |             152161 |           0 |
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | Flex Banner      |       20 | 2930   | 2012-05-15 |      152161 |             14524  |     137637  |
|  753263343 | Chaka  | Stephen  |       10 |       231290 |       56000 |  152161 | 2012-05-15 | A2 Single Colour |      199 | 650    | 2012-05-15 |      152161 |             2000   |      150161 |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+

虽然预期的输出不完整，但也许您可以从 debtor_amount_paid 和 new_balance 的数据中得到图片

最佳答案

我相当确定您的问题是因为您的 GROUP BY 子句。不确定您使用它的目的是什么，但请尝试不使用它。如果您得到重复的行，请尝试使用 SELECT DISTINCT。如果您的目标是根据该列对结果进行排序，请使用 ORDER BY。

此外，在 WHERE 子句中指定两次 receipt_no 是多余的。这两个表已经由该列连接，因此您只需在一个表中对其进行过滤即可。并且反引号并不是真正必要的，除了一些特殊的异常(exception)，比如列名中的空格或逗号，或者与保留字同名的列(也可能是其他一些)。你的唯一一列看起来需要反引号是 date 列，但即使在该列中排除它们，你仍然应该没问题。我只是发现到处都是反引号会使查询变得更长且更难阅读。他们在那里不会伤害任何东西，所以如果你愿意，你可以离开他们，但就我个人而言，我不是粉丝。

我用上面提到的修改重写了你的查询，另外我给表别名以进一步缩短它。这不会提高性能或任何东西，只是让 IMO 更容易阅读:

SELECT DISTINCT
    b.receipt_no, client, operator, discount, total_amount,
    amount_paid, balance, `date`, jobtitle, quantity,
    amount, date_paid, old_balance, debtor_amount_paid, new_balance
FROM
    booking b
    INNER JOIN jobtitle jt ON jt.bookingID = b.bookingID
    INNER JOIN first_graphics_debtors fgd ON fgd.receipt_no = b.receipt_no
WHERE
    b.receipt_no = '753263343'
ORDER BY
    jt.quantity

关于mysql - 我无法让这个 mysql 连接查询产生所需的结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10652009/