我有一張保險單表，我正在嘗試查找所有具有完全相同資訊的保單，但郵政編碼不同（最后一列）。
如果有人對如何找到它有任何建議，將不勝感激
Policy | Expiry | State| Region | InsuredValue | Construction | BusinessType| Zip
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10002
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10011
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10002
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10011
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10011
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10011
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10002
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 10005
在這個例子中，我希望它回傳：
Policy Expiry State Region InsuredValue Construction BusinessType dupCount
100242 | 2-Jan-21 | NY | East | 1,617,630 | Frame | Retail | 3
dupCount 為 3，因為此資料有 3 個不同的郵政編碼
uj5u.com熱心網友回復：
只需按所有列（Zip 除外）分組并按 Zip 計數。
SELECT policy,
expiry,
state,
region,
insuredvalue,
construction,
businesstype,
COUNT(distinct zip) AS dupcount
FROM my_table
GROUP BY policy,
expiry,
state,
region,
insuredvalue,
construction,
businesstype
HAVING COUNT(distinct zip) > 1;
uj5u.com熱心網友回復：
你可以試試下面的。
select t.Policy, t.Expiry, t.State, t.Region, t.InsuredValue, t.Construction, t.BusinessType, count(1)
from (
select distinct Policy,
Expiry, State, Region,
InsuredValue, Construction,
BusinessType, Zip from tablename) t
group by t.Policy, t.Expiry, t.State, t.Region, t.InsuredValue, t.Construction, t.BusinessType;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/471301.html