Join結合にて同一商品の場合、何個目のデータなのかを明確にしたい。

Question

明細行のデータ突合のため、同一商品の場合は何個目のデータなのかを明確にしたい。

買い物データのヘッダー
テーブル purchases

id	purchased_on
1	2019/07/01
2	2019/07/02
3	2019/07/03

買い物データの明細
テーブル purchase_details

id	purchase_id	item_cd	price
1	1	001	100
2	1	001	100
3	1	002	200
4	2	002	200
5	3	003	300

Join

select
    a.id,
    a.purchased_on,
    b.id purchase_detail_id,
    b.item_cd,
    b.price
from purchases a
join purchase_details b
   on a.id = b.purchase_id

結果テーブル
|id|purchased_on|purchase_detail_id|item_cd|price|
|:--:|:--|:--|:--|
|1|2019/07/01|1|001|100|
|1|2019/07/01|2|001|100|
|1|2019/07/01|3|002|200|
|2|2019/07/02|4|002|200|
|3|2019/07/03|5|003|300|

7/1に商品001を２個、商品002を１個買った。
7/2に商品002を１個買った。
7/1に商品003を１個買った。

という内容ですが、以下のようなデータのとり方は可能でしょうか？

|id|purchased_on|purchase_detail_id|item_cd|price|個目|
|:--:|:--|:--|:--|:--|
|1|2019/07/01|1|001|100|1|
|1|2019/07/01|2|001|100|2|
|1|2019/07/01|3|002|200|1|
|2|2019/07/02|4|002|200|1|
|3|2019/07/03|5|003|300|1|

※DBはMySQLを使用していおります。

Answer 1

select
    a.id,
    a.purchased_on,
    b.purchase_detail_id,
    b.item_cd,
    b.price
    b.`個目`
from purchases a
     inner join (
       select purchase_id, item_cd, price
            , max(id) as purchase_detail_id, count(*) as `個目`
       from purchase_details
       group by purchase_id, item_cd, price
     ) b
     on a.id = b.purchase_id

Answer 2

create table purchases(id int primary key,purchased_on date);
insert into purchases values
(1,'2019/07/01'),
(2,'2019/07/02'),
(3,'2019/07/03');
create table purchase_details(id int primary key,purchase_id int,item_cd varchar(10),price int);
insert into purchase_details values
(1,1,'001',100),
(2,1,'001',100),
(3,1,'002',200),
(4,2,'002',200),
(5,3,'003',300);

select t2.*,t1.*,
(select count(*)+1 from purchase_details 
where item_cd=t1.item_cd and purchase_id=t1.purchase_id and id<t1.id) as 個目
from purchase_details as t1
inner join purchases as t2 on t1.purchase_id=t2.id

※select文は手抜き、必要なカラム名は列記したほうがよい

Answer 3

Window関数ROW_NUMBER() OVER (PARTITION BY が使えるなら簡単でしょう。

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで明示したり、バージョンも明記した方が適切なコメントが付き易いです。SQLの観点から Oracle Database, PostgreSQL, MySQL の特徴を整理しよう！

SQLの質問にはそれを実行できるCREATE TABLE文、今のデータを示すINSERT文も提示できると解決が早いです。