SQL进阶02自连接

01 排列 组合

# 1. 可重排列
SELECT t1.id AS id1
	,  t2.id AS id2
FROM table1 t1,table1 t2;

# 2. 排列
SELECT t1.id AS id1
	,  t2.id AS id2
FROM table1 t1,table1 t2
WHERE t1.id<>t2.id;


# 3. 可重组合
SELECT t1.id AS id1
	,  t2.id AS id2
FROM table1 t1,table1 t2
WHERE t1.id>=t2.id;

# 4. 组合
SELECT t1.id AS id1
	,  t2.id AS id2
FROM table1 t1,table1 t2
WHERE t1.id>t2.id

02 删除重复数据

删除name 和price 重复的数据，

id	name	price
1	电脑	$1600
2	手机	$12
3	导管	$1
4	导管	$1

集合的思想

#mysql DELETE使用别名时 别名写在DELETE 和 FROM之间
DELETE p1 FROM products 
WHERE id <(
	SELECT MAX(p2.id) FROM products p2
    WHERE p1.name=p2.name AND p1.price=p2.price
)

#其它
DELETE