【SQL学习笔记】《SQL进阶教程》1.2

1-2自连接的用法

自连接+非等值连接

自连接+GROUP BY = 递归集合

表是行的集合，面向集合

开销较大

唯二重要的方法

CASE 自连接

SQL语言 ： 面向集合的特性

可重排列、排列、组合

有序对<>、无序对{}

--获取可重排列(交叉连接 笛卡尔积) 3*3
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Product P2;

--获取排列 A32 = 3*2
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name <> P2.name;

--获取组合C32 = 2*3 / 2
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;

--获取组合: 扩展程3列
SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
FROM Products P1, Products P2, Products AS P3
WHERE P1.name > P2.name
AND   P2.name > P3.name;

删除重复行

可用的行id仅有Oracle和postgresql可以使用、且pgsl需要在建表是指定with oids

--关联子查询：对两个拥有相同数据的集和进行的关联操作
DELETE FROM Products P1
WHERE rowid < ( SELECT MAX(P2.rowid))
				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.rowid < P2.rowid ) ;

查找局部不一致的列

--查找同一家人住址不同
--寻找价格相等的商品组合（+ DISTINCT 避免出现重复行）
--自连接 + 非等值连接
SELECT DISTINCT A1.name， A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;

-- 用于查找价格相等但商品名称不同的记录的 SQL 语句(DISTINCT)
SELECT DISTINCT P1.name, P1.price
FROM Products P1, Products P2
WHERE P1.price = P2.price
AND P1.name <> P2.name;

--关联子查询方法（作业）
SELECT P1.name, P1.price
FROM Products AS P1
WHERE P1.name <> ( SELECT P2.name
                   FROM Products AS P2
                   WHERE P1.price = P2.price)

排序

跳过之后的位次

不跳过之后的位次

--窗口函数
SELECT name, price,

--跳过之后的位次，123336
	RANK() OVER (ORDER BY price DESC) AS rank_1
--不跳过之后的位次，123334
	DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
FROM Products

--非等值自连接
--有几个比自己大的再加一就是自己的排序

--排序从 1 开始。如果已出现相同位次，则跳过之后的位次
--去掉 +1 则从0排序；COUNT(DISTINCT P2.price)，则不跳过之后的位次。
SELECT P1.name, P1.price,
	(SELECT COUNT(P2.price)
	 FROM Products P2
	 WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;

--排序：使用自连接
SELECT P1.name,
	MAX(P1.price) AS price,
	COUNT(P2.name) +1 AS rank_1
FROM Products P1 LEFT OUTER JOIN Products P2
ON   P1.price < P2.price
GROUP BY P1.name
ORDER BY rank_1;

--排序：使用内连接，结果没有第一名
SELECT P1.name,
	MAX(P1.price) AS price,
	COUNT(P2.name) +1 AS rank_1
FROM Products P1 INNER JOIN Products P2
ON   P1.price < P2.price
GROUP BY P1.name
ORDER BY rank_1;

习题1-2-1可重组合

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 Products P2
WHERE P1.name >= P2.name;

1-2-2 分地区排序

SELECT district, name, price,
	RANK OVER(PARTITION BY district ORDER BY price DESC) AS rank_1
FROM DistrictProducts;

--关联子查询
SELECT P1.district, P1.name,
       P1.price,
       (SELECT COUNT(P2.price)
          FROM DistrictProducts P2
         WHERE P1.district = P2.district    /* 在同一个地区内进行比较 */
           AND P2.price > P1.price) + 1 AS rank_1
  FROM DistrictProducts P1;

/* 练习题1-2-2：自连接 */
SELECT P1.district, P1.name,
       MAX(P1.price) AS price, 
       COUNT(P2.name) +1 AS rank_1
  FROM DistrictProducts P1 LEFT OUTER JOIN DistrictProducts P2
    ON  P1.district = P2.district
   AND P1.price < P2.price
 GROUP BY P1.district, P1.name;

更新位次

/* 练习题1-2-3：更新位次 */--关联子查询
UPDATE DistrictProducts2 P1
   SET ranking = (SELECT COUNT(P2.price) + 1
                    FROM DistrictProducts2 P2
                   WHERE P1.district = P2.district
                     AND P2.price > P1.price);

--自连接
UPDATE DistrictProducts2
   SET ranking =
         (SELECT P1.ranking
            FROM (SELECT district , name ,
                         RANK() OVER(PARTITION BY district
                                     ORDER BY price DESC) AS ranking
                    FROM DistrictProducts2) P1
                   WHERE P1.district = DistrictProducts2.district
                     AND P1.name = DistrictProducts2.name);