您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

LeetCode SQL刷题(3)- 表连接问题

目录

1. UNION和UNION ALL

2. Join(四种连接方式)


1. UNION和UNION ALL

要求:

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同

区别1 - Union会进行去重:

a. Union对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序
b. Union all对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复

区别2 - Union会进行排序:

a. Union默认排序,如果第一个字段重复,则按第二个字段排序,以此类推
b. Union all不排序

共性:

a. Union/Union all的前后两张表都不需要加括号

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

b. Union后可以直接使用两张表的新建列名做排序

SELECT 'succeeded' AS period_state,
MIN(success_date) AS start_date,
MAX(success_date) AS end_date
FROM
(SELECT *, 
ROW_NUMBER() OVER(ORDER BY success_date ASC) AS rk,
DATE_SUB(success_date, INTERVAL ROW_NUMBER() OVER(ORDER BY success_date ASC) DAY) AS diff
FROM Succeeded
WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31') a
GROUP BY diff
UNION ALL
SELECT 'failed' AS period_state,
MIN(fail_date) AS start_date,
MAX(fail_date) AS end_date
FROM
(SELECT *, 
ROW_NUMBER() OVER(ORDER BY fail_date ASC) AS rk,
DATE_SUB(fail_date, INTERVAL ROW_NUMBER() OVER(ORDER BY fail_date ASC) DAY) AS diff
FROM Failed
WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31') a
GROUP BY diff
ORDER BY start_date

2. Join(四种连接方式)

  1. 内连接:Inner Join
  2. 外连接:Left Outer Join,Right Outer Join(Outer可省略)
  3. 全连接:Full Outer Join(Outer可省略)-- 只要其中某个表存在匹配,Full Join 关键字就会返回行(左连接+右连接)
  4. 笛卡尔积连接:Cross Join -- 两表直接笛卡尔积的结果数量是两表的数据量相乘(列相加,行相乘)

注意:Mysql中没有Full Join,可以左连接和右连接进行Union

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.name = table2.name

UNION

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.name =table2.name

注意:笛卡尔积连接有两种写法

select * from a  CROSS JOIN  b;

或者

select * from a, b

 


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进