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

原创
2017/07/01 22:19
阅读数 203

多表连接
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;

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部