SELECT ym,vendor,p1,concat(if(p1=p2,'還是',''),p2) FROM (
SELECT ym,vendor,
	SUBSTRING_INDEX(GROUP_CONCAT(product),",",1) AS p1, 
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(product),",",2),",",-1) AS p2 
	FROM (
		SELECT *,DATE_FORMAT(exdate,"%Y%m") AS ym FROM `tab`  GROUP BY exdate,vendor,product ORDER BY exdate
	) AS t group BY ym,vendor
) AS c

這次認真了一下下。

保留錯的版本

check

想了一下groupby好像就不太需要用count...

select VENDOR,MONTH, RNK1 產品1,NVL2(RNK2,RNK2,'還是'||RNK1) 產品2 from (
  select 
        rank() over(partition by vendor,trunc(exdate,'mm') order by exdate) rnk,
        product,
        vendor,
        to_char(exdate,'YYYY-MM') month
  from (
  select vendor, min(exdate) exdate, product from tab group by vendor, product,trunc(exdate,'mm')
  ) t
  order by vendor,trunc(exdate,'mm')
  ) pivot
(
    max(product) for rnk in (1 "RNK1",2 "RNK2") 
)
order by 1,2;

create table tab (
   vendor varchar(20)
 , exdate date
 , product varchar(20)
);

insert into tab values
('好吃雞腿', '2021-08-01',  '炸雞腿'),
('好吃雞腿', '2021-08-02',  '炸雞腿'),
('好吃雞腿', '2021-08-03',  '滷雞腿'),
('好吃雞腿', '2021-08-04',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-01',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-02',  '蜜汁雞腿'),
('好吃雞腿', '2021-09-03',  '蜜汁雞腿'),
('黯然排骨', '2021-08-01',  '排骨酥'),
('黯然排骨', '2021-08-02',  '椒鹽排骨'),
('黯然排骨', '2021-08-03',  '紅燒排骨');

SELECT R.vendor,R.YearMonth,R.product,R.product2
FROM (
SELECT M.vendor,M.YearMonth,M.product,
ROW_NUMBER() OVER (PARTITION BY M.vendor,M.YearMonth ORDER BY M.exdateMIN) AS NO,
LEAD(M.product,1,CONCAT('還是' , M.product)) OVER (PARTITION BY M.vendor,M.YearMonth ORDER BY M.exdateMIN) AS product2
FROM (
SELECT vendor,LEFT(exdate,7) AS YearMonth,product,MIN(exdate) AS exdateMIN
FROM tab
GROUP BY vendor,LEFT(exdate,7),product) M
ORDER BY M.vendor,M.exdateMIN) R
WHERE R.NO = 1

Demo