[PSQL] 自连接的用法

已于 2022-08-01 23:05:36 修改
阅读量758 收藏 1
点赞数
文章标签： sql
于 2022-07-24 20:36:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wy_hhxx/article/details/125961383
版权

《SQL进阶教程》 MICK 1-2 自连接的用法

关于用于创建示例用表的SQL语句和示例代码等，请参考如下网站。
http://www.ituring.com.cn/book/1813（请点击“随书下载”下载中文版相关资料）

---------------------------------------------------------------

说明：如下笔记中的测试基于postgresql14

命令行连接本地PSQL：  psql -U <username> -d <dbname> -h 127.0.0.1 -W

目录

可重排列、排列、组合

删除重复行

方法一：使用极值函数

方法二：使用非等值连接

查找局部不一致的列

排序

可重排列、排列、组合

Products建表语句

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);

==========================================
test=> select * from Products
test-> ;
 name | price
------+-------
 苹果 |    50
 橘子 |   100
 香蕉 |    80
(3 rows)

test=>

针对相同的表进行的连接被称为“自连接”（self join）

（1）可重排列

test=> SELECT P1.name AS name_1, P2.name AS name_2
test-> FROM Products P1, Products P2;
 name_1 | name_2
--------+--------
 苹果   | 苹果
 苹果   | 橘子
 苹果   | 香蕉
 橘子   | 苹果
 橘子   | 橘子
 橘子   | 香蕉
 香蕉   | 苹果
 香蕉   | 橘子
 香蕉   | 香蕉
(9 rows)

test=>

（2）排列

去掉由相同元素构成的对，例如（苹果，苹果）

test=> SELECT P1.name AS name_1, P2.name AS name_2
test-> FROM Products P1, Products P2
test-> WHERE P1.name <> P2.name;
 name_1 | name_2
--------+--------
 苹果   | 橘子
 苹果   | 香蕉
 橘子   | 苹果
 橘子   | 香蕉
 香蕉   | 苹果
 香蕉   | 橘子
(6 rows)

test=>

（3）组合

test=> SELECT P1.name AS name_1, P2.name AS name_2
test-> FROM Products P1, Products P2
test-> WHERE P1.name > P2.name;
 name_1 | name_2
--------+--------
 苹果   | 橘子
 香蕉   | 橘子
 香蕉   | 苹果
(3 rows)

test=>

删除重复行

重建Products

test=> DROP TABLE Products;
DROP TABLE
test=>
test=> CREATE TABLE Products
test-> (rowid INTEGER PRIMARY KEY,
test(>  name VARCHAR(16),
test(>  price INTEGER NOT NULL);
CREATE TABLE
test=>
test=> INSERT INTO Products VALUES(1, '苹果',50);
INSERT 0 1
test=> INSERT INTO Products VALUES(2, '橘子',100);
INSERT 0 1
test=> INSERT INTO Products VALUES(3, '橘子',100);
INSERT 0 1
test=> INSERT INTO Products VALUES(4, '橘子',100);
INSERT 0 1
test=> INSERT INTO Products VALUES(5, '香蕉',80);
INSERT 0 1
test=>
test=> SELECT * FROM Products;
 rowid | name | price
-------+------+-------
     1 | 苹果 |    50
     2 | 橘子 |   100
     3 | 橘子 |   100
     4 | 橘子 |   100
     5 | 香蕉 |    80
(5 rows)

test=>

使用关联子查询删除重复行

方法一：使用极值函数

test=> DELETE FROM Products P1
test->  WHERE rowid < ( SELECT MAX(P2.rowid)
test(>                    FROM Products P2
test(>                   WHERE P1.name = P2. name
test(>                     AND P1.price = P2.price ) ;
DELETE 2
test=>
test=> SELECT * FROM Products;
 rowid | name | price
-------+------+-------
     1 | 苹果 |    50
     4 | 橘子 |   100
     5 | 香蕉 |    80
(3 rows)

test=>

方法二：使用非等值连接

test=> DELETE FROM Products P1
test->  WHERE EXISTS ( SELECT *
test(>                   FROM Products P2
test(>                  WHERE P1.name = P2.name
test(>                    AND P1.price = P2.price
test(>                    AND P1.rowid < P2.rowid );
DELETE 2
test=> SELECT * FROM Products;
 rowid | name | price
-------+------+-------
     1 | 苹果 |    50
     4 | 橘子 |   100
     5 | 香蕉 |    80
(3 rows)

test=>

查找局部不一致的列

【例】假设有下面这样一张住址表，主键是人名，同一家人家庭ID一样

如果家庭ID一样，住址也必须一样，可以认为前田夫妇这样的“同一家人但住址却不同的记录”是手误写错了，可以使用非等值自连接来实现找出这样的记录

Addresses建表语句

--查找局部不一致的列
CREATE TABLE Addresses
(name VARCHAR(32),
 family_id INTEGER,
 address VARCHAR(32),
 PRIMARY KEY(name, family_id));

INSERT INTO Addresses VALUES('前田义明', '100', '东京都港区虎之门3-2-29');
INSERT INTO Addresses VALUES('前田由美', '100', '东京都港区虎之门3-2-92');
INSERT INTO Addresses VALUES('加藤茶',   '200', '东京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('加藤胜',   '200', '东京都新宿区西新宿2-8-1');
INSERT INTO Addresses VALUES('福尔摩斯',  '300', '贝克街221B');
INSERT INTO Addresses VALUES('华生',  '400', '贝克街221B');

查找是同一家人但住址却不同的记录

test=> select * from Addresses;
   name   | family_id |         address
----------+-----------+-------------------------
 前田义明 |       100 | 东京都港区虎之门3-2-29
 前田由美 |       100 | 东京都港区虎之门3-2-92
 加藤茶   |       200 | 东京都新宿区西新宿2-8-1
 加藤胜   |       200 | 东京都新宿区西新宿2-8-1
 福尔摩斯 |       300 | 贝克街221B
 华生     |       400 | 贝克街221B
(6 rows)

test=>
test=> SELECT DISTINCT A1.name, A1.address
test-> FROM Addresses A1, Addresses A2
test-> WHERE A1.family_id = A2.family_id
test-> AND A1.address <> A2.address ;
   name   |        address
----------+------------------------
 前田义明 | 东京都港区虎之门3-2-29
 前田由美 | 东京都港区虎之门3-2-92
(2 rows)

test=>

【例】使用非等值自连接找出Products中价格相同的商品

test=> DROP TABLE Products;
DROP TABLE
test=> CREATE TABLE Products
test-> (name VARCHAR(16) PRIMARY KEY,
test(> price INTEGER NOT NULL);
CREATE TABLE
test=> DELETE FROM Products;
DELETE 0
test=> INSERT INTO Products VALUES('苹果',50);
INSERT 0 1
test=> INSERT INTO Products VALUES('橘子',100);
INSERT 0 1
test=> INSERT INTO Products VALUES('葡萄',50);
INSERT 0 1
test=> INSERT INTO Products VALUES('西瓜',80);
INSERT 0 1
test=> INSERT INTO Products VALUES('柠檬',30);
INSERT 0 1
test=> INSERT INTO Products VALUES('香蕉',50);
INSERT 0 1
test=>
test=> SELECT * FROM Products
test-> ;
 name | price
------+-------
 苹果 |    50
 橘子 |   100
 葡萄 |    50
 西瓜 |    80
 柠檬 |    30
 香蕉 |    50
(6 rows)

test=> SELECT DISTINCT P1.name, P1.price
test-> FROM Products P1, Products P2
test-> WHERE P1.price = P2.price
test-> AND P1.name <> P2.name;
 name | price
------+-------
 香蕉 |    50
 苹果 |    50
 葡萄 |    50
(3 rows)

test=>

排序

【例】按照价格从高到低对表Products里的商品进行排序。我们让价格相同的商品位次也一样，而紧接着它们的商品则有两种排序方法，一种是跳过之后的位次，另一种是不跳过之后的位次。

test=> SELECT name, price,
test->     RANK() OVER (ORDER BY price DESC) AS rank_1,
test->     DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
test->   FROM Products;
 name | price | rank_1 | rank_2
------+-------+--------+--------
 橘子 |   100 |      1 |      1
 西瓜 |    80 |      2 |      2
 苹果 |    50 |      3 |      3
 葡萄 |    50 |      3 |      3
 香蕉 |    50 |      3 |      3
 柠檬 |    30 |      6 |      4
(6 rows)

test=>

【例】不依赖于排序函数实现，如果已出现相同位次，则跳过之后的位次

test=> SELECT P1.name,
test->        P1.price,
test->       (SELECT COUNT(P2.price)
test(>          FROM Products P2
test(>         WHERE P2.price > P1.price) + 1 AS rank_1
test->  FROM Products P1
test->  ORDER BY rank_1;
 name | price | rank_1
------+-------+--------
 橘子 |   100 |      1
 西瓜 |    80 |      2
 苹果 |    50 |      3
 葡萄 |    50 |      3
 香蕉 |    50 |      3
 柠檬 |    30 |      6
(6 rows)

test=>

wy_hhxx
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
psql连接数据
喝醉酒的小白
10-01 1270
psql
psql连接数据库
kylinxjd的博客
10-10 3771
使用命令行psql连接数据库
PostgreSQL自连接查询
林胖的专栏
03-28 2268
首先SQL中的自连接查询是一种比较特殊的多表查询方式，其特殊之处就在于数据来源于同一张表。 很多时候我们在第一次接触自连接查询时会比较迷乱，不知道怎么去理解。所以我们在处理这种自连接查询时，不要去刻意的在意只有一张表，而是把这张来源表当作一个视图，我们需要查数据，就给其取一个别名，这样就刻意把之前的表认为成另一个表。这样当我们需要时关联几张表时，就取几个别名，就当作是几张表，下意识的去当作不同的...
psql 命令【postgresql，连接数据库，列出数据库，列出表等】
最新发布
Teng的博客
01-23 1309
是 PostgreSQL 的命令行工具，用于与 PostgreSQL 数据库进行交互。中，你还可以使用 SQL 语句执行数据库管理和查询操作。为你要连接的数据库名称。系统可能会要求你输入密码。命令，你可以根据需要使用更多的命令和选项。为你的 PostgreSQL 用户名，
自连接
Back from Dead
09-29 633
自连接自连接：将表复制一份，重命名，然后关联两张表查询。查语文成绩大于数学成绩的同学的姓名。 SQL如下: SELECT yy.stuName, yy.courseGrade, ss.courseGrade FROM grade yy, grade ss WHERE yy.courseGrade > ss.courseGrade and yy.s
psql常用命令
07-06
psql 常用命令大全 PostgreSQL 是当前最流行、最先进的开源数据库之一，在企业中非常流行，因此掌握 PostgreSQL 也是 IT 人员的基本功之一。psql 是 PostgreSQL 的命令行工具，提供了许多实用的命令来帮助用户高效...
Greenplum底层是postgresql，客户端自然也是psql。由于psql本身不支持通过参数来指定密码进行登录，所以需要
07-08
总结起来，以上三种方法都提供了在Greenplum中使用`psql`客户端执行SQL语句时的免密登录解决方案。选择哪种方法取决于具体的安全需求、自动化脚本的复杂性和环境的特性。在实际操作中，确保遵循最佳安全实践，如定期...
免安装Oracle客户端使用PLSQL连接Oracle的2种方法
03-30
免安装Oracle客户端使用PLSQL连接Oracle的2种方法 很好用的
Python3创建Django项目的几种方法(3种)
12-17
这里运行了模块venv,并使用它创建一个名为ll_env的虚拟环境 注意：如果python是较早版本，则先安装virtualenv（pip3 install virtualenv）然后再创建虚拟环境（virtualenv ll_env） 2、激活虚拟环 境命令激活：ll_...
vc连接数据库的第三方类
04-20
vc连接数据库的第三方类 连接access\sqlserver\mysql 只需要修改连接字符串 //Mysql 数据库 /*采用mysql数据库中作为vc的连接数据库，当采用ado来处理时，只需要按下面步骤即可。 1.下载mysql odbc，or mysql ...
PostgreSQL数据库连接和数据库管理
SQLplusDB的小院
05-06 4468
利用自带的psql工具连接和断开PostgreSQL服务器。 PostgreSQL数据库操作相关的常用命令。
PostgreSQL表连接：内连接，外连接，自连接，交叉连接
dugushangliang的博客
01-03 3447
版权声明：转载请注明作者（独孤尚良dugushangliang）出处：https://blog.csdn.net/dugushangliang/article/details/103817246 搜了搜，基本上都是写内连接、外连接、交叉连接这三种类型，但我发现PostgreSQL还有自连接。不妨一并写来做个记录。 先说概念： 内连接，就是两个表逐行匹配，匹配上的内容都显示，没有匹配的都...
PostgreSQL-数据库命令
孙霸天的专栏
08-02 4358
一个数据库是一个或多个模式的集合，而模式包含表、函数等。因此，完整的逻辑组织结构层次是服务器实例（PostgreSQL Server）、数据库（Database）、模式（Schema）、表（Table），以及某些其他对象（如函数）。一个PostgreSQL服务器实例可以管理多个数据库。当应用程序连接到一个数据库时，一般只能访问该数据库中的数据。
SQL进阶教程——自连接的用法（第二章）
qq_28285403的博客
10-29 1766
1. 什么是自连接 针对相同的表进行的连接被称为“自连接”，一旦熟练掌握自连接技术，我们便能快速的解决很多问题。但是，其处理过程不太容易想象，以至于常常被人们敬而远之。 理解自连接不仅可以让我们学会实际工作中能用到的技能，还能增进我们对“面向集合”这一SQL语言特征的理解。 2. 可重排列，排列，组合 假设这列有一张存放了商品名称及价格的表，表里有“苹果，橘子，香蕉”这三条记录。在生成用于查询销售...
PostgreSQL 命令行工具介绍
分享既学习
09-18 1932
psql 是 PostgreSQL 自带的命令行交互客户端工具，类似于 MySQL 的不过相当于 MySQL 的命令行工具 psql 功能更丰富些，例如单击 tab 自动补全。本篇文章介绍 psql 的使用及常用命令。
SQL自连接查询原理及完全连接用法
YY的博客
04-07 6078
SQL自连接查询语句 select * from customers c1,customer c2; 所以最终的自连接表，如下所示：
PostgreSQL数据库连接
热门推荐
weixin_43821620的博客
10-30 1万+
PostgreSQL数据库连接 1、使用JDBC连接 （1）使用maven导入依赖包 ​ 在pom.xml文件中加入依赖 <dependency> <groupId>postgresql</groupId> <artifactId>postgresql</artifactId> <version>8.2-504</version> </dependency> （2）加载postgre
MySQL--自然连接的用法
或是深海的博客
10-15 9309
本文根据《SQL进阶教程》（[日]MICK 吴炎昌/译）所写笔记。 之前学习的内连接，外连接，交叉连接都是针对不同表的，本节我们学习自联结，是针对相同表的连接。 可重排列、排列、组合 我们先创建一张Products表，。在生成用于查询销售额的报表时，我们有时候会需要获得这些商品的组合： CREATE TABLE Products (name VARCHAR(16) PRIMARY KEY,...
Linux下psql安装配置以及手动连接远程数据库
weixin_57667479的博客
07-13 2062
在redhat下安装：在操作界面输入下面脚本命令进行RPM的安装 下载完成后在执行下面的脚本命令进行配置 #在这里安装的PG13版本的源可使用 来查看psql的使用方法 上面是远程连接数据库所需要的，例如; 这里需要注意的是，手动配置的过程中，-h等命令后不需要等号以及空格 -W有密码的时候会自动弹出password：（密码输入过程中不会显现）***有一个自动的shell脚本可以做参考...
Psql每次连接都需要再次输入密码
06-02
如果你在使用psql连接到PostgreSQL时每次都需要再次输入密码，可能是由于以下原因： 1. 你使用的是peer身份验证方法，这种方法通过检查当前登录用户和目标数据库的用户名是否匹配来确定用户是否有权访问数据库。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值