mysql - 如何在 MYSQL 中连接具有相同实例的相同表？

Question

假设我在 MYSQL 中有以下表

Sailors (sid,sname,rating,age) where sid-> Sailor's id, 

Reserves (sid,bid,day) where bid-> boat's id

<小时/>

我的查询是:-

Find names of the sailors who have sailed two different boats on the same day ?

首先，我尝试连接两个保留表副本并添加一个条件，即出价必须不同。

SELECT * FROM reserves R1,reserves R2 WHERE R1.day = R2.day AND R1.bid <> R2.bid;

我得到这样的输出:-

现在，查询需要水手的姓名，因此我必须将水 watch 与结果表连接起来。

<小时/>

现在，我如何应用连接(条件是什么)操作来获得所需的结果？

Answer 1

drop table if exists sailors;
drop table if exists reserves;

create table sailors(sid int, sname varchar(3));
create table reserves(bid int,sid int, dt date);

insert into sailors values
(1,'abc'),(2,'def'),(3,'ghi');
insert into reserves values
(100,1,'2017-01-01'),(200,1,'2017-01-01'),
(100,1,'2017-01-02'),(100,1,'2017-01-02'),
(100,2,'2017-01-01'),(100,2,'2017-01-01'),(300,2,'2017-01-01'),
(100,2,'2017-01-03'),(200,2,'2017-01-04')
;

select s.sid,t.sname,s.dt
        ,group_concat(s.bid order by s.bid) boats
from
(
select r.sid,r.dt,r.bid,
        if(concat(r.sid,r.dt,r.bid) <> @p, @rn:=1,@rn:=@rn+1) rn,       
         @p:=concat(r.sid,r.dt,r.bid) p
from   (select @rn:=0,@p:='') rn,reserves r
order by r.sid,r.dt,r.bid
) s
join sailors t on t.sid = s.sid
where s.rn = 1
group by s.sid,s.dt
having instr(boats,',') > 0

+------+-------+------------+---------+
| sid  | sname | dt         | boats   |
+------+-------+------------+---------+
|    1 | abc   | 2017-01-01 | 100,200 |
|    2 | def   | 2017-01-01 | 100,300 |
+------+-------+------------+---------+
2 rows in set (0.00 sec)