Sql 查询仅使用特定的 c_id 检索发送者的数据，其中发送者和接收者的数据在同一个表中 [英] Sql query for retrieve data of senders only with a specific c_id where senders and receivers data in same table

问题描述

t 是一个表格，其中包含快递员的发送和接收日期以及他们各自的客户，客户 ID 是 c_id，快递员 ID 是 cr_id，打磨或接收日期是 dt.

t is a table which contains sending and receiving date of courier along with their respective customer, Customer ID is c_id, Courier Id is cr_id , sanding or receiving date is dt.

create table t(c_id int,cr_id int,dt date);

insert into t values
(1,56,'2020-05-15'),(56,56,'2020-10-17'),
(1,8,'2020-05-17'),(56,8,'2020-12-17'),
(123,78,'2020-01-17'),(1,78,'2020-12-18');

如果任何订单成功下单，我将首先插入发送快递的客户，然后插入接收快递的客户.

I am inserting customer first which sends a courier and below that which receives a courier if any order placed successfully.

这里我只想检索具有特定 c_id 的发件人数据?

Here I want to retrieve data of senders only with a specific c_id?

推荐答案

你需要ROW_NUMBER窗口函数如下:

select * from
(select t.*, row_number() over (partition by cr_id) as rn
  from t)
where rn = 1

这篇关于Sql 查询仅使用特定的 c_id 检索发送者的数据，其中发送者和接收者的数据在同一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！