我有兩張桌子product和mastercategory
產品有列
id - pk,
title- varchar,
price - varchar,
selling_price - varchar,
description - varchar,
is_approved - bool,
is_live - bool,
is_visible - bool,
category_id - fk (foreign_key of category table)
mastercategory 有列
id - pk,
name - varchar,
is_active - bool,
is_hotcategory - bool,
我想獲得每個類別的 2 條最新is_hotcategory記錄true
我怎樣才能得到這個
我用這個試過：
select cp.id,cp.title, cp.category_id from product cp
left join mastercategory cmc on cp.category_id = cmc.id where cmc.is_hotcategory = 1 and cp.id in (
select max(cp.id) As id
from product cp
where cp.is_visible = 1 and cp.is_live= 1 and cp.is_approved=1
group by category_id
union all
select max(cp.id) As id
from product cp
left join mastercategory cmc on cp.category_id = cmc.id
where cmc.is_hotcategory = 1 and
cp.is_visible = 1 and cp.is_live= 1 and cp.is_approved=1 and
(cp.id not in (select max(cp.id)
from product cp
where cp.is_visible = 1 and cp.is_live= 1 and cp.is_approved=1 group by category_id )) group by category_id
) order by category_id asc;
我得到每個類別的最后一條記錄，然后得到每個類別的第二條最后一條記錄，然后結合兩個搜索查詢
我認為它會起作用，但如果我必須獲得每個類別的 2 條以上記錄怎么辦。有沒有其他解決方案。
uj5u.com熱心網友回復：
WITH cte AS (
select cp.id, cp.title, cp.category_id,
ROW_NUMBER() OVER (PARTITION BY cp.category_id ORDER BY cp.id DESC) rn
from product cp
join mastercategory cmc on cp.category_id = cmc.id
where cmc.is_hotcategory
and cp.is_visible
and cp.is_live
and cp.is_approved
)
SELECT *
FROM cte
WHERE rn < 3;
我假設“每個類別的最新記錄”是id相同行中最大的產品行category_id（我沒有找到類似的列created_at）。如果不是，則相應地調整框架規格。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/483432.html
上一篇：如何從adf中的全域引數傳遞表名