1.拼多多面试题sql（来自牛客）

自连接的思路 t1 join t2 on t1.ip=t2.ip where t1.uid != t2.uid group by t1.uid, t2.uid having count(*)>=3，但是这样自连接不会出现id1，id2和id2，id1这样反序的重复情况，所以我就改成log1.uid<log2.uid，这样输出就是 a b，如果是>那输出就是ba，否则会有ab ba两条。

select log1.uid,log2.uid

from ip_log log1 inner join ip_log log2 on log1.ip=log2.ip  and log1.uid<log2.uid

group by log1.uid,log2.uid

having count(*)>=3

建一个表看看，方便理解：

truncate table ip_log;

insert into ip_log values('a' , '124' , '2019-08-07');

insert into ip_log values('b' , '124' , '2019-08-07');

insert into ip_log values('c' , '124' , '2019-08-07');

insert into ip_log values('a' , '174' , '2019-08-07');

insert into ip_log values('b' , '174' , '2019-08-07');

insert into ip_log values('a' , '194' , '2019-08-07');

insert into ip_log values('b' , '124' , '2019-08-07');

2.

order（ord_id,mall_id,goods_id,sale_number,amount）,求每个商店里的商品价格的中位数。

（用到了开窗函数和join，我大体方向正确，但是考虑不周全，面试官有引导纠正）

我觉得下面这类似的求的不是特别好

应该用 rank over 开窗排序

select *

from

(SELECT id,name, Sales,rank() over (partition by id order by Sales) rank_

FROM median_sale)a

inner join

(select id,count(*) as cnt from median_sale group by 1 )b

on a.id=b.id

where a.rank_=(b.cnt+1) div 2

增加考虑奇偶，如果是偶数保留中间两个，最后求平均

select a.id,avg(Sales) as median

from

(SELECT id,name, Sales,rank() over (partition by id order by Sales) rank_

FROM median_sale)a

inner join

(select id,count(*) as cnt from median_sale group by 1 )b

on a.id=b.id

where

case when b.cnt%2 !=0 then a.rank_=((b.cnt+1) div 2)

else (a.rank_= (b.cnt) div 2 or (a.rank_= b.cnt div 2 +1) )end

group by 1

中间步骤是这样的

WHERE tab.sales_rank=(select (count(*)+1) div 2 from total_sales);

SELECT * from

(

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank

FROM Total_Sales a1, Total_Sales a2

WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

GROUP BY a1.Name, a1.Sales

ORDER BY a1.Sales DESC, a1.Name DESC

) as tab WHERE tab.sales_rank=(select (count(*)+1) div 2 from total_sales);

可以查找到中间的项，count(*)+1，这个1必须要添加，总项数假如为奇数7，则count(*)+1 / 2 = 4，假如是6则为3，都是满足的，如果不+1的话，对奇数项则不满足，如7，count(*) /2 = 3，而我们要查找的则是位于4的位置

滴滴sql一

image.png

两个表如上，查出每个司机每天接的第一单(name,o_id)；

方法一

image.png

方法二

image.png

存在问题：没有接单的用户不存在了

可以改进使用左连接和右连接

作者：微斯人_吾谁与归

链接：https://www.jianshu.com/p/eb4d8404ec12

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

insert into t1 values('1','c罗','33');

insert into t1 values('2','梅西','31');

insert into t1 values('3','内马尔','29');

insert into t2 values('1','3','1');

insert into t2 values('1','5','2');

insert into t2 values('2','0','1');

insert into t2 values('2','8','2');

很多同学在学习 Mysql 表关联的时候弄不清ON与WHERE的区别，不知道条件应该写在ON里面还是WHERE里面，作者在工作的时候也入过坑，总觉得条件写在哪里查询结果都是一样的，最后出错坏了事，差点惹了大祸。所以今天简单易懂的总结一下他们的区别，大家共同学习。

准备工作

我们先准备两个表，并造一些数据：

-- t1createtablet1(idbigintdefault'0'notnullcomment'主键id',namechar(100)default''notnullcomment'姓名',ageintdefault'0'notnull);-- t2createtablet2(idbigintdefault'0'notnullcomment'主键id',goalsintdefault'0'notnullcomment'进球数',matchesintdefault'0'notnullcomment'比赛编号');

t1, t2

探究

口诀：先执行ON，后执行WHERE；ON是建立关联关系，WHERE是对关联关系的筛选。记住这句话就可以准确地判断查询结果了，我们通过两个 sql 来进行分析：

SELECTt1.id,t1.name,t1.ageFROMt1LEFTJOINt2ONt1.id=t2.idWHEREmatches=2;-- 条件放在 WHERESELECTt1.id,t1.name,t1.ageFROMt1LEFTJOINt2ONt1.id=t2.idANDmatches=2;-- 条件放在 ON

执行第一个 sql 时，前提是LEFT JOIN，所以左边的数据在创建关联关系时会保留，根据口诀，先执行ON建立关联关系，然后通过WHERE筛选，过程如下：

sql1

第二个 sql 没有WHERE，那么ON建立的关联关系就是最终结果：

sql2

通过这两个 sql 可以很好的区分WHERE和ON的区别了，希望大家使用的时候多注意这一点，避免犯错！

简书作者 小菜荔枝原创 转载请联系作者获得授

作者：小菜荔枝

链接：https://www.jianshu.com/p/d923cf8ae25f

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

在使用left join时，on and和 where and条件的区别如下：

 在使用链接时，先执行 on and，再执行  where and

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左表中的记录。

2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录了），

    条件不为真的就全部过滤，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

在使用inner join时，on and和 where and条件：

取交集，on and后对左右表都筛选，和where一样。

具体数据比较看原链接：

http://blog.csdn.net/xingzhemoluo/article/details/39677891