我无法获得此mysql联接查询以产生所需的结果 [英] I can't get this mysql join query to produce the desired result
问题描述
这三个表的结构.
预订
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
Jobtitle
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
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 |
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
Jobtitle
+------------+---------------------------+----------+--------+-----------+-----------+
| 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
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 |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
以下各列中的数据重复了三遍，而不是从与回执编号有关的四行中获取数据
Data on the columns below are repeated three times, instead of getting data from the four rows relating to the 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
Though,the expected output not complete, but maybe you can get the picture from the data on debtor_amount_paid and new_balance
推荐答案
我完全同意 Travesty3 关于表格的说法别名和反引号.
I fully agree with what Travesty3 said about table aliases and backticks.
我认为您的问题是，您正在尝试将应该"是两个单独的查询组合为一个.对于任何给定的预订，您可以具有任意数量的职位名称条目，并且完全独立于职位名称信息，您可以具有任意数量的第一图形债务人"条目.在示例收据753263343的示例数据中，您有一个预订，三个职位名称条目和4个第一图形债务人条目.
Your problem is, I think, that you are trying to combine what 'should' be two separate queries into one. For any given Booking, you can have an arbitrary number of Job Title entries, and completely independently of the Job Title information, you can have an arbitrary number of First Graphics Debtors entries. In the sample data for the sample Receipt 753263343, you have one Booking, three Job Title entries, and 4 First Graphic Debtors entries.
默认情况下(没有GROUP BY子句)，您将获得12个条目，它们对应于三组行(1 x 3 x 4)的叉积.
By default (without a GROUP BY clause), you would get 12 entries corresponding to the cross-product of the three sets of rows (1 x 3 x 4).
MySQL中的GROUP BY与大多数其他DBMS不同；标准SQL不允许您忽略GROUP BY子句中的非聚合列(并且所有列都是非聚合的).
The GROUP BY in MySQL is different from most other DBMS; Standard SQL would not allow you to omit the non-aggregate columns from the GROUP BY clause (and all the columns are non-aggregate).
我想您想要的是类似于(使用FGD作为First Graphics Debtors的缩写)的输出结果集:
What I think you're after is an output result set similar to (using FGD as an abbreviation for First Graphics Debtors):
+-------------------+---------------------+---------------+
| Data from Booking | Data from Job Title | Data from FGD |
+-------------------+---------------------+---------------+
| Booking ID 208... | Job Title ID 460... | FGD ID 9 |
| Booking ID 208... | Job Title ID 461... | FGD ID 13 |
| Booking ID 208... | Job Title ID 462... | FGD ID 14 |
| Booking ID 208... | ...?????????????... | FGD ID 15 |
+-------------------+---------------------+---------------+
问号最合理的一组值可能是一组NULL.
The most plausible set of values for the question marks is probably a set of NULLs.
不过，使用两个单独的查询可能会更好，一个查询用于获取职位名称信息，一个查询用于获取FGD信息(并且只有两个查询之一需要返回预订信息).但是，然后您必须在应用程序代码中进行一些演示工作(应该在此处进行演示工作，但这是另一种讨论).
You would probably be better off, though, using two separate queries, one to fetch the Job Title information, one to fetch the FGD information (and only one of the two needs to return the Booking information). However, you then have to do some presentation work in your application code (which is where presentation work should be done, but that's a different discussion).
因此，为使您胆怯，我建议使用两个查询.为了勇敢，请继续阅读...
So, for the faint of heart, I recommend using two queries. For the brave, read on...
更新:直到 TDQD的材料—包括测试是轻微错误.
首先，让我说一下:如果这是您要追求的，那么尽管可以做到，但要做的工作相对艰巨.其次，在DBMS中使用OLAP功能可以简化堆操作.使用MySQL(和其他一些DBMS)，您可以使用临时表保存中间结果.
First off, let me state: if that is what you're after, then it is relatively hard work to do, though it can be done. Secondly, it is a heap simpler with OLAP functions in the DBMS. With MySQL (and some other DBMS), you can use temporary tables to hold the intermediate results.
您在预订ID上加入预订"和职务".您在收据编号上加入了Booking和FGD.假设我们创建了两个中间结果集:
You join Booking and Job Title on Booking ID. You join Booking and FGD on Receipt No. Suppose we create two intermediate result sets:
SequencedJobTitles(Booking ID, Job_Seq_No)
SequencedReceipts(Receipt No, Rec_Seq_No)
其中第一个工作序号从每个预订ID的1..N开始，收据序号从每个收货号的1..M开始
where the job sequence number in the first goes from 1..N for each Booking ID, and the receipt sequence number goes from 1..M for each Receipt No.
现在我们可以写:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM SequencedJobTitles AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM SequencedReceipts AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
WHERE J.Job_Seq_No = R.Rec_Seq_No
AND B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
是的，*的使用是懒惰的；我对其进行了优化，以列出生产查询中所需的确切列列表.对于调试，它可以确保您查看所有数据，这可能会有所帮助.
Yes, the use of * is lazy; I'd refine it to list the exact list of columns that I wanted in a production query. For debugging, it ensures you see all the data, which is probably helpful.
如何创建两个中间结果集?
How to create the two intermediate result sets?
在没有OLAP功能的情况下，您可以使用以下命令生成SequencedJobTitles:
In the absence of OLAP functionality, you could generate the SequencedJobTitles with:
SELECT J1.BookingID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID <= J2.JobTitleID
GROUP BY J1.BookingID;
类似于带有以下内容的SequencedReceipts:
Similarly for the SequencedReceipts with:
SELECT R1.Receipt_No, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID <= R2.ID
GROUP BY R1.Receipt_No;
这些表达式使用带有非等联接的自联接，这是相当昂贵的.如果表很大，您可能希望仅将相关的预订ID或收据编号限制到子查询中.请注意，您可以单独测试这些子查询.对于TDQD(测试驱动查询设计)来说，这是个好主意.
These expressions use a self-join with a non-equijoin, which is moderately expensive. You'd probably want to get a restriction to just the relevant Booking ID or Receipt Numbers into the sub-queries if the tables are big. Note that you can test these sub-queries in isolation; this is a good idea for TDQD (Test Driven Query Design).
但是，我们可以简单地将这些子查询放入之前开发的主查询中:
However, we can simply slot these subqueries into the main query developed before:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM (SELECT J1.BookingID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID <= J2.JobTitleID
GROUP BY J1.BookingID
) AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM (SELECT R1.Receipt_No, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID <= R2.ID
GROUP BY R1.Receipt_No
) AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
ON J.Job_Seq_No = R.Rec_Seq_No
WHERE B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
警告:未经测试的SQL！
更新:此警告没有被闲置在那儿.无论我多么努力，我都不相信自己会在第一时间准确地编写复杂的查询.而且，我并没有尝试产生我之前显示的大纲结果(我一直使用两个单独的查询，而是使用表示工具的方法来组合数据)，所以我认为错误是不可避免的.
Update: This warning was not put there idly. I don't trust myself to write complex queries accurately first time, no matter how hard I try. And I'd not tried to produce the outline result I showed before (I've always used the two separate queries and combine the data in the presentation tool approach instead), so mistakes were, I suppose, inevitable.
真实TDQD显示序列查询是关闭的，但不正确.尽管我将<=条件更改为>=条件，但这比实际更具修饰性.正确的查询如下:
Real TDQD shows that the sequence queries are close, but not correct. Although I changed the <= conditions into >= conditions, that is more cosmetic than substantive. The correct queries are as follows:
SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID;
SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID;
这两个结果可以分别与相应的表联接起来，但是联接条件与我最初写的不同:
These two results can each be joined back with the corresponding table, but the join condition is different from what I originally wrote:
SELECT J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
ORDER BY J4.BookingID, J3.Job_Seq_No;
SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
ORDER BY R4.Receipt_No, R3.Rec_Seq_No;
然后您可以运行以下查询:
And then you can run this query:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON B.Receipt_No = R.Receipt_No
WHERE B.Receipt_No = 753263343
AND J.Job_Seq_No = R.Rec_Seq_No
ORDER BY B.BookingID, NVL(J.Job_Seq_No, R.Rec_Seq_No);
不幸的是，它仅在样本数据上产生三行；它省略了4个收据编号中的最后一个.这就是为什么我要进行TDQD；当我捷径时，我发现有问题.
Unfortunately, it produces just three rows on the sample data; it omits the last of the 4 receipt numbers. This is why I do TDQD; when I take short cuts, I find there are problems.
最后一个查询的结构必须使J和R子查询使用FULL OUTER JOIN(通常)进行连接，因此，它们需要公用的收据号或预订ID.我选择将收据编号添加到JobTitle信息中；在将预订ID"添加到第一图形债务人"信息中后，它将可以正常工作；对称性建议同时做这两项，但这也可能被认为是过大的杀伤力.
The structure of the last query needs to be such that the J and R sub-queries are joined using a FULL OUTER JOIN (in general), and therefore they need either the Receipt Number or Booking ID in common. I chose to add the Receipt Number to the JobTitle information; it would work fine with the Booking ID added to the First Graphics Debtors information; symmetry suggests doing both, but that might also be regarded as overkill.
SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
ORDER BY J4.BookingID, J3.Job_Seq_No;
然后，将两个顺序列表与FULL OUTER JOIN组合在一起:
Then, combining the two sequenced lists with a FULL OUTER JOIN:
SELECT *
FROM (SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
) AS J
FULL OUTER JOIN
(SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON J.Receipt_No = R.Receipt_No AND J.Job_Seq_No = R.Rec_Seq_No
ORDER BY NVL(J.Receipt_No, R.Receipt_No), NVL(J.Job_Seq_No, R.Rec_Seq_No);
最终样本查询
最后将其嵌入到主查询中即可得到(非常详细的版本)所需的结果:
Final Sample Query
And finally embedding that into the main query gives (a very verbose version of) the desired result:
SELECT B.*, JR.*
FROM Booking AS B
JOIN (SELECT NVL(J.Receipt_No, R.Receipt_No) AS Rec_No,
NVL(J.Job_Seq_No, R.Rec_Seq_No) AS Seq_No,
J.*, R.*
FROM (SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
) AS J
FULL OUTER JOIN
(SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON J.Receipt_No = R.Receipt_No AND J.Job_Seq_No = R.Rec_Seq_No
) AS JR
ON B.Receipt_No = JR.Rec_No
WHERE B.Receipt_No = 753263343
ORDER BY B.BookingID, Seq_No;
具有更多这样的选择性列列表:
With a very much more selective column list like this:
SELECT B.BookingID AS Booking,
B.Receipt_No AS Receipt,
B.Client AS Client,
JR.JobTitleID AS Title
JR.JobTitle AS JobTitle,
JR.Old_Balance AS Old_Balance,
JR.New_Balance AS New_Balance
FROM Booking AS B
...
最终样本输出
我从样本数据中获得的结果数据是:
Final Sample Output
The result data I obtained from the sample data was:
booking receipt client title jobtitle old_balance new_balance
208 753263343 Chaka 460 A3 Full Colour 152161 0
208 753263343 Chaka 461 Flex Banner 152161 137637
208 753263343 Chaka 462 A2 Single Colour 152161 150161
208 753263343 Chaka 152161 151161
唯一的残留问题是MySQL是否支持FULL OUTER JOIN表示法.如果没有，您可以在此样本数据上使用右外部联接"(职务"信息中的行少于FGD信息中的行).
The only residual issue is whether MySQL support the FULL OUTER JOIN notation. If not, you can use a RIGHT OUTER JOIN on this sample data (where there are fewer rows in the Job Title information than in the FGD information).
更新:这是基于现在已知的错误查询.必须在此处进行类似的更改，以使其按要求工作.
Update: This is based on the now known to be erroneous queries. Analagous changes must be made here to get it working as required.
借助OLAP函数，我认为您可以创建如下排序的结果集:
With OLAP functions, I think you'd be able to create the sequenced result sets like this:
SELECT BookingID,
ROW_NUMBER() OVER (PARTITION BY BookingID ORDER BY JobTitleID) AS Job_Seq_No
FROM JobTitle;
SELECT Receipt_No,
ROW_NUMBER() OVER (PARTITION BY Receipt_No ORDER BY ID) AS Rec_Seq_No
FROM First_Graphics_Debtors;
但是，我对SQL的这一部分不太自信.但是，生成的大查询更简单:
However, I'm much less confident of this part of the SQL. However, the resulting big query is simpler:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM (SELECT BookingID,
ROW_NUMBER()
OVER (PARTITION BY BookingID ORDER BY JobTitleID)
AS Job_Seq_No
FROM JobTitle
) AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM (SELECT Receipt_No,
ROW_NUMBER() OVER (PARTITION BY Receipt_No ORDER BY ID)
AS Rec_Seq_No
FROM First_Graphics_Debtors
) AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
WHERE J.Job_Seq_No = R.Rec_Seq_No
AND B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
警告:更多未经测试的SQL！
Caution: more untested SQL!
这篇关于我无法获得此mysql联接查询以产生所需的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
