好友推荐（列转行，help_topic_id）

---

前言

本题主要分享一下结合mysql.help_topic 这张系统表做列转行的一些巧妙的解题思想。

---

一、题目：好友推荐

1、 两人不是好友，但有共同好友，可以相互推荐(共同好友有n个，推荐指数为n)
2、例如：小明和小王不是好友，但是却有公共的好友小孙，因此可以将小明推荐给小王，也可以将小王推荐给小明
3、代码如下：

create table user_friends(
	user varchar(5),
	friends text
);

insert into user_friends(user,friends)values
('小明','小张,小李,小孙'),
('小张','小明,小李'),
('小李','小张,小明,小孙'),
('小孙','小明,小李,小王'),
('小王','小孙');

4、需求结果展示：

-- 原表
+------+----------------+
| user | friends        |
+------+----------------+
| 小明 | 小张,小李,小孙 |
| 小张 | 小明,小李      |
| 小李 | 小张,小明,小孙 |
| 小孙 | 小明,小李,小王 |
| 小王 | 小孙           |
+------+----------------+
-- 结果展示
+-------------+----------+
| 互相推荐    | 推荐指数 |
+-------------+----------+
| 小孙<->小张 | 2        |
| 小明<->小王 | 1        |
| 小李<->小王 | 1        |
+-------------+----------+

二、代码块

1.源代码

代码如下（先贴sql，大家自己思考一下，后面再分解思想）：

-- 1、为了方便 创建一张视图
select view V_friends 
AS
	select
		user,substring_index(substring_index(U.friends,',',M.help_topic_id+1),',',-1)friend
	from
		user_friends U,mysql.help_topic M
	where
		M.help_topic_id<=length(U.friends)-length(replace(U.friends,',',''))

-- 2、
select
	concat(if(A.user<B.user,A.user,B.user),'<->',if(A.user>B.user,A.user,B.user))互相推荐,
	round(count(1)/2) as 推荐指数
from
	V_friends A
cross join
	V_friends B
on
	A.user!=B.user and
	A.friend=B.friend and
	A.user not in (select friend from V_friends where user=B.user)
group by
	互相推荐
order by
	推荐指数 DESC;

2.SQL分解

代码如下（示例）：

-- user_friends 表和 mysql.help_topic表做笛卡尔积
-- where 条件决定了mysql.help_topic 的行数
	-- 如何决定行数？？？
select 
	user,friends,help_topic_id 
from 
	user_friends,mysql.help_topic 
where 
	help_topic_id<5 
order by 
	user,friends;

-- 根据user_friends 中每行friends列值中逗号的数量来决定mysql.help_topic的行数
select
	user,help_topic_id
from
	user_friends U,mysql.help_topic M
where
	M.help_topic_id<=length(U.friends)-length(replace(U.friends,',',''))


-- 通过笛卡尔积后的每行friends列 和本行的 help_topic_id 值配合提取和help_topic_id对应的好友名
select view V_friends 
AS
	select
		user,substring_index(substring_index(U.friends,',',M.help_topic_id+1),',',-1)friend
	from
		user_friends U,mysql.help_topic M
	where
		M.help_topic_id<=length(U.friends)-length(replace(U.friends,',',''))
	
-- 可视化展示	
select
	concat(if(A.user<B.user,A.user,B.user),'<->',if(A.user>B.user,A.user,B.user))互相推荐,
	round(count(1)/2) as 推荐指数
from
	V_friends A
cross join
	V_friends B
on
	A.user!=B.user and
	A.friend=B.friend and
	A.user not in (select friend from V_friends where user=B.user)
group by
	互相推荐
order by
	推荐指数 DESC;