多表连接

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;