MySQL Forums
Forum List  »  Oracle

MySQL equivalent Oracle Lag, Partition By - Query Help
Posted by: Si Han Tang
Date: September 02, 2009 01:08AM

I need to retrieve invoices from 3 tables.
Table Project_Detail has my project details
Table Project_Item storing items for my project. each project have 1 or more items.
Table Project_Item_Invoice will store invoices for each items in each project. 1 item may have more than 1 invoice also.

Query:
SELECT A.Project_No, B.Item_ID, B.Item_Name,
C.Invoice_ID, C.Date, C.Amount, C.Invoice_No
FROM Project_Detail A
LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No AND
B.Item_ID = C.Item_ID
WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed'
ORDER BY A.Project_No DESC, B.Item_ID, C.Invoice_ID

The selected data from database:
___________________________________________________________________________
|Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
---------------------------------------------------------------------------
|WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
|WPJ-0906-1693 | 1 | TNA0052941 | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
|WPJ-0906-1693 | 1 | TNA0052941 | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
|WPJ-0906-1693 | 2 | TNA0052942 | 1 | 2009-08-26 | 5678.90 | 0906-1150 |
|WPJ-0906-1693 | 2 | TNA0052942 | 2 | 2009-08-27 | 4356.20 | 0906-1158 |

The result I want is like this:
___________________________________________________________________________
|Project_No | Item_ID | Item_Name | Invoice_ID | Date | Amount | Invoice_No |
---------------------------------------------------------------------------
|WPJ-0906-1693 | 1 | TNA0052941 | 1 | 2009-08-25 | 1207.58 | 0907-1141 |
|___(blank)____ | _ | __(blank)___ | 2 | 2009-08-26 | 2836.70 | 0906-1153 |
|___(blank)____ | _ | __(blank)___ | 3 | 2009-08-27 | 1234.70 | 0906-1157 |
|___(blank)____ | 2 | TNA0052942 | 1 | 2009-08-23 | 5678.90 | 0906-1150 |
|___(blank)____ | _ | __(blank)___ | 2 | 2009-08-25 | 4356.20 | 0906-1158 |

In Oracle, I can select the data with decode, lag, partition by:

select
decode (t.Project_No,t.Prev_Project_No,'') Project_No,
decode (t.Item_ID,t.Prev_Item_Id,'') Item_Id,
decode (t.Item_Name,t.Prev_Item_Name,'') Item_Name,
t.Invoice_ID, t.Date, t.Amount, t.Invoice_No
from
(
SELECT
A.Project_No, B.Item_ID, B.Item_Name,
C.Invoice_ID, C.Date, C.Amount, C.Invoice_No,
lag(A.Project_No,1,0)
over (partition by A.Project_No
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Project_No,
lag(B.Item_ID,1,0)
over (partition by B.Item_ID
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Id,
lag(B.Item_Name,1,0)
over (partition by C.Item_Name
order by A.Project_No DESC, B.Item_ID, C.Item_Name )Prev_Item_Name,
FROM Project_Detail A
LEFT JOIN Project_Item B ON A.Project_No = B.Project_No
LEFT JOIN Project_Item_Invoice C ON A.Project_No = C.Project_No
AND B.Item_ID = C.Item_ID
WHERE A.Project_No LIKE 'WPJ%' AND A.Project_Status = 'Closed'
) t

How to do this in MySQL? I only know MySQL have 'if' (equal to 'decode' in Oracle) statement. How about lag and partition?

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL equivalent Oracle Lag, Partition By - Query Help
10435
September 02, 2009 01:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.