2019独角兽企业重金招聘Python工程师标准>>> 
多表连接
SELECT t1.col_name,t2.col_name,t3.col_name
FROM table_name AS t1
INNER JOIN table_name AS t2 ON conditions
INNER JOIN table_name AS t3 ON conditions;
无限分类表设计与查询
CREATE TABLE tbd_goods_type(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NNULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
查找所有分类及其父类
SELECT s.type_id,s.type_name,p.type_name
FROM tbd_goods_type AS s
LEFT JOIN tbd_goods_type AS p
ON s.parent_id = p.type_id;
查找所有分类及其子类
SELECT p.type_id,p.type_name,s.type_name
FROM tbd_goods_type AS p
LEFT JOIN tbd_goods_type AS s
ON s.parent_id = p.type_id;
查找所有分类及其子类的数目
SELECT p.type_id,p.tyoe_name,count(s.type_name) typecount
FROM tbd_goods_type AS p
LEFT JOIN tbd_goods_type AS s
ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id;
查找重复记录
SELECT goods_id,goods_name
FROM tdb_goods
GROUP BY goods_name H
AVING count(goods_name) >= 2;
删除重复记录
DELETE t1 FROM tdb_goods AS t1
LEFT JOIN (
SELECT goods_id,goods_name
FROM tdb_goods
GROUP BY goods_name
HAVING count(goods_name) >= 2 ) AS t2
ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;