SQL-自连接的用法

[TOC]

参考文献

• SQL进阶教程

自连接的用法

可重排列、排列、组合

CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
 price INTEGER NOT NULL);

-- 可重排列·排列·组合
INSERT INTO Products VALUES('苹果',	50);
INSERT INTO Products VALUES('橘子',	100);
INSERT INTO Products VALUES('香蕉',	80);

-- 用于获取可重排列的 SQL语句
SELECT P1.name AS name_1, P2.name AS name_2 
FROM Products P1, Products P2;

-- 用于获取排列的 SQL语句
SELECT P1.name AS name_1, P2.name AS name_2 
FROM Products P1, Products P2
WHERE P1.name <> P2.name;

-- 用于获取组合的 SQL语句
SELECT P1.name AS name_1, P2.name AS name_2 
FROM Products P1, Products P2
WHERE P1.name > P2.name;

删除重复行

-- 用于删除重复行的 SQL语句  (1)：使用极值函数 
DELETE FROM Products P1
WHERE id < ( SELECT MAX(P2.id) 
             FROM Products P2 
             WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;
                    
-- 用于删除重复行的 SQL语句  (2)：使用非等值连接 
DELETE FROM Products P1
WHERE EXISTS ( SELECT *
               FROM Products P2 
               WHERE P1.name = P2.name 
                   AND P1.price = P2.price 
                   AND P1.id < P2.id );

查找局部不一致的列

-- 用于查找是同一家人但住址却不同的记录的SQL语句 
SELECT DISTINCT A1.name, A1.address
FROM Addresses A1, Addresses A2 
WHERE A1.family_id = A2.family_id 
   AND A1.address <> A2.address ;
   
-- 用于查找价格相等但商品名称不同的记录的  SQL语句 
SELECT DISTINCT P1.name, P1.price
FROM Products P1, Products P2 
WHERE P1.price = P2.price 
   AND P1.name <> P2.name;