分享很少见很有用的SQL功能CORRESPONDING_Mysql

来源:脚本之家  责任编辑:小易  
目录
前言使用CORRESPONDING使用CORRESPONDING BY

前言

我最近偶然发现了一个标准的SQL特性,令我惊讶的是,这个特性在HSQLDB中实现了。这个关键字是CORRESPONDING ,它可以和所有的集合操作一起使用,包括UNION 、INTERSECT 、和EXCEPT 。

让我们来看看sakila数据库它有3个表:

CREATE TABLE actor (
    actor_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp
);

CREATE TABLE customer (
    customer_id integer NOT NULL PRIMARY KEY,
    store_id smallint NOT NULL,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    email varchar(50),
    address_id smallint NOT NULL,
    create_date date NOT NULL,
    last_update timestamp,
    active boolean
);

CREATE TABLE staff (
    staff_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    address_id smallint NOT NULL,
    email varchar(50),
    store_id smallint NOT NULL,
    active boolean NOT NULL,
    username varchar(16) NOT NULL,
    password varchar(40),
    last_update timestamp,
    picture blob
);

相似,但不相同。如果我们想从我们的数据库中获得所有的 "人 "呢?在任何普通的数据库产品中,有一种方法可以做到这一点:

SELECT first_name, last_name
FROM actor
UNION ALL
SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM staff
ORDER BY first_name, last_name

结果可能看起来像这样:

|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |

使用CORRESPONDING

现在,在HSQLDB中,以及在标准SQL中,你可以使用CORRESPONDING 来完成这种任务。比如说:

SELECT *
FROM actor
UNION ALL CORRESPONDING
SELECT *
FROM customer
UNION ALL CORRESPONDING
SELECT *
FROM staff
ORDER BY first_name, last_name

其结果是这样的:

|first_name|last_name|last_update            |
|----------|---------|-----------------------|
|AARON     |SELBY    |2006-02-15 04:57:20.000|
|ADAM      |GOOCH    |2006-02-15 04:57:20.000|
|ADAM      |GRANT    |2006-02-15 04:34:33.000|
|ADAM      |HOPPER   |2006-02-15 04:34:33.000|
|ADRIAN    |CLARY    |2006-02-15 04:57:20.000|
|AGNES     |BISHOP   |2006-02-15 04:57:20.000|
|AL        |GARLAND  |2006-02-15 04:34:33.000|
|ALAN      |DREYFUSS |2006-02-15 04:34:33.000|
|...       |...      |...                    |

那么,发生了什么?列FIRST_NAME,LAST_NAME, 和LAST_UPDATE 是这三个表所共有的。换句话说,如果你针对HSQLDB中的INFORMATION_SCHEMA ,运行这个查询:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'ACTOR'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'CUSTOMER'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'STAFF'

你得到的正是这3个列:

|COLUMN_NAME|
|-----------|
|FIRST_NAME |
|LAST_NAME  |
|LAST_UPDATE|

换句话说,CORRESPONDING ,在集合操作的子查询中创建列的交集(即 "共享列"),投影这些,并应用该投影的集合操作。在某种程度上,这类似于一个 [NATURAL JOIN](https://blog.jooq.org/impress-your-coworkers-with-a-sql-natural-full-outer-join/),后者也试图找到列的交集以产生一个连接谓词。然而,NATURAL JOIN ,然后投影所有的列(或列的联合),而不仅仅是共享的列。

使用CORRESPONDING BY

就像NATURAL JOIN ,这是个有风险的操作。只要一个子查询改变了它的投影(例如,由于表的列重命名),所有这些查询的结果也会改变,甚至可能不会产生语法错误,只是结果不同。

事实上,在上面的例子中,我们可能根本不关心那个LAST_UPDATE 列。它被意外地包含在UNION ALL 的集合操作中,就像NATURAL JOIN 会意外地使用LAST_UPDATE 来连接一样。

对于连接,我们可以使用JOIN .. USING (first_name, last_name) ,至少指定我们想通过哪一个共享列名来连接这两个表。使用CORRESPONDING ,我们可以为同样的目的提供可选的BY 子句:

SELECT *
FROM actor
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM customer
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM staff
ORDER BY first_name, last_name;

现在,这只产生了两个想要的列:

|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |

事实上,这样一来,我们甚至可以有意义地使用INTERSECT和EXCEPT的语法,例如,找到与某个演员共享名字的客户:

SELECT *
FROM actor
INTERSECT CORRESPONDING BY (first_name, last_name)
SELECT *
FROM customer
ORDER BY first_name, last_name;

制作:

|first_name|last_name|
|----------|---------|
|JENNIFER  |DAVIS    |

到此这篇关于分享很少见很有用的SQL功能CORRESPONDING的文章就介绍到这了,更多相关SQL功能内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:SQL?Server的全文搜索功能MySql字符串拆分实现split功能(字段分割转列)NodeJs+MySQL实现注册登录功能.NET?ORM框架SqlSugar实现导航查询功能mysql使用自定义序列实现row_number功能(步骤详解)

  • 本文相关:
  • mysql触发器概念、原理与用法详解
  • mysql数据库命名规范及约定
  • mysql transaction事务安全示例讲解
  • 使用limit,offset分页场景时为什么会慢
  • 解决mysql服务器启动时报错问题的方法
  • 详解mysql数据库之触发器
  • mysql/postgrsql 详细讲解如何用odbc接口访问mysql指南
  • mysql 5.7.19 免安装版配置方法教程详解(64位)
  • mysql替换时间(年月日)字段时分秒不变实例解析
  • mysql 字符集的系统变量说明
  • SQL就是C语言吗?两者有什么区别?
  • c语言怎么与SQL相连?
  • 数据库标准语言SQL和C语言、C++语言的关系是什么?
  • sql怎样创建c表引用a和b表的数据
  • 如何有效修改SQLServer2008的sa密码?
  • SQL是C的缩写什么?
  • 求在sql中“将选修了c语言程序设计课程的所有同学成绩加5分...
  • 如何用SQL语句查询同时选修课程代码为C001和C007的学生的...
  • 用SQL查询语句怎么表达,在表C中统计开设课程的教师人数?
  • 用WINCC的C脚本如何访问远程SQL服务器上的数据库?
  • oracle12c sql Developer怎么用
  • SQL Server 2008安装错误,提示提供有效的账户和密码,都填上...
  • 做vfp+sql2000的c/s系统方法
  • 安装SQL2008出现 “不是有效的安装文件夹”怎么破, 球解决
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlsql?server的全文搜索功能mysql字符串拆分实现split功能(字段分割转列)nodejs+mysql实现注册登录功能.net?orm框架sqlsugar实现导航查询功能mysql使用自定义序列实现row_number功能(步骤详解)mysql触发器概念、原理与用法详解mysql数据库命名规范及约定mysql transaction事务安全示例讲解使用limit,offset分页场景时为什么会慢解决mysql服务器启动时报错问题的方法详解mysql数据库之触发器mysql/postgrsql 详细讲解如何用odbc接口访问mysql指南mysql 5.7.19 免安装版配置方法教程详解(64位)mysql替换时间(年月日)字段时分秒不变实例解析mysql 字符集的系统变量说明mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql日期数据类型、时间类型使用总结mysql查看版本号的几种方式mysql清空数据表的方法实例与分析mysql中select+update处理并发更新问题解决方案分享mysql使用partition功能实现水平分区的策略详解mysql中的sqrt函数的使用方法mysql数据库备份过程的注意事项浅析mysql 主键使用数字还是uuid查询快详解mysql事务的隔离级别与mvccmysql查看死锁与解除死锁的深入讲解mysql启动时innodb引擎被禁用了的解决方法sqlite3迁移mysql可能遇到的问题集合
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved