MySQL 长难句分析

/*原始数据*/

数据表pc：

3	IBM	8000	256
4	ASUS	5000	512
6	hp	7000	2000
8	MAC	12000	4000
9	lenovo	5000	2000
10	MAC	12000	2000

数据表phone：

1	ipone	5000
2	huawei	2000
3	xiaomi	1000
4	meizu	1234
5	meizu	5678
6	meizu	2000

select * from pc;

select * from phone;

# 显示phone中重复的条目，显示出来的条目的phone的字段的信息是id最小的那条条目的信息
select idphone, phone_name, phone_price from phone group by phone_name having count(phone_name) >= 2;

# 显示pc和phone中id相同的条目
select * from pc join (select idphone, phone_name, phone_price from phone /*having count(phone_name) >= 2*/) as test on pc.idpc = test.idphone order by idphone;

# 显示phone中重复的条目和重复次数
select *, count(d1.phone_name) as count from (phone as d1 left join (select phone.phone_name, phone.phone_price from phone) as d2 on d1.phone_name = d2.phone_name) group by d1.phone_name having count >= 2 order by d1.idphone;

# 显示phone和phone中重复条目 去掉下面的注释之后变成只显示一条
select */*, count(tele.phone_name)*/ from phone as com left join (select phone_name, idphone from phone group by phone_name having count(phone_name) >= 2) as tele on com.phone_name = tele.phone_name;

# phone（com）与phone（tele）中重复条目左连接，连接条件是com的phone_name与tele的phone_name相同。连接完了之后删除com中"满足条件：com中的idphone大于tele中的idphone的条目"的条目  操作完成的同时，com的类型，也就是数据表phone，也会对应删除com中被删除的条目。
delete com from (phone as com left join (select phone_name, idphone from phone group by phone_name having count(phone_name) >= 2) as tele on com.phone_name = tele.phone_name) where com.idphone > tele.idphone;