我有一張這樣的桌子
我想選擇多次出現的 Itemid 具有不同的 Rate with group by Masterid
輸出應該是這樣的：
uj5u.com熱心網友回復：
您可以嘗試以下方法：
SELECT masterid, detailid, itemid, rate FROM mytable
WHERE (masterid, detailid, rate) IN
(
SELECT masterid, detailid, rate FROM mytable t
JOIN mytable o ON o.masterid = t.masterid
AND o.detailid = t.detailid AND o.rate <> t.rate
GROUP BY t.masterid, t.detailid, t.rate
HAVING COUNT(*) >= 2
)
子查詢中的內部連接確保僅出現具有不相等計數器部分的行。或者，您可以向外部查詢添加另一個子查詢條件：
AND EXISTS
(
SELECT * FROM mytable o
WHERE o.masterid = t.masterid AND o.detailid = t.detailid AND o.rate <> t.rate
)
uj5u.com熱心網友回復：
我相信您正在尋找如下查詢
select t1.* from t t1
join
(
select masterid,itemid
from t
group by masterid,itemid
having count(distinct rate )>1
)t2
on t1.masterid=t2.masterid and t1.itemid=t2.itemid
order by masterid,detailid
這是一個有效的資料庫小提琴
uj5u.com熱心網友回復：
試試下面的代碼：
Select masterid, detailid, rate, count(*) as count from Mytable
group by masterid, detailid, rate
having count(*) > 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/504689.html