ฉันมีตารางที่มีธุรกรรม:
Transactions
------------
id | account | type | date_time | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
2 | 003 | 'R' | '2012-01-02 12:53:10' | 1500
3 | 003 | 'A' | '2012-01-03 13:10:01' | -1500
4 | 002 | 'R' | '2012-01-03 17:56:00' | 2000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
6 | 002 | 'A' | '2012-01-04 13:23:01' | -2000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
8 | 003 | 'R' | '2012-01-05 12:12:00' | 1250
9 | 003 | 'A' | '2012-01-06 17:24:01' | -1250
และฉันต้องการเลือกประเภทบางประเภททั้งหมด ('R') แต่ไม่ใช่ประเภทที่ทันที (ตามลำดับช่อง date_time) มีธุรกรรมประเภทอื่น ('A') สำหรับบัญชีเดียวกันที่ยื่น...
ดังนั้น แบบสอบถามควรแสดงแถวต่อไปนี้ ตามตัวอย่างก่อนหน้านี้:
id | account |type | date | amount
----------------------------------------------------
1 | 001 | 'R' | '2012-01-01 10:01:00' | 1000
5 | 001 | 'R' | '2012-01-04 12:30:01' | 1000
7 | 003 | 'R' | '2012-01-04 15:13:10' | 3000
(อย่างที่คุณเห็น แถวที่ 2 จะไม่แสดงเนื่องจากแถวที่ 3 'ยกเลิก' มัน... นอกจากนี้แถวที่ 4 ก็ 'ยกเลิก' โดยแถวที่ 6' เช่นกัน แถวที่ 7 จะปรากฏขึ้น (แม้ว่าบัญชี 003 จะเป็นของแถวที่ยกเลิก #2 ครั้งนี้ในแถวที่ 7 จะไม่ถูกยกเลิกโดยแถว 'A' ใด ๆ ) และแถวที่ 8 จะไม่ปรากฏขึ้น (มันเกินไปสำหรับบัญชี 003 เนื่องจากตอนนี้อันนี้ถูกยกเลิกด้วย 9 ซึ่งไม่ได้ยกเลิก 7 เช่นกัน เพียงก่อนหน้านี้ หนึ่ง: 8...
ฉันได้ลองเข้าร่วมแล้ว แบบสอบถามย่อยใน Where clauses แต่ฉันไม่แน่ใจจริงๆ ว่าจะต้องค้นหาอย่างไร...
สิ่งที่ฉันได้ลอง:
กำลังพยายามเข้าร่วม:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
INNER JOIN ( SELECT t.type AS type, t.acct AS acct, t.date_time AS date_time
FROM Transactions t
WHERE t.date_time > trans.date_time
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
WHERE trans.type IN ('R')
AND nextTrans.type NOT IN ('A')
ORDER BY DATE(trans.date_time) ASC
สิ่งนี้ทำให้เกิดข้อผิดพลาด เนื่องจากฉันไม่สามารถแนะนำค่าภายนอกให้กับ JOIN ใน MySQL ได้
กำลังลองใช้แบบสอบถามย่อยโดยที่:
SELECT trans.type as type,
trans.amount as amount,
trans.date_time as dt,
trans.account as acct,
FROM Transactions trans
WHERE trans.type IN ('R')
AND trans.datetime <
( SELECT t.date_time AS date_time
FROM Transactions t
WHERE t.account = trans.account
ORDER BY t.date_time DESC
) AS nextTrans
ON nextTrans.acct = trans.acct
ORDER BY DATE(trans.date_time) ASC
นี่เป็นสิ่งที่ผิด ฉันสามารถแนะนำค่าภายนอกให้กับ WHERE ใน MySQL ได้ แต่ฉันไม่สามารถหาวิธีกรองสิ่งที่ฉันต้องการได้อย่างถูกต้อง...
การแก้ไขที่สำคัญ:
ฉันจัดการเพื่อให้ได้โซลูชัน แต่ตอนนี้จำเป็นต้องมีการปรับให้เหมาะสมอย่างจริงจัง นี่คือ:
SELECT *
FROM (SELECT t1.*, tFlagged.id AS cancId, tFlagged.type AS cancFlag
FROM transactions t1
LEFT JOIN (SELECT t2.*
FROM transactions t2
ORDER BY t2.date_time ASC ) tFlagged
ON (t1.account=tFlagged.account
AND
t1.date_time < tFlagged.date_time)
WHERE t1.type = 'R'
GROUP BY t1.id) tCanc
WHERE tCanc.cancFlag IS NULL
OR tCanc.cancFlag <> 'A'
ฉันเข้าร่วมโต๊ะด้วยตัวเอง แค่พิจารณาบัญชีเดียวกันและ date_time ที่ดี การเข้าร่วมจะเรียงลำดับตาม date_time การจัดกลุ่มตามรหัส ฉันจัดการเพื่อให้ได้เฉพาะผลลัพธ์แรกของการเข้าร่วม ซึ่งเป็นธุรกรรมถัดไปสำหรับบัญชีเดียวกัน
จากนั้นในการเลือกภายนอก ฉันจะกรองรายการที่มี 'A' ออก เนื่องจากนั่นหมายความว่าธุรกรรมถัดไปเป็นการยกเลิกอย่างมีประสิทธิภาพ กล่าวคือ หากไม่มีรายการถัดไปสำหรับบัญชีเดียวกัน หรือหากรายการถัดไปเป็น 'R' ก็จะไม่ถูกยกเลิกและจะต้องแสดงในผลลัพธ์...
ฉันได้รับสิ่งนี้:
+----+---------+------+---------------------+--------+--------+----------+
| id | account | type | date_time | amount | cancId | cancFlag |
+----+---------+------+---------------------+--------+--------+----------+
| 1 | 001 | R | 2012-01-01 10:01:00 | 1000 | 5 | R |
| 5 | 001 | R | 2012-01-04 12:30:01 | 1000 | NULL | NULL |
| 7 | 003 | R | 2012-01-04 15:13:10 | 3000 | 8 | R |
+----+---------+------+---------------------+--------+--------+----------+
มันเชื่อมโยงแต่ละธุรกรรมกับรายการถัดไปในเวลาสำหรับบัญชีเดียวกัน จากนั้นกรองรายการที่ถูกยกเลิกออก... สำเร็จ!!
อย่างที่ฉันบอกไป ปัญหาตอนนี้คือการเพิ่มประสิทธิภาพ ข้อมูลจริงของฉันมีแถวจำนวนมาก (เนื่องจากคาดว่าจะมีตารางที่เก็บธุรกรรมในช่วงเวลา) และสำหรับตารางประมาณ 10,000 แถวในขณะนี้ ฉันได้ผลลัพธ์เชิงบวกกับข้อความค้นหานี้ใน 1 นาที 44 วินาที ฉันคิดว่านั่นคือเรื่องของการรวม... (สำหรับผู้ที่รู้โปรโตคอลในนี้ ฉันควรทำอย่างไร ตั้งคำถามใหม่ที่นี่ และโพสต์สิ่งนี้เพื่อเป็นวิธีแก้ปัญหาสำหรับคำถามนี้ หรือเพียงแค่รอคำตอบเพิ่มเติมที่นี่?)