mysql自查询 重复_mysql-连接及子查询使用删除重复记录

多表连接

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;