选择id有重复项的所有数据

Question

我试图从客户购物超过1的数据库中获取所有数据

当前代码:

select * from sales s 
join closest_dealerships cd 
on s.customer_id=cd.customer_id 
order by s.customer_id;

我试过了:

select *,
case
when s.customer_id in (select s.customer_id from sales s 
group by (s.customer_id)
having count(s.customer_id) >1
order by s.customer_id)
end 
from sales s 
join closest_dealerships cd 
on s.customer_id=cd.customer_id 
order by s.customer_id;

当前输出:

| customer_id | product_id | column1 | columnx
|      1      |      8     |         |
|      2      |      7     |         |
|      2      |      1     |         |
|      3      |     12     |         |
|      4      |     23     |         |

我希望的输出:

| customer_id | product_id | column1 | columnx
|      2      |      7     |         |
|      2      |      1     |         |
|      4      |     23     |         |
|      4      |      9     |         |
|      4      |     12     |         |

Answer 1

您想要显示连接的行，但只有在客户有不止一行的情况下。因此，计算每个客户的行数，并只保留计数大于1的数据。

select *
from
(
select s.*, c.*, count(*) over (partition by s.customer_id) as cnt
from sales s 
join closest_dealerships cd using (customer_id)
) counted
where cnt > 1
order by customer_id;